快捷導(dǎo)航

Mysql中如何刪除表重復(fù)數(shù)據(jù)

更新時間：2023年07月14日 10:22:17 作者：撒拉Hi

這篇文章主要介紹了Mysql中如何刪除表重復(fù)數(shù)據(jù)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

Mysql刪除表重復(fù)數(shù)據(jù)

表里存在唯一主鍵

根據(jù)多個字段刪除重復(fù)數(shù)據(jù)，只保留一條數(shù)據(jù)

DELETE
FROM
	table_name 
WHERE
	(字段1, 字段2) IN (
		SELECT
			t.字段1,
			t.字段2
		FROM
			(
				SELECT
					字段1,
					字段2
				FROM
					table_name 
				GROUP BY
					字段1,
					字段2
				HAVING
					count(1) > 1
			) t
	)
AND id NOT IN (
	SELECT
		dt.id
	FROM
		(
			SELECT
				min(id) AS id
			FROM
				table_name 
			GROUP BY
				字段1,
				字段2
			HAVING
				count(1) > 1
		) dt
)

沒有主鍵時刪除重復(fù)數(shù)據(jù)

1、創(chuàng)建新表

①創(chuàng)建一個新表與目標(biāo)表結(jié)構(gòu)字段保持一致

create table new_table_temp

②將過濾查詢的統(tǒng)計的數(shù)據(jù)寫入到新表

insert into new_table_temp

③將舊表table_name 刪除

delete from table_name?

④將創(chuàng)建的新表名稱修改為舊表名稱

2、添加字段

①表結(jié)構(gòu)添加一個自增且唯一字段

②按照存在唯一主鍵進(jìn)行刪除重復(fù)數(shù)據(jù)

③刪除添加的自增字段

Mysql刪除表中重復(fù)數(shù)據(jù)并保留一條

最近有個需求，給角色添加菜單權(quán)限，這是一個role_menu 表。

里面存放的是角色id和菜單id，是批量給一種類型角色添加，但有可能角色人為添加過，因為數(shù)據(jù)量還是比較大的，如果先查詢這個有沒有添加過再添加會很耗時，而統(tǒng)一不管有沒有添加過一并添加則很快，這就需要后續(xù)給重復(fù)數(shù)據(jù)給刪除掉，于是有了今天的分享。

我這里只做一個列子，工作代碼安全底線大家謹(jǐn)記哈。

準(zhǔn)備一張表用的是mysql8 大家自行更改

/*
 Navicat Premium Data Transfer
 Source Server         : localmysql
 Source Server Type    : MySQL
 Source Server Version : 80030
 Source Host           : localhost:3306
 Source Schema         : nie_db
 Target Server Type    : MySQL
 Target Server Version : 80030
 File Encoding         : 65001
 Date: 17/08/2022 10:49:41
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `id` bigint(0) NOT NULL,
  `message_title` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `message_context` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  `send_peo` bigint(0) NULL DEFAULT NULL,
  `receive_peo` bigint(0) NULL DEFAULT NULL,
  `scope` int(0) NULL DEFAULT 0,
  `del_flag` tinyint(0) NULL DEFAULT 0,
  `create_time` datetime(0) NULL DEFAULT NULL,
  `creator` tinyint(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of message
-- ----------------------------
INSERT INTO `message` VALUES (1, '測試消息', '消息內(nèi)容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (2, '測試消息', '消息內(nèi)容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (3, '測試消息', '消息內(nèi)容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
INSERT INTO `message` VALUES (4, '測試消息', '消息內(nèi)容', 23, 1231, 0, 0, '2022-08-17 10:39:51', 3, '2022-08-17 10:40:00');
SET FOREIGN_KEY_CHECKS = 1;

創(chuàng)建表并添加四條相同的數(shù)據(jù)

接下來是我們這次的重頭，我封裝了一個存儲過程，具體的邏輯都在注釋里了，你也可以分析然后單獨(dú)拿出來分批次執(zhí)行sql

CREATE DEFINER=`root`@`localhost` PROCEDURE `remove`()
BEGIN
DECLARE count_all INT DEFAULT 0;
DECLARE count_copy INT DEFAULT 1;
-- 創(chuàng)建一個臨時復(fù)制表,并將目標(biāo)表數(shù)據(jù)復(fù)制進(jìn)來
DROP TABLE if exists message_01 ;
create TEMPORARY TABLE message_01 (SELECT * FROM message);
-- 查詢?nèi)ブ睾髮嶋H條數(shù) 并賦值給我們的變量 去重根據(jù)實際需求更改GROUP BY 后面條件
SELECT COUNT(1) into count_all from (select COUNT(1) FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t ;
/*刪除復(fù)制表的重復(fù)數(shù)據(jù)并保留一條  保留哪條數(shù)據(jù)可以自己根據(jù)條件調(diào)節(jié)，
比如最小id等等，就是條件問題 還有就是去重根據(jù)實際需求更改GROUP BY 后面條件
這里如果使用了邏輯刪除，有需要保留數(shù)據(jù)的可以改成修改邏輯刪除字段
*/
DELETE FROM message_01 WHERE ID NOT IN (
select t.id FROM (select MAX(id) as id FROM message WHERE del_flag = 0 GROUP BY message_title,message_context) t
);
-- 再次不去重查詢 如結(jié)果和查詢結(jié)果一樣則操作正確且完整
select COUNT(1) INTO count_copy FROM message_01 WHERE del_flag = 0 ;
-- 進(jìn)行最后兩次查詢結(jié)果比對
IF count_all = count_copy THEN
TRUNCATE message;
INSERT INTO message (SELECT * FROM message_01);
SELECT 'success';
ELSE 
SELECT '改造失敗',count_all AS '原表條數(shù)',count_copy AS '復(fù)制表刪除重復(fù)數(shù)據(jù)后統(tǒng)計的條數(shù)';
END IF;
END

另外創(chuàng)建存儲過程，就是再函數(shù)那里右鍵 -》過程-》輸入名字-》完成，你也可以百度下怎么創(chuàng)建的，我這里就不說太多啦。