如何正确的使用一条SQL删除重复数据

点击上面的蓝字关注我

数据库中存在重复数据，需要清理重复数据。清理后保留其中一个是常见的要求。如何通过一个？SQL如何准确删除数据？

1. 创建表格和测试数据

1.1 数据库中创建一张测试表

CREATETABLE`test`(   `id` INT  NOT NULL AUTO_INCREMENT,   `c1` VARCHAR(20) DEFAULT NULL,   `c2` VARCHAR(20) DEFAULT NULL,   `c3` INT  DEFAULT NULL,   `c4` DATETIME DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=INNODB  DEFAULT CHARSET=utf8;

1.2 插入测试数据

INSERT INTO test(c1,c2,c3,c4) VALUES( 'a','b',10, '2022-05-24 18:00:46)a','c',20, '2022-05-24 18:00:46'); INSERT INTO test(c1,c2,c3,c4) VALUES( 'a','c',10, '2022-05-24 18:00:46)a','b',20, '2022-05-24 18:00:46'); INSERT INTO test(c1,c2,c3,c4) VALUES( 'b','c',10, '2022-05-24 18:00:46)d','b',20, '2022-05-24 18:00:46'); INSERT INTO test(c1,c2,c3,c4) VALUES( 'b','c',20, '2022-05-24 18:00:46)d','b',30, '2022-05-24 18:00:46'); INSERT INTO test(c1,c2,c3,c4) VALUES( 'b','c',20, '2022-05-24 18:00:46)a','b',40, '2022-05-24 18:00:46'); INSERT INTO test(c1,c2,c3,c4) VALUES( 'd','b',40, '2022-05-24 18:00:46)r','f',40, '2022-05-24 18:00:46');

1.3 查看重复数据

例如c1,c2 这两个字段组合是查询重复数据的唯一条件SQL如下

SELECT   c1,   c2,   COUNT(*) FROM   test GROUP BY c1,   c2 HAVING COUNT(*) > 1;

结果如下：

2. 如何删除重复数据

2.1 方案一

许多研发学生的习惯如下：

• 首先找出重复的记录(使用in）
  

• 在重复记录中再次发现id不在每组id记录最大值

• 直接将select 改为delete进行删除

查询SQL如下

SELECT *    FROM  test   WHERE (c1,c2) IN ( SELECT c1,c2   FROM test  GROUP BY c1,c2  HAVING COUNT(*)>1 ) AND  id NOT  IN ( SELECT MAX(id)  FROM  test  GROUP BY c1,c2  HAVING COUNT(*)>1)  ORDER BY c1,c2 ;

看起来更符合结果，但改为delete结果如下：

--delete SQL DELETE FROM  test   WHERE (c1,c2) IN ( SELECT c1,c2   FROM test  GROUP BY c1,c2  HAVING COUNT(*)>1 ) AND  id NOT  IN ( SELECT MAX(id)  FROM  test  GROUP BY c1,c2  HAVING COUNT(*)>1)

出现报错信息：

错误代码：1093 You can't specify target table 'test' for update in FROM clause

也就是说MySQL里目标表需要删除in在子查询中，删除操作不能直接执行。

3. 推荐写法

基于上述情况，使用单条SQL删除方法如下：

查询SQL：

SELECT  a.*   FROM  test  a , (SELECT  c1,c2,MAX(id)id FROM test  GROUP BY c1,c2 HAVING COUNT(*)>1)b WHERE    a.c1=b.c1 AND a.c2=b.c2 ANDa.id<>b.id

删除SQL

DELETE  a  FROM  test  a , (SELECT  c1,c2,MAX(id)id FROM test  GROUP BY c1,c2 HAVING COUNT(*)>1)b WHERE    a.c1=b.c1 AND a.c2=b.c2 AND a.id <>b.id

结果：

查询：delete a FROM test a , (select c1,c2,max(id)id from test group by c1,c2 having count(*)>1)b where a.c1=b.c1 and a.c2=b.c2 and a...   共 7 行受到影响

删除后的数据如下：

无重复数据。

过去的精彩回顾

1.MySQL高可用之MHA集群部署

2.mysql8.新用户和修改加密规则的事情

3. 比hive大数据查询利器快10倍-- presto

4. 监控利器出鞘：Prometheus Grafana监控MySQL、Redis数据库

5. PostgreSQL主要从复制-物理复制

6.MySQL传统点复制在线转向传统点复制GTID模式复制

7.nbsp; MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注