“ INSERT IGNORE”与“ INSERT…ON DUPLICATE KEY UPDATE”

Question

在执行INSERT包含多行的语句时，我想跳过重复的条目，否则它们会导致失败。经过研究后，我的选择似乎是使用以下任一方法：

• ON DUPLICATE KEY UPDATE 这意味着要付出一定代价进行不必要的更新，或者
• INSERT IGNORE 这暗示了其他类型的失败的邀请。

这些假设对吗？跳过可能导致重复的行并继续其他行的最佳方法是什么？

Answer 1

我建议使用INSERT...ON DUPLICATE KEY UPDATE。

如果使用INSERT IGNORE，那么如果该行导致重复键，则实际上不会插入该行。但是该语句不会产生错误。而是生成警告。这些情况包括：

• 在具有PRIMARY KEY或UNIQUE约束的列中插入重复的键。
• 将NULL插入具有NOT NULL约束的列中。
• 在分区表中插入一行，但是您插入的值不会映射到分区。

如果使用REPLACE，MySQL实际上会在内部执行，DELETE然后执行INSERT内部操作，这会产生一些意外的副作用：

• 分配了一个新的自动增量ID。
• 带有外键的相关行可能会被删除（如果您使用级联外键），否则可能会阻止REPLACE。
• 触发的触发器DELETE不需要执行。
• 副作用也传播到副本。

修正： 既REPLACE和INSERT...ON DUPLICATE KEY UPDATE是非标准的，私有的发明具体到MySQL。ANSI
SQL 2003定义了MERGE可以满足相同需求（甚至更多）的MERGE语句，但是MySQL不支持该语句。

一位用户尝试编辑此帖子（主持人拒绝了该编辑）。修改尝试添加一个声明，该声明INSERT...ON DUPLICATE KEY UPDATE导致分配了新的自动递增ID。确实会 生成 新的id ，但是更改后的行中不会使用它。

请参见下面的演示，该演示已通过Percona Server 5.5.28测试。配置变量innodb_autoinc_lock_mode=1（默认）：

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

上面的示例表明IODKU语句检测到重复项，并调用更新以更改的值u。请注意，AUTO_INCREMENT=3表示已生成ID，但未在行中使用。

而REPLACE确实会删除原始行并插入新行，从而生成 并 存储新的自动增量ID：

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+