问题描述
我正在尝试使用ALTER IGNORE TABLE + UNIQUE KEY从MySQL表中删除重复项. MySQL文档说:
I'm trying to remove duplicates from a MySQL table using ALTER IGNORE TABLE + an UNIQUE KEY. The MySQL documentation says:
当我运行查询时...
When I run the query ...
ALTER IGNORE TABLE table ADD UNIQUE INDEX dupidx (field)
...我仍然收到错误#1062-键"dupidx"的条目"blabla"重复.
... I still get the error #1062 - Duplicate entry 'blabla' for key 'dupidx'.
推荐答案
MySQL的IGNORE关键字扩展似乎具有在某些版本的MySQL上的InnoDB版本中出现错误.
The IGNORE keyword extension to MySQL seems to have a bug in the InnoDB version on some version of MySQL.
您总是可以将其转换为MyISAM，对索引进行IGNORE-ADD，然后再转换回InnoDB
You could always, convert to MyISAM, IGNORE-ADD the index and then convert back to 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.
这篇关于MySQL:ALTER IGNORE TABLE给出了“违反完整性约束"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！