跳至主要內容

如何实现删除重复记录并且只保留一条

2025年9月13日大约 5 分钟

如何实现删除重复记录并且只保留一条

准备工作

最近，在维护数据库数据，由于某历史记录表需要添加唯一索引，所以需要查询出重复的数据，并且删除掉重复的数据只保留其中1条，以保证索引的创建成功。

-- 表
CREATE TABLE `tb_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `category_id` bigint(20) NOT NULL COMMENT '内容类目ID',
  `title` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '内容标题',
  `sub_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '子标题',
  `title_desc` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '标题描述',
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '内容',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `category_id` (`category_id`) USING BTREE,
  KEY `updated` (`updated`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;

-- 数据准备
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (28, 89, '标题1', '标题1', '标题1', '标题1', '2015-07-27 14:41:57', '2015-07-27 14:41:57');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (29, 89, 'ad2', 'ad2', 'ad2', 'ad2', '2015-07-27 14:42:36', '2015-07-27 14:42:36');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (30, 89, 'ad3', 'ad3', 'ad3', 'ad3', '2015-07-27 14:42:58', '2015-07-27 14:42:58');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (31, 89, 'ad4', 'ad4', 'ad4', 'ad4', '2015-07-27 14:43:15', '2015-07-27 14:43:15');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (32, 89, '标题1', '标题1', '标题1', '标题1', '2015-07-27 14:41:57', '2015-07-27 14:41:57');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (33, 89, 'ad2', 'ad2', 'ad2', 'ad2', '2015-07-27 14:42:36', '2015-07-27 14:42:36');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (34, 89, 'ad3', 'ad3', 'ad3', 'ad3', '2015-07-27 14:42:58', '2015-07-27 14:42:58');
INSERT INTO `tb_score`(`id`, `category_id`, `title`, `sub_title`, `title_desc`, `content`, `created`, `updated`) VALUES (35, 89, 'ad4', 'ad4', 'ad4', 'ad4', '2015-07-27 14:43:15', '2015-07-27 14:43:15');

单个字段的操作

Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1;

查看是否有重复的数据:

  • GROUP BY <列名序列>
  • HAVING <组条件表达式>

查询出：根据dname分组，同时满足having字句中组条件表达式（重复次数大于1）的那些组

count(*)与count(1) 其实没有什么差别，用哪个都可以

count(*)与count（列名）的区别：

count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）。

1.查询全部重复的数据

SELECT
	* 
FROM
	tb_score 
WHERE
	title IN ( SELECT title FROM tb_score GROUP BY title HAVING Count( * ) > 1 );

2.删除全部重复数据

将上面的查询select改为delete（这样会出错的）

DELETE 
FROM
	tb_score 
WHERE
	title IN ( SELECT title FROM tb_score GROUP BY title HAVING count( 1 ) > 1 );

会出现如下错误：

DELETE 
FROM
	tb_score 
WHERE
	title IN ( SELECT title FROM tb_score GROUP BY title HAVING count( 1 ) > 1 )
> 1093 - You can't specify target table 'tb_score' for update in FROM clause
> 时间: 0.012s

原因是：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作 解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新

3.查询表中多余重复数据

a.第一种方法

-- 第一种方法 根据id来判断，除了rowid最小的一个
SELECT
	* 
FROM
	tb_score 
WHERE
	title IN ( SELECT title FROM tb_score GROUP BY title HAVING COUNT( 1 ) > 1 ) 
	AND id NOT IN ( SELECT MIN( id ) FROM tb_score GROUP BY title HAVING COUNT( 1 ) > 1 );

上面这种写法正确，但是查询的速度太慢，可以试一下下面这种方法：

b.第二种方法

根据title分组，查找出id最小的。然后再查找id不包含刚才查出来的。这样就查询出了所有的重复数据（除了id最小的那行）

-- 根据title分组，查找出id最小的。然后再查找id不包含刚才查出来的。这样就查询出了所有的重复数据（除了id最小的那行）
SELECT
	* 
FROM
	tb_score 
WHERE
	id NOT IN ( SELECT dt.minid FROM ( SELECT MIN( id ) AS minid FROM tb_score GROUP BY title ) dt );

c.补充第三种方法

SELECT
	* 
FROM
	table_name AS ta 
WHERE
	ta.唯一键 <> ( SELECT max( tb.唯一键 ) FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 );

4. 删除表中多余重复试题并且只留1条

a. 第一种方法：

-- 第一种方法：删除表中多余重复试题并且只留1条
DELETE 
FROM
	tb_score 
WHERE
	title IN ( SELECT t.title FROM ( SELECT title FROM tb_score GROUP BY title HAVING count( 1 ) > 1 ) t ) 
	AND id NOT IN (
SELECT
	dt.minid 
FROM
	( SELECT min( id ) AS minid FROM tb_score GROUP BY title HAVING count( 1 ) > 1 ) dt 
	)

b.第二种方法（与上面查询的第二种方法对应，只是将select改为delete）

-- 第二种方法
DELETE 
FROM
	tb_score 
WHERE
	id NOT IN ( SELECT dt.minid FROM ( SELECT MIN( id ) AS minid FROM tb_score GROUP BY title ) dt );

c.补充第三种方法（推荐的一种方法）

DELETE 
FROM
	table_name AS ta 
WHERE
	ta.唯一键 <> (
SELECT
	t.maxid 
FROM
	( SELECT max( tb.唯一键 ) AS maxid FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 ) t 
	);

多个字段的操作

多个字段也非常简单。就是将group by 的字段增加为你想要的即可。此处只写一个。

DELETE 
FROM
	tb_score 
WHERE
	( category_id, title ) IN (
SELECT
	t.category_id,
	t.title 
FROM
	( SELECT category_id, title FROM tb_score GROUP BY category_id, title HAVING count( 1 ) > 1 ) t 
	) 
	AND id NOT IN (
SELECT
	dt.minid 
FROM
	( SELECT min( id ) AS minid FROM tb_score GROUP BY category_id, title HAVING count( 1 ) > 1 ) dt 
	)

总结

其实上面的方法还有很多需要优化的地方，如果数据量太大的话，执行起来很慢，可以考虑加优化一下：

  • 在经常查询的字段上加上索引
  • 将*改为你需要查询出来的字段，不要全部查询出来
  • 小表驱动大表用IN，大表驱动小表用EXISTS。IN适合的情况是外表数据量小的情况，而不是外表数据大的情况，因为IN会遍历外表的全部数据，假设a表100条，b表10000条那么遍历次数就是100*10000次，而exists则是执行100次去判断a表中的数据是否在b表中存在，它只执行了a.length次数。至于哪一个效率高是要看情况的，因为in是在内存中比较的，而exists则是进行数据库查询操作的。
编辑此页
上次编辑于: 2025/9/13 14:58:07
贡献者: cangbao