MySQL:ALTER IGNORE TABLE ADD UNIQUE，什么会被截断?

2021-11-20 00:00:00 mysql alter-table unique-key

我有一个包含 4 列的表格:ID、类型、所有者、描述.ID 是 AUTO_INCREMENT PRIMARY KEY，现在我想:

I have a table with 4 columns: ID, type, owner, description. ID is AUTO_INCREMENT PRIMARY KEY and now I want to:

ALTER IGNORE TABLE `my_table` ADD UNIQUE (`type`, `owner`);

当然，我很少有 type = 'Apple' 和 owner = 'Apple CO' 的记录.所以我的问题是，哪条记录将是那个 ALTER TABLE 之后要保留的特殊记录，是 ID 最小的记录还是最新插入的 ID 最大的记录?

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?

推荐答案

第一条记录会保留，其余删除§§:

The first record will be kept, the rest deleted §§:

IGNORE 是 MySQL 的扩展标准 SQL.它控制如何改变如果存在重复项，则 TABLE 有效新表中的唯一键或者如果使用严格模式时会出现警告启用.如果未指定 IGNORE，副本被中止并回滚，如果发生重复键错误.如果 IGNORE指定，只有第一行使用在 a 上有重复的行唯一键，其他冲突行被删除.不正确的值是截断到最接近的匹配可接受值

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

我猜这里的第一个"是指 ID 最小的那个，假设 ID 是主键.

I am guessing 'first' here means the one with the smallest ID, assuming the ID is the primary key.

另请注意:

从 MySQL 5.7.4 开始，ALTER TABLE 的 IGNORE 子句被删除，它的使用会产生一个错误强>.

As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error.

相关文章