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

数据库中表存在重复数据，需要清理重复数据，清理后保留其中一条的情况是比较常见的需求，如何通过1条SQL准确的删除数据呢？

1. 创建表及测试数据

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

CREATETABLE`test` (
`id`INTNOTNULLAUTO_INCREMENT,
`c1`VARCHAR(20) DEFAULTNULL,
`c2`VARCHAR(20) DEFAULTNULL,
`c3`INTDEFAULTNULL,
`c4`DATETIMEDEFAULTNULL,
PRIMARYKEY (`id`)
) ENGINE=INNODBDEFAULTCHARSET=utf8;

1.2 插入测试数据

INSERTINTOtest(c1,c2,c3,c4) VALUES( 'a','b',10, '2022-05-24 18:00:46'),('a','c',20, '2022-05-24 18:00:46');
INSERTINTOtest(c1,c2,c3,c4) VALUES( 'a','c',10, '2022-05-24 18:00:46'),('a','b',20, '2022-05-24 18:00:46');
INSERTINTOtest(c1,c2,c3,c4) VALUES( 'b','c',10, '2022-05-24 18:00:46'),('d','b',20, '2022-05-24 18:00:46');
INSERTINTOtest(c1,c2,c3,c4) VALUES( 'b','c',20, '2022-05-24 18:00:46'),('d','b',30, '2022-05-24 18:00:46');
INSERTINTOtest(c1,c2,c3,c4) VALUES( 'b','c',20, '2022-05-24 18:00:46'),('a','b',40, '2022-05-24 18:00:46');
INSERTINTOtest(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 这2个字段组合作为唯一条件，则查询重复数据的SQL如下

SELECT
c1,
c2,
COUNT(*)
FROM
test
GROUPBYc1,
c2
HAVINGCOUNT(*) >1;

可见，结果如下：

2. 如何删除重复数据

2.1 方案一

很多研发同学习惯的思路如下：

• 先查出重复的记录（使用in）
• 再查出在重复记录但id不在每组id最大值的记录
• 直接将select 改为delete进行删除

查询SQL如下

SELECT*FROMtest
WHERE (c1,c2) IN (
SELECTc1,c2
FROMtest
GROUPBYc1,c2
HAVINGCOUNT(*)>1 )
ANDidNOTIN (
SELECTMAX(id) 
FROMtest
GROUPBYc1,c2
HAVINGCOUNT(*)>1)
ORDERBYc1,c2
;

看上去比较符合结果了，但是改为delete执行的时候结果如下：

--deleteSQL
DELETEFROMtest
WHERE (c1,c2) IN (
SELECTc1,c2
FROMtest
GROUPBYc1,c2
HAVINGCOUNT(*)>1 )
ANDidNOTIN (
SELECTMAX(id) 
FROMtest
GROUPBYc1,c2
HAVINGCOUNT(*)>1)

出现报错信息：

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

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

3. 推荐写法

基于以上情况，使用单条SQL删除的方式如下：

查询SQL：

SELECTa.*
FROMtesta ,
(SELECTc1,c2,MAX(id)idFROMtestGROUPBYc1,c2HAVINGCOUNT(*)>1)b
WHEREa.c1=b.c1ANDa.c2=b.c2
ANDa.id<>b.id

删除SQL

DELETEa
FROMtesta ,
(SELECTc1,c2,MAX(id)idFROMtestGROUPBYc1,c2HAVINGCOUNT(*)>1)b
WHEREa.c1=b.c1ANDa.c2=b.c2
ANDa.id<>b.id

结果：

<n>查询：deleteaFROMtesta , (selectc1,c2,max(id)idfromtestgroupbyc1,c2havingcount(*)>1)bwherea.c1=b.c1anda.c2=b.c2anda....


共7行受到影响

删除后数据如下：

无重复数据了。