首页 文章

删除除MySQL中的One之外的所有重复行? [重复]

提问于
浏览
384

可能重复:删除MySQL中的重复行

如何从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 只删除重复项并保留每个重复记录?

2 回答

  • 170

    编辑器警告:此解决方案在计算上效率低下,可能会导致大型表的连接断开 .

    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.

    • 如果要保留具有最低 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
    尽管OP的问题是关于 DELETE ,但请注意,使用 INSERTDISTINCT 要快得多 . 对于具有800万行的数据库,以下查询花了13分钟,而使用 DELETE ,花了2个多小时但尚未完成 .

    INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
        SELECT DISTINCT cellId,attributeId,entityRowId,value
        FROM tableName;
    
  • 864

    如果要保留具有最低 id 值的行:

    DELETE FROM NAMES
     WHERE id NOT IN (SELECT * 
                        FROM (SELECT MIN(n.id)
                                FROM NAMES n
                            GROUP BY n.name) x)
    

    如果您想要 id 值最高:

    DELETE FROM NAMES
     WHERE id NOT IN (SELECT * 
                        FROM (SELECT MAX(n.id)
                                FROM NAMES n
                            GROUP BY n.name) x)
    

    子查询中的子查询对于MySQL是必需的,否则您将收到1093错误 .

相关问题