MySQL查詢數據庫中重復的記錄幾條sql

重復的記錄在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);