在联结表中删除重复记录。

Question

我有一个用户和角色之间的HABTM关联。

用户可以是管理员（role_id = 1）或用户（role_id = 2）。

在连接表roles_users中，我有一些冗余记录。例如：

我想要删除重复的记录，比如1:1，2:4。

两个问题：

1. 最好在哪里执行删除重复记录的SQL脚本 -- 迁移？脚本？

2. 什么是删除重复记录的SQL查询语句？

- keruilin

Answer 1

CREATE TABLE roles_users2 LIKE roles_users; -- this ensures indexes are preserved
INSERT INTO roles_users2 SELECT DISTINCT * FROM roles_users; 
DROP TABLE roles_users;
RENAME TABLE roles_users2 TO roles_users;

而且为了将来，防止重复行

ALTER TABLE roles_users ADD UNIQUE INDEX (role_id, user_id);

或者，您可以使用 ALTER TABLE IGNORE 一步完成所有操作：

ALTER IGNORE TABLE roles_users ADD UNIQUE INDEX (role_id, user_id);

IGNORE 是 MySQL 扩展的标准 SQL。它控制 ALTER TABLE 在新表中存在唯一键重复或启用严格模式时发生警告时的处理方式。如果没有指定 IGNORE，则在出现重复键错误时，副本将中止并回滚。如果指定了 IGNORE，则仅使用具有唯一键重复的第一行。其他冲突行将被删除。不正确的值将被截断为最接近的匹配可接受值。

- John Douthat

---

它是ALTER IGNORE TABLE而不是ALTER TABLE IGNORE。但IGNORE并不总是起作用，最好使用@John Douthat的第一个建议通过创建临时表来解决问题。 - Jad B.

@Jadenko88 谢谢你发现了这个问题！现在已经修复了。 - John Douthat

Answer 2

最简单的方法是将数据复制到一个新表中，去除重复项：

CREATE TABLE roles_users2 AS
SELECT DISTINCT * FROM roles_users

您可以选择以下之一：

• 删除旧表，将新表重命名为旧名称并添加索引。
• 清空旧表并将roles_users2中的行插入到roles_users中。

- Mark Byers

---

截断旧表将保留约束和外键引用不变。当然，如果一开始就有明智的约束，就不会有重复数据。(耸肩) - Mike Sherrill 'Cat Recall'