MySQL：ALTER IGNORE TABLE 给出“违反完整性约束”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8053447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: ALTER IGNORE TABLE gives "Integrity constraint violation"
提问by Philippe Gerber
I'm trying to remove duplicates from a MySQL table using ALTER IGNORE TABLE + an UNIQUE KEY. The MySQL documentation says:
我正在尝试使用 ALTER IGNORE TABLE + 一个唯一键从 MySQL 表中删除重复项。MySQL 文档说：
IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.
IGNORE 是标准 SQL 的 MySQL 扩展。如果新表中的唯一键存在重复项，或者在启用严格模式时出现警告，它会控制 ALTER TABLE 的工作方式。如果未指定 IGNORE，则在发生重复键错误时中止并回滚副本。如果指定了 IGNORE，则只有第一行用于在唯一键上具有重复项的行。其他冲突行被删除。不正确的值被截断为最接近匹配的可接受值。
When I run the query ...
当我运行查询时...
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field)
... I still get the error #1062 - Duplicate entry 'blabla' for key 'dupidx'.
...我仍然收到错误#1062 - 键 'dupidx' 的重复条目 'blabla'。
回答by Emma
The IGNOREkeyword extension to MySQL seems to have a bug in the InnoDB versionon some version of MySQL.
IGNOREMySQL的关键字扩展在某些版本的 MySQL 上的 InnoDB 版本中似乎有一个错误。
You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to InnoDB
您总是可以转换为 MyISAM，忽略添加索引，然后转换回 InnoDB
ALTER TABLE table ENGINE MyISAM;
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field);
ALTER TABLE table ENGINE InnoDB;
Note, if you have Foreign Key constraints this will not work, you will have to remove those first, and add them back later.
请注意，如果您有外键约束，这将不起作用，您必须先删除它们，然后再重新添加它们。
回答by Jay Julian Payne
Or try set session old_alter_table=1 (Don't forget to set it back!)
或者尝试 set session old_alter_table=1 （不要忘记将其设置回来！）
See: http://mysqlolyk.wordpress.com/2012/02/18/alter-ignore-table-add-index-always-give-errors/
请参阅：http: //mysqlolyk.wordpress.com/2012/02/18/alter-ignore-table-add-index-always-give-errors/
回答by Glen Solsberry
The problem is that you have duplicate data in the field you're trying to index. You'll need to remove the offending duplicates before you can add a unique index.
问题是您尝试索引的字段中有重复的数据。您需要先删除有问题的重复项，然后才能添加唯一索引。
One way is to do the following:
一种方法是执行以下操作：
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;
this allows you to insert only the unique data into the table
这允许您仅将唯一数据插入表中
