問題描述
如何從 MySQL 表中刪除所有重復數據?
How would I delete all duplicate data from a MySQL Table?
例如，使用以下數據:
SELECT * FROM names;
+----+--------+
| id | name |
+----+--------+
| 1 | google |
| 2 | yahoo |
| 3 | msn |
| 4 | google |
| 5 | google |
| 6 | yahoo |
+----+--------+
如果是 SELECT 查詢，我會使用 SELECT DISTINCT name FROM names;.
I would use SELECT DISTINCT name FROM names; if it were a SELECT query.
我將如何使用 DELETE 執行此操作以僅刪除重復項并僅保留每個記錄?
How would I do this with DELETE to only remove duplicates and keep just one record of each?
推薦答案
編輯器警告:此解決方案計算效率低下，可能會導致大表的連接中斷.
注意 - 您需要首先在您的表的測試副本上執行此操作！
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;
這篇關于刪除除 MySQL 中的一行之外的所有重復行?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！