如何从MySQL表中删除所有重复数据？
例如，使用以下数据:
SELECT * FROM names;
+----+--------+
| id | name |
+----+--------+
| 1 | google |
| 2 | yahoo |
| 3 | msn |
| 4 | google |
| 5 | google |
| 6 | yahoo |
+----+--------+
如果是SELECT查询，我会使用SELECT DISTINCT name FROM names;.
我如何做到这一点，用DELETE只删除重复，并保持每个记录只有一个？
Editor warning: This solution is computationally inefficient and may bring down your connection for a large table.
NB - You need to do this first on a test copy of your table!
When I did it, I found that unless I also included 100, 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中使用了这种方法
不确定其他版本.
Update: Since people Googling for removing duplicates end up here
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
SELECT DISTINCT cellId,attributeId,entityRowId,value
FROM tableName;