mysql去重删除数据只保留一条（保留id最小的那一条）

这里记录一下用到的语句和语句模板：

先把我用到语句分别列出来：

-- 查询出重复的数据 SELECT COUNT(*) as repeats, address, signer_name, signer_mobile FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING repeats > 1; -- 查询出重复的数据中最小的id SELECT MIN(id) as min_id, COUNT(*) as repeats, address, signer_name, signer_mobile FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING repeats > 1; -- 查出重复的数据中非最小的id(需要删除的) SELECT id FROM user_operation_useraddress WHERE (address, signer_name, signer_mobile) IN ( SELECT address, signer_name, signer_mobile FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ) AND id NOT IN ( SELECT MIN(id) FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ); -- 建立一张临时表t SELECT id FROM ( SELECT id FROM user_operation_useraddress WHERE (address, signer_name, signer_mobile) IN ( SELECT address, signer_name, signer_mobile FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ) AND id IN ( SELECT MIN(id) FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ) ) as t; -- 删除重复的数据(只保留一条，保留最小id的) DELETE FROM user_operation_useraddress WHERE id IN ( SELECT id FROM ( SELECT id FROM user_operation_useraddress WHERE (address, signer_name, signer_mobile) IN ( SELECT address, signer_name, signer_mobile FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ) AND id IN ( SELECT MIN(id) FROM user_operation_useraddress GROUP BY address, signer_name, signer_mobile HAVING COUNT(*) > 1 ) ) as t );

在Mysql中是不能删除查询出来的记录，而是要通过一张临时表来解决，以上是我用到的语句。 然后下面给出删除重复数据的模板，可以套用：

-- 删除重复的数据(只保留一条，保留最小id的) DELETE FROM table_name WHERE id IN ( SELECT id FROM ( SELECT id FROM table_name WHERE (field1, field2, field3) IN ( SELECT field1, field2, field3 FROM table_name GROUP BY field1, field2, field3 HAVING COUNT(*) > 1 ) AND id IN ( SELECT MIN(id) FROM table_name GROUP BY field1, field2, field3 HAVING COUNT(*) > 1 ) ) as t );

参考文章：简书