"INSERT IGNORE"与"INSERT ... ON DUPLICATE KEY UPDATE"的区别

Question

执行包含多行的INSERT语句时，我希望跳过可能导致失败的重复条目。经过一些研究，我的选择似乎是使用以下方式之一：

• ON DUPLICATE KEY UPDATE，这意味着会产生不必要的更新成本。
• INSERT IGNORE会邀请其他类型的故障悄悄地滑入。

我的假设是否正确？有没有更好的方法可以简单地跳过可能导致重复的行，并继续处理其他行？

- ThomasGHenry

Answer 1

我建议使用INSERT...ON DUPLICATE KEY UPDATE。如果您使用INSERT IGNORE，则如果导致重复键，则实际上不会插入该行。但是该语句不会生成错误，而是生成一个警告。这些情况包括：

- 在具有主键或唯一约束条件的列中插入重复键。 - 将NULL插入到具有NOT NULL约束条件的列中。 - 向分区表中插入一行，但您插入的值不映射到分区。

如果您使用REPLACE，MySQL实际上在内部执行了一个DELETE，然后是一个INSERT，这会产生一些意想不到的副作用：

- 分配新的自动增量ID。 - 可能会删除具有外键的相关行（如果您使用级联外键），否则会阻止REPLACE。 - 不必要地执行触发器。 - 副作用也会传播到副本中。 更正： 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语句检测到了重复，并调用更新来更改的值。请注意，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 |
+----+------+

- Bill Karwin

---

INSERT IGNORE 语句即使没有插入任何数据，也会增加自增值。 - Maxime

实际上，（我无法再编辑我的评论了）INSERT IGNORE 在插入失败的原因不是相同的键时也可以增加自动递增值，例如，如果值 u 必须是唯一的。 - Maxime

是的，没错。 - Bill Karwin

Answer 2

如果你想了解这一切的含义，以下是所有内容的详细说明：

CREATE TABLE `users_partners` (
  `uid` int(11) NOT NULL DEFAULT '0',
  `pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`,`pid`),
  KEY `partner_user` (`pid`,`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

主键基于这个快速参考表的两个列。主键需要唯一值。

让我们开始：

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...1 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1);
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1);
...0 row(s) affected

INSERT INTO users_partners (uid,pid) VALUES (1,1) ON DUPLICATE KEY UPDATE uid=uid
...0 row(s) affected

注意，上述操作通过将列设置为其自身来避免了大量的额外工作，实际上不需要进行更新

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

现在进行一些多行测试：

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...Error Code : 1062
...Duplicate entry '1-1' for key 'PRIMARY'

INSERT IGNORE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...3 row(s) affected

控制台未生成其他消息，现在表格数据中有这4个值。我只保留了(1,1)，以便从同一起点进行测试。

INSERT INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4) ON DUPLICATE KEY UPDATE uid=uid
...3 row(s) affected

REPLACE INTO users_partners (uid,pid) VALUES (1,1),(1,2),(1,3),(1,4)
...5 row(s) affected

所以，这就是全部内容。由于这个操作是在一个几乎没有数据并且不在生产环境下的新表上进行的，所以执行时间非常短暂且无关紧要。欢迎任何有真实数据的人贡献自己的经验。

- Paulus Maximus

Answer 3

需要补充一些重要的内容：当使用INSERT IGNORE时，如果出现关键冲突，MySQL不会发出警告！

例如，如果您试图一次插入100条记录，其中有一条错误的记录，您将在交互模式下获得如下结果：

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

可以看到，没有警告！这种行为甚至在官方Mysql文档中被错误地描述。

如果您的脚本需要被告知未添加某些记录（由于关键冲突），则必须调用mysql_info()函数并解析“Duplicates”值。

- Jens

---

如果您正在使用PHP，您需要使用mysqli_affected_rows()来确定是否实际发生了INSERT。 - Amal Murali

在MySQL 5.5和MariaDB 10中，我会收到一个错误无法添加或更新子行：外键约束失败，并且没有行（即使是有效的行）被添加。 - Floris

那个错误是由于外键约束而不是重复键导致的。我正在使用MySQL 5.5.28版本。使用 INSERT IGNORE 命令时，如果出现重复键，则会被忽略且不会有错误或警告。 - toxalot

Answer 4

我经常使用INSERT IGNORE，听起来正是您所需要的行为。只要您知道会导致索引冲突的行将不会被插入，并且按照计划编写程序，这就不会引起任何问题。

- David

Answer 5

如上所述，如果您使用INSERT..IGNORE，在执行INSERT语句时发生的错误将被视为警告。

需要注意的是，如果使用INSERT..IGNORE，在插入时无效的值会被调整为最接近的值（而如果未使用IGNORE关键字，则无效的值会导致查询中止）。

- Chris

Answer 6

Replace Into 似乎是一个选项。或者您可以进行检查。

IF NOT EXISTS(QUERY) Then INSERT

这将插入或删除然后再插入。我倾向于先进行IF NOT EXISTS的检查。

- NTulip

---

你需要展示一个例子，因为你的语法不起作用。 - TheRealChx101

Answer 7

INSERT IGNORE存在潜在的危险。如果您试图插入一个比列定义更长的VARCHAR值，则该值将被截断并插入，即使启用了严格模式也是如此。

- LOL

Answer 8

ON DUPLICATE KEY UPDATE并不是真正的标准。它和REPLACE一样标准。请参见SQL MERGE。

实质上，这两个命令都是标准命令的替代语法版本。

- Chris KL

Answer 9

补充一下，如果你在同一个语句中同时使用INSERT IGNORE和ON DUPLICATE KEY UPDATE，当插入操作发现重复键时，更新操作仍然会执行。换句话说，更新操作优先于忽略操作。但是，如果 ON DUPLICATE KEY UPDATE 子句本身导致了重复键错误，则该错误将被忽略。

当你有多个唯一键或者更新操作试图违反外键约束时，就会出现这种情况。

CREATE TABLE test 
 (id BIGINT (20) UNSIGNED AUTO_INCREMENT, 
  str VARCHAR(20), 
  PRIMARY KEY(id), 
  UNIQUE(str));

INSERT INTO test (str) VALUES('A'),('B');

/* duplicate key error caused not by the insert, 
but by the update: */
INSERT INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A'; 

/* duplicate key error is suppressed */
INSERT IGNORE INTO test (str) VALUES('B') 
 ON DUPLICATE KEY UPDATE str='A';

- joshstrike

Answer 10

如果使用insert ignore在查询集的末尾加上SHOW WARNINGS;语句将显示一个包含所有警告的表格，包括哪些ID是重复的。

- Ray Foss

---

SHOW WARNINGS; 只会影响最新的查询。如果您有多个语句，则不会累积任何先前的语句。 - Kawu