在数据库中查找重复项并删除它们。

Question

我有一个如下所示的表格结构：

table(A, B)

他们都是主键，需要连接另一张表中的两个条目（即它们代表用户之间的友谊关系）。

我需要检查表格，如果(A,B)存在，则删除(B,A)（或反之亦然）。

由于数据库非常庞大，我无法每次手动为每个条目执行此操作。

当然，我编写了填充数据库的脚本以检查此情况并避免出现问题，但我们在8台不同的PC上使用了该脚本，因此可能存在“反向重复”问题。

- Gurzo

---

你想清理现有的数据库表还是防止未来出现这种情况？在插入/更新时使用触发器可以快速验证是否存在相反的记录。如果是这种情况，您只需要运行一次清理现有数据的一次性操作（不必运行得很快）。 - John Pickup

你不能声明两种方式的键吗？例如，UNIQUE(a, b) 和 UNIQUE(b, a)？那么数据库本身就会执行您的约束，甚至不需要触发器。 - Borealid

我知道现在已经太晚了，但是这个问题本可以通过应用程序避免：A和B通常是ID，您的应用程序应该只将A < B的配对保存到表中。 - Robert

@John Pickup：我需要整理一下。正如所述，我已经使用了触发器，但我们在不同的电脑上运行脚本，可能导致重复。 - Gurzo

@Robert：这是不可能的。由于我们收集的数据的性质，数据库中可能有（A，B），但不可能有（B，A）。尽管如此，我仍然需要这种关系是“唯一”的。 - Gurzo

Answer 1

问题出现的原因是您试图描述的关系是对称的，但模式却建模为非对称关联。正确的建模方法是维护一个关系表，然后有一个将用户链接到关系的表，例如：

relationship:
   id auto_increment

related:
   r_id foreign key references relationship.id
   u_id foreign key references user.id
   primary key (r_id, u_id)

但是要清理现有的数据...一个明显的方法是...

DELETE FROM yourtable d
WHERE A>B AND EXISTS (
    SELECT 1 
    FROM yourtable r
    WHERE r.A=d.B
    AND r.B =d.A
)

然而，如果我记得没错的话，MySQL不喜欢在删除语句中使用一个子查询来引用与删除相同的表。所以...

SELECT d.A,d.B 
INTO dups
FROM yourtable d, yourtable r
WHERE d.A>d.B
AND r.A=d.B
AND r.B =d.A;

那么....

DELETE FROM yourtable
WHERE EXISTS (
 SELECT 1 FROM dups
 WHERE dups.A=yourtable.A
 AND dups.B=yourtable.B
)

不确定推送的谓词是否仍会引起问题，因此如果这样不行....

DELETE FROM yourtable
WHERE CONCAT(A, '/', B) IN (
 SELECT CONCAT(A, '/' B) FROM dups
)

- symcbean

---

这个完美地运行了！只是一个注意点：正确的语法是INSERT INTO ... SELECT ... FROM。谢谢！ - Gurzo