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 | +----+--------+
参考资料:
上一篇: 设置MySQL默认时区
下一篇: MySQL导入多个.sql文件高效方法