mysql 删除重复数据_MySQL查找和删除重复数据

如何从MySQL中删除重复数据，只保留一条呢？

假设有如下一个数据表：

mysql > SELECT * FROM users;

+----+--------+

| id | name |

+----+--------+

| 1 | google |

| 2 | yahoo |

| 3 | msn |

| 4 | google |

| 5 | google |

| 6 | yahoo |

+----+--------+

后面3条都是重复的数据，如何用一条命令删掉这些重复数据呢？

1 查询重复记录数量

查询全部重复记录：

SELECT * FROM users as a WHERE EXISTS (SELECT 1 FROM users as b WHERE b.name = a.name LIMIT 1, 1) order by name;

查询重复记录和最小id：

SELECT min(id), name, count(*) as c FROM users group by name having c > 1;

查询重复记录和最大id：

SELECT max(id), name, count(*) as c FROM users group by name having c > 1;

两个方法的区别就是一个用了min()函数，一个用了max()函数。

2 保留id最小的记录

删除重复记录，保留id最小的记录，命令：

DELETE n1 FROM users n1, users n2 WHERE n1.id > n2.id AND n1.name = n2.name

执行结果就是：

mysql > SELECT * FROM users;

+----+--------+

| id | name |

+----+--------+

| 1 | google |

| 2 | yahoo |

| 3 | msn |

+----+--------+

3 保留id最大的记录

删除重复记录，保留id最大的记录，命令：

DELETE n1 FROM users n1, users n2 WHERE n1.id < n2.id AND n1.name = n2.name

执行结果就是：

mysql > SELECT * FROM users;

+----+--------+

| id | name |

+----+--------+

| 3 | msn |

| 5 | google |

| 6 | yahoo |

+----+--------+

参考资料：