MySQL 查询及删除重复记录

查找多余的重复记录，重复根据字段来判断 

SELECT * FROM <table_name> WHERE <col> IN (SELECT <col> FROM <table_name> GROUP BY <col> HAVING COUNT(<col>) > 1)

删除表中多余的重复记录，重复根据字段来判断，保留 rowid 较小的记录 

DELETE FROM <table_name> WHERE <col> IN (SELECT <col> FROM <table_name> GROUP BY <col> HAVING COUNT(<col>) > 1 ) AND <rowID> NOT IN (SELECT MIN(<rowid>) FROM <table_name> GROUP BY <col> HAVING COUNT(<col>) > 1)

给有重复记录的表添加唯一索引 

alter ignore table <table_name>  add unique index(user_id,user_name);

这样也会删除多余的重复记录

参考

https://blog.csdn.net/u014723529/article/details/51218638