删除MySQL中除One之外的所有重复行？ [英] Delete all Duplicate Rows except for One in MySQL?

问题描述

可能重复：

删除MySQL中的重复行

我将如何删除所有来自MySQL表的重复数据？

例如，使用以下数据：

  SELECT * FROM names; 
  

  + --- -  + -------- + 
 | id |名称| 
 + ---- + -------- + 
 | 1 | google | 
 | 2 |雅虎| 
 | 3 | msn | 
 | 4 | google | 
 | 5 | google | 
 | 6 |雅虎| 
 + ---- + -------- + 
  

我会使用 SELECT DISTINCT name FROM names; 如果它是一个 SELECT 查询。如何使用 DELETE 只能删除重复项并保留每个记录？

解决方案

注意 - 您需要首先在表格的测试副本上执行此操作！

当我这样做，我发现除非我还包括 AND n1.id<> n2.id ，它删除表中的每一行。

1）如果要保持最低的行 id value：

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

2）如果要保持行最高 id value：

  DELETE n1 FROM names n1，names n2 WHERE n1.id<我在MySQL 5.1中使用了这种方法。/ / p $ p 
 
 
不知道其他版本。 
 
 
 
 更新：由于Googling删除重复的人最终在这里 
 
虽然OP的问题是关于DELETE，请建议使用INSERT和DISTINCT快得多。对于具有800万行的数据库，下面的查询花了13分钟，而使用DELETE，花费了2个多小时，但尚未完成。 
  INSERT INTO tempTableName（cellId，attributeId，entityRowId，value）
 SELECT DISTINCT cellId，attributeId，entityRowId，value 
 FROM tableName; 
  
 

  
Possible Duplicate:

  Remove duplicate rows in MySQL  
How would I delete all duplicate data from a MySQL Table?

For example, with the following data:
SELECT * FROM names;




+----+--------+
| id | name   |
+----+--------+
| 1  | google |
| 2  | yahoo  |
| 3  | msn    |
| 4  | google |
| 5  | google |
| 6  | yahoo  |
+----+--------+
I would use SELECT DISTINCT name FROM names; if it were a SELECT query. 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!

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

1)  If you want to keep the row with the lowest id value:  
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:
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

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.  
INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
    SELECT DISTINCT cellId,attributeId,entityRowId,value
    FROM tableName;


                        
这篇关于删除MySQL中除One之外的所有重复行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！