MySQL查询数据库中重复的记录几条sql

1.根据表中单个字段(name)来查询重复记录

SELECT * FROM user WHERE name IN ( SELECT name FROM user GROUP BY name HAVING COUNT(name) > 1 );

2.根据表中单个字段(name)，删除重复记录，只保留id最小的记录

DELETE FROM user WHERE name IN ( SELECT name FROM user GROUP BY name HAVING COUNT(name) > 1 ) AND id NOT IN (SELECT min(id) FROM user GROUP BY name HAVING COUNT(name) > 1);

3.根据表中多个字段(name, age)来查询重复记录

SELECT * FROM user u WHERE (u.name, u.age) IN ( SELECT name, age FROM user GROUP BY name, age HAVING COUNT(*) > 1 );

4.根据表中多个字段(name, age)，删除重复记录，只保留id最小的记录

DELETE FROM user u WHERE (u.name, u.age) IN ( SELECT name, age FROM user GROUP BY name, age HAVING COUNT(*) > 1 ) AND id NOT IN (SELECT min(id) FROM user GROUP BY name, age HAVING COUNT(*) > 1);