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秒的时间将数据从原始表复制到临时表中,但是其他所有操作都非常快。