更新时间:2023-01-24 18:18:40
编辑警告:此解决方案的计算效率低下,并且可能会导致大型表的连接中断.
注意 - 您需要首先在表的测试副本上执行此操作!
NB - You need to do this first on a test copy of your table!
当我这样做的时候,我发现除非我也包括了 AND n1.id <>n2.id
,它删除了表中的每一行.
When I did it, I found that unless I also included AND n1.id <> n2.id
, it deleted every row in the table.
如果要保留 id
值最低的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
如果要保留 id
值最高的行:
DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
我在 MySQL 5.1 中使用了这种方法
I used this method in MySQL 5.1
不确定其他版本.
更新:由于人们在谷歌上搜索删除重复项到此结束
虽然 OP 的问题是关于 DELETE
,但请注意,使用 INSERT
和 DISTINCT
会快得多.对于一个有 800 万行的数据库,下面的查询耗时 13 分钟,而使用 DELETE
则耗时 2 个多小时,但仍未完成.
Update: Since people Googling for removing duplicates end up here
Although the OP's question is about DELETE
, please be advised that using INSERT
and DISTINCT
is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE
, it took more than 2 hours and yet didn't complete.
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
SELECT DISTINCT cellId,attributeId,entityRowId,value
FROM tableName;