mysql - 删除重复行，没有唯一键 - 关系表

Question

我有一个关系表，它根据其他两个表的 ID 将它们连接起来。两列可以有重复项——但同一行不能有两次。我负责检查代码方面。

如何删除重复行(见下文):

select * from people:

a | b
1   2
1   3
1   3
1   7
2   3
2   5
2   5
2   9

我想要的结果是:

a | b
1   2
1   3
1   7
2   3
2   5
2   9

Answer 1

这应该有效:

ALTER IGNORE TABLE people ADD UNIQUE (a,b);

如果您不想添加索引，那么这应该可行:

DROP TABLE IF EXISTS people_old;
DROP TABLE IF EXISTS people_new;
CREATE TABLE people_new LIKE people;
INSERT INTO people_new SELECT DISTINCT * FROM people;
RENAME TABLE people TO people_old, people_new TO people;