mysql 除in以外的方法_刪除除了MySQL中的行之外的所有重復行?(復制)

833

NB - You need to do this first on a test copy of your table!

NB -您需要首先在您的表的測試副本上執行此操作!

When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table.

當我做的時候，我發現除非我也包括n1。id < > n2。id，它刪除了表中的每一行。

1) If you want to keep the row with the lowest id value:

1)如果要保持id值最小的行:

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

2) If you want to keep the row with the highest id value:

2)如果要保持id值最高的行:

DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name

I used this method in MySQL 5.1

我在MySQL 5.1中使用了這種方法

Not sure about other versions.

不確定其他版本。

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.

更新:雖然OP的問題是關於刪除，但是由於人們在google上搜索刪除重復，所以請注意使用INSERT和DISTINCT會更快。對於一個有800萬行的數據庫，下面的查詢需要13分鍾，而使用DELETE時，需要2個多小時，但還沒有完成。

INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)

SELECT DISTINCT cellId,attributeId,entityRowId,value

FROM tableName;