MySQL：ALTER IGNORE TABLE ADD UNIQUE，什么会被截断？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5456520/
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 ADD UNIQUE, what will be truncated?
提问by kuba
I have a table with 4 columns: ID, type, owner, description. ID is AUTO_INCREMENT PRIMARY KEY and now I want to:
我有一个包含 4 列的表：ID、类型、所有者、描述。ID 是 AUTO_INCREMENT PRIMARY KEY，现在我想：
ALTER IGNORE TABLE `my_table`
ADD UNIQUE (`type`, `owner`);
Of course I have few records with type = 'Apple' and owner = 'Apple CO'. So my question is which record will be the special one to stay after that ALTER TABLE, the one with smallest ID or maybe the one with biggest as the latest inserted?
当然，我的 type = 'Apple' 和 owner = 'Apple CO' 的记录很少。所以我的问题是，哪条记录将是在 ALTER TABLE 之后保留的特殊记录，是 ID 最小的记录还是最新插入的最大的记录？
回答by Galz
The first record will be kept, the rest deleted §§:
第一个记录将被保留，其余的被删除§§：
IGNOREis 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. IfIGNOREis not specified, the copy is aborted and rolled back if duplicate-key errors occur. IfIGNOREis specified, only the firstrow 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指定，则仅第一行用于在唯一键上具有重复项的行，删除其他冲突行。不正确的值被截断为最接近匹配的可接受值
I am guessing 'first' here means the one with the smallest ID, assuming the ID is the primary key.
我猜这里的“第一个”是指 ID 最小的那个，假设 ID 是主键。
Also note:
另请注意：
As of MySQL 5.7.4, the
IGNOREclause forALTER TABLEis removedand its use produces an error.
从 MySQL 5.7.4 开始，
IGNOREfor 子句ALTER TABLE被删除，它的使用会产生一个错误。
回答by Sally Levesque
It appears that your problem is one of the very reasons that ALTER IGNORE has been deprecated.
看来您的问题是 ALTER IGNORE 已被弃用的原因之一。
This is from the MySQL noteson the ALTER IGNORE deprecation:
这是来自关于 ALTER IGNORE 弃用的MySQL 注释：
"This feature is badly defined (what is the first row?), causes problems for replication, disables online alter for unique index creation and has caused problems with foreign keys (rows removed in parent table)."
“此功能定义错误（第一行是什么？），导致复制问题，禁用在线更改以创建唯一索引，并导致外键问题（从父表中删除的行）。”
