MySQL：ALTER IGNORE TABLE 出现“完整性约束冲突”

Question

我正在尝试使用ALTER IGNORE TABLE和UNIQUE KEY从MySQL表中删除重复项。 MySQL文档如下所述:

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

当我运行查询时...

ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field)

...我仍然遇到错误#1062 - Duplicate entry 'blabla' for key 'dupidx'。

- Philippe Gerber

Answer 1

在某些 MySQL 版本的 InnoDB 版本中，IGNORE 关键字扩展似乎存在一个错误。

您可以始终将表转换为 MyISAM，使用 IGNORE-ADD 添加索引，然后再转换为 InnoDB。

ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;

注意，如果您有外键约束，则无法使用此方法，您需要先删除这些约束，然后再添加回来。

- Emma

---

在那个指向InnoDB bug的链接中，建议首先运行set session old_alter_table=1; 这对我有用。 - Peter

谢谢Peter - 现在看起来对我有效了。我之前不知道这个问题 - 我的开发机器是mariadb，但当我必须在生产环境中运行(mysql 5.5)时遇到了这个问题。这个stackoverflow救了我的一天！ - spidie

这可能是我在 Stack Overflow 上见过的最差的被接受答案之一。即使对于中等大小的表格，更改存储引擎本身也是一项重大任务。这三个查询有可能会让数据库服务器锁定数小时。这根本不像是解决方案。 - Mikkel

我觉得我刚才有点不舒服。 - John Hunt

Answer 2

或者尝试设置session old_alter_table=1 (不要忘记设置回来!)

参见: http://mysqlolyk.wordpress.com/2012/02/18/alter-ignore-table-add-index-always-give-errors/

- Jay Julian Payne

---

这对我来说确实有效。在大表上可能会比较冗长，但它似乎相当线性。在我的机器上，它能够每小时处理大约2GiB的数据，大约需要两天时间。我想知道这与被接受的解决方案相比如何，该解决方案是将其转换为myISAM，添加索引并转换回来。 - Chris Strickland

注意！如果您正在使用复制功能，old_alter_table设置不会被复制，因此在从库上执行ALTER TABLE IGNORE将会失败并中断复制。为了解决这个问题，我手动在从库上执行了ALTER，然后通过使用SET GLOBAL sql_slave_skip_counter = 1跳过了有问题的ALTER TABLE，最后恢复了复制。 - thenickdude

Answer 3

问题在于您要建立索引的字段中存在重复数据。您需要在添加唯一索引之前删除这些重复项。

一种方法是执行以下操作：

   CREATE TABLE tmp_table LIKE table;
   ALTER IGNORE TABLE tmp_table ADD UNIQUE INDEX dupidx (field);
   INSERT IGNORE INTO tmp_table SELECT * FROM table;
   DROP TABLE table;
   RENAME TABLE tmp_table TO table;

这可以让你只插入唯一的数据到表中。

- Glen Solsberry

---

不，IGNORE 关键字应该可以处理那些重复项。这就是这个解决方案的美妙之处。请参见我问题中引用的文档。 - Philippe Gerber