MySQL如何删除违反UNIQUE约束的重复数据并保留一条

这种情况太常见了——当你想给字段组合加唯一索引时，发现表里已经有大量重复数据。既然你确认重复行除了主键其他字段都一致，那我们可以安全地清理掉重复项，只保留每组里的一条。

下面给你两种实用的方法，按需选择：

方法一：直接删除重复行（适合数据量中等的情况） #

假设你的表主键是id（记得替换成你实际的主键字段名），执行这条DELETE语句就能保留每组重复数据中主键最小的那条，删掉其他重复行：

DELETE t1 FROM mytable t1
JOIN mytable t2 
WHERE t1.id > t2.id
AND t1.first = t2.first
AND t1.second = t2.second
AND t1.third = t2.third;

如果想保留主键最大的那条，把条件里的t1.id > t2.id改成t1.id < t2.id就行。

方法二：用临时表迁移数据（适合数据量很大的场景） #

如果表数据特别多，DELETE操作可能会很慢甚至锁表，用临时表的方式更高效：

• 先创建一个和原表结构完全一致的临时表，直接加上你想要的唯一索引：

  CREATE TABLE mytable_temp LIKE mytable;
  ALTER TABLE mytable_temp ADD UNIQUE myunique_name(first, second, third);
  

• 把原表的数据导入临时表，INSERT IGNORE会自动跳过违反唯一约束的重复行：

  INSERT IGNORE INTO mytable_temp SELECT * FROM mytable;
  

• 交换原表和临时表的名字：

  RENAME TABLE mytable TO mytable_old, mytable_temp TO mytable;
  

• 确认新表数据没问题后，再删掉旧表（可选但推荐）：

  DROP TABLE mytable_old;
  

重要提醒 #

不管用哪种方法，操作前一定要备份原表数据！生产环境建议先在测试环境验证一遍，或者选低峰期操作，避免影响业务。

等你清理完重复数据，再执行你原来的ALTER TABLE语句添加唯一索引，就不会报错啦。

内容的提问来源于stack exchange，提问作者Roland Seuhs