mysql - "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 语句，可以解决同样的需求(以及更多)，但 MySQL 不支持 MERGE 语句。

---

一位用户试图编辑这篇文章(编辑被版主拒绝)。编辑试图添加一个声明，即 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 |
+----+------+