mysql - 删除MySql中的重复项

Question

我继承的数据库存在“重复问题”。

当我运行时:

SELECT myFIELD, COUNT(*) c FROM myTABLE GROUP BY myFIELD HAVING c > 1;

我收到约 600 条重复记录

无或三倍或任何其他倍数

我希望删除其中一条记录，不留下重复的记录。

查询是什么样的？

Answer 1

您可以使用这样的查询...您应该根据同一行重复的次数重复查询..

delete from my_table 
where (myFIELD, id) in  
(select a.myFIELD, max(a.id) 
FROM myTABLE as a  GROUP BY a.myFIELD HAVING count(*) > 1)

否则你可以使用

delete from my_table 
where (myFIELD, id) not in 
(select a.myFIELD, min(a.id) 
FROM myTABLE as a GROUP BY a.myFIELD )

这应该删除镜头中的所有重复行

如果表名有问题

delete from my_table 
where (myFIELD, id) in  (select field, id from 
(select a.myFIELD as field, max(a.id) as  id
FROM myTABLE as a  GROUP BY a.myFIELD HAVING count(*) > 1) as t)