删除具有两个重复值的值，不管它们在哪一列中

Question

我有一个mysql表，如下所示：

id_one     id_two
1          2
2          1
3          2
2          3
4          5
5          4

我想删除具有两个重复值的行，而不考虑它们所在的列，因此示例将如下所示：

id_one     id_two
1          2
3          2
5          4

总共有超过1200万行。我该怎么做，有什么建议吗？Php或mysql查询将是首选。

Answer 1

DELETE a
FROM table1 a
LEFT JOIN
    (
      select id_one, id_two
      from   Table1
      GROUP BY least(id_one, id_two), greatest(id_one, id_two)
    ) b ON a.id_one = b.id_one AND a.id_two = b.id_two
WHERE b.id_two IS NULL

• SQLFiddle Demo

Answer 2

我建议采取两步走的方法：

1. 使id_one始终是较小的值，也就是说，如果id_one大于id_two，则交换它们的值-考虑如下所示(取自here)：

UPDATE tablename SET id_one = (@temp:=id_one)，id_one = id_two，id_two = @temp WHERE id_one >id_two

• 删除重复项，如下所述here：

DELETE tablename FROM tablename INNER JOIN (SELECT min(primary_key) AS min_id，id_one，id_two FROM tablename GROUP BY id_one，id_two HAVING count(1) > 1) AS d ON (d.id_one = tablename.id_one AND d.id_two = tablename.id_two和d.min_id <> tablename.primary_key)

(我假设您在一个包含1,200万个条目的表上有一个主键。)

未测试，请备份您的数据！

Answer 3

DELETE FROM ztable zt
WHERE zt.id_one > zt.id_two
  AND EXISTS (
    SELECT * 
    FROM ztable tx
    WHERE tx.id_one = zt.id_two
      AND tx.id_two = zt.id_one
    )
    ;

在mysql中不起作用，因为在mysql中您不能引用正在更新或删除的表。由于您无论如何都要创建一个备份副本，因此可以在EXISTS子查询中使用它：

CREATE table safetable AS (SELECT * from ztable);

DELETE FROM ztable zt 
WHERE zt.id_one > zt.id_two 
AND EXISTS (
   SELECT * 
   FROM safetable tx
   WHERE tx.id_one = zt.id_two 
     AND tx.id_two = zt.id_one
   );