MySQL表中删除重复记录

MySQL 表中的存在某些重复行，需要从表中删除重复的行并在某些列上添加唯一约束，以确保不会再出现重复的行。

问题是如何删除重复的行？该表的模式是这样的：

describe table1;

 

| id           | int(11)      

| category     | varchar(255) 

| image_set_id | int(11)      

| position     | decimal(16,6)

就我而言，它在组合上是重复的(category, image_set_id)。我想确保合并后的值永远是唯一的。

在Google上搜索后，我很快找到了解决方案。查询是这样的：

DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name

映射到我们的表，我们有以下查询：

DELETE t1 FROM table1 t1, table t2 WHERE t1.id > t2.id AND t1.category = t2.category AND t1.image_set_id = t2.image_set_id

这种方法有什么问题？这是一个自连接，非常慢。请记住，我们有超过一百万行，因此不要期望很快完成上述查询。

ALTER IGNORE

幸运的是，我找到了另一种满足我要求的解决方案。我们可以用来添加到表中，同时删除重复的行。查询是：ALTER IGNORE UNIQUE INDEX

ALTER IGNORE TABLE table1 ADD UNIQUE INDEX index_on_category_image_set_id(category, image_set_id)

但是我很快意识到它没有用。为什么？已从MySQL 5.7中删除，而这正是我使用的版本。ALTER IGNORE

INSERT IGNORE

最后，我找到了“最佳”解决方案。无需尝试删除当前表，我们可以利用查询。步骤：INSERT IGNORE

• 创建临时表有类似的模式，以现有同类表
• UNIQUE向我们想要的列添加约束
• 运行将数据从原始表复制到临时表时，任何重复的行都不会插入到临时表中，因为它们违反了UNIQUE约束（我们只是忽略了错误）INSERT IGNORE
• 将原始表重命名为其他名称，并将临时表重命名为原始表。删除冗余表

最终的SQL语句为：

 

-- Create temporary table

CREATE TABLE temp_table LIKE table1;

 

-- Add constraint

ALTER TABLE temp_table ADD UNIQUE(category, image_set_id);

 

-- Copy data

INSERT IGNORE INTO temp_table SELECT * FROM table1;

 

-- Rename and drop

RENAME TABLE table1 TO old_table1, temp_table TO table1;

DROP TABLE old_table1;

而已。该脚本花费了大约20秒的时间将数据从原始表复制到临时表中，但是其他所有操作都非常快。