MySQL:ALTER IGNORE TABLE ADD UNIQUE，什么会被截断? [英] MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?

问题描述

我有一个包含4列的表格:ID，类型，所有者，描述. ID是AUTO_INCREMENT主键，现在我要:

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`);

我当然很少有类型='Apple'和owner ='Apple CO'的记录.因此，我的问题是，哪条记录将是保留在该ALTER TABLE之后的特殊记录，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上有重复项，则TABLE可以工作 新表中的唯一键，如果 当严格模式为 已启用.如果未指定IGNORE， 该副本将中止并在以下情况下回滚 出现重复键错误.如果IGNORE 被指定，只有第一行 用于重复的行 唯一键，其他冲突的行 被删除.不正确的值是 截断到最接近的匹配 可接受的值

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.

这篇关于MySQL:ALTER IGNORE TABLE ADD UNIQUE，什么会被截断?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！