数据库中表存在重复数据,需要清理重复数据,清理后保留其中一条的情况是比较常见的需求,如何通过1条SQL准确的删除数据呢?
CREATE TABLE `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;
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');
例如c1,c2 这2个字段组合作为唯一条件,则查询重复数据的SQL如下
SELECT
c1,
c2,
COUNT(*)
FROM
test
GROUP BY c1,
c2
HAVING COUNT(*) > 1;
可见,结果如下:
很多研发同学习惯的思路如下:
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子查询中时,不能直接执行删除操作。
基于以上情况,使用单条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
AND a.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
结果:
<n>查询: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 行受到影响
删除后数据如下:
无重复数据了。