删除重复记录但保留其中一行数据的sql写法

分类：Java

数据库表中存在重复记录的场景并不罕见，导致这种情况的原因可能是进行了数据迁移，或者程序代码有bug（比如并发情况没有处理好），或者在测试库中手动插入了重复数据，不管怎么样，它们可能都违反了业务规则约束，需要删除重复的数据记录，同时又需要保存其中的一条。除了在现实的工作中会碰到这种情况，面试中也经常会问起这个问题。那么，这个sql应该怎么写呢（基于MySQL数据库）？我们尝试用两种方法来实现这个功能。

当然，我们可以通过创建新表然后向新表中插入不重复的数据来完成，或者通过程序来实现这样的功能，但这样稍显麻烦，本文主要考虑的是用一条sql来完成。另外，重复数据主要是基于业务逻辑来判定的，比如除了主键以外，其它所有字段（或者其中某些字段）都相同则视为重复。

一、准备工作

创建一张客户表，向其中插入一些包含重复记录的测试数据：

CREATE TABLE `t_customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `cust_name` varchar(32) NOT NULL COMMENT '名字',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `email` varchar(32) NOT NULL COMMENT 'email',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_customer` VALUES ('1', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO `t_customer` VALUES ('2', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO `t_customer` VALUES ('3', 'Tom', 'Male', 'high_salary@sample.com');
INSERT INTO `t_customer` VALUES ('4', 'John', 'Male', 'good_job@sample.com');
INSERT INTO `t_customer` VALUES ('5', 'Jennifer', 'Female', 'good_job@sample.com');
INSERT INTO `t_customer` VALUES ('6', 'Tom', 'Male', 'high_salary@sample.com');

我们约定，当客户的名字、性别、邮件都相同时，则判定它们为重复的记录。

对于以上的测试数据，id等于1、3、6的为重复数据，id为2、5的也是重复数据，我们需要删除多余的数据，同时需要保留其中的一条。

二、使用DELETE JOIN

表进行自关联，关联条件就是名称、性别和email，删除掉id较小的那些记录，保留id最大值，如果需要保留最小值，改成大于号即可：

DELETE c1 FROM t_customer c1 INNER JOIN t_customer c2
WHERE c1.id<c2.id AND c1.cust_name=c2.cust_name 
AND c1.gender=c2.gender AND c1.email=c2.email

上述sql执行完成后，表中的数据变成如下所示，可以看出，表中已经没有了重复记录，但对于删除前的多条重复数据，还保留了其中的一条：

二、使用子查询

使用GROUP BY加上HAVING COUNT的条件判断，可以找出重复的记录，同时，在删除的时候排除掉id最大的那条记录，这样也可以达到目的：

DELETE FROM t_customer WHERE (cust_name,gender,email) IN 
 (SELECT cust_name,gender,email FROM(
  SELECT cust_name,gender,email FROM t_customer
  GROUP BY cust_name,gender,email
  HAVING COUNT(*) > 1) t1)
AND id NOT IN
 (SELECT id FROM(SELECT MAX(id) id 
FROM t_customer GROUP BY cust_name,gender,email
 HAVING COUNT(*) > 1) t2)