“INSERT IGNORE”vs“INSERT ... ON DUPLICATE KEY UPDATE”

在执行包含许多行的INSERT语句时，我想跳过可能导致失败的重复条目。经过一些研究，我的选择似乎是使用：

ON DUPLICATE KEY UPDATE ，意味着以某种代价进行不必要的更新，或者
INSERT IGNORE意味着邀请其他类型的失败者在未经宣布的情况下失败。

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

mysql insert

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

如果使用INSERT IGNORE ，则实际上不会插入行，如果它导致重复键。但该声明不会产生错误。它会生成警告。这些案件包括：

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

如果您使用REPLACE ，MySQL 实际上会在内部INSERT 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 |
+----+------+

如果你想看看这一切意味着什么，这里是一切的吹嘘：

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

所以你有它。由于这一切都是在一张几乎没有数据且没有生产的新桌子上进行的，因此执行的时间是微观的，无关紧要的。任何拥有真实数据的人都非常欢迎您提供。

要添加的重要内容：使用 INSERT IGNORE 并确实存在密钥违规时，MySQL 不会发出警告！

如果您尝试一次插入 100 条记录，如果有一条有错误，您将进入交互模式：

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

如你所见：没有警告！在官方的 Mysql 文档中甚至错误地描述了这种行为。

如果需要通知您的脚本，如果没有添加某些记录（由于密钥违规），您必须调用 mysql_info（）并解析它以获取 “Duplicates” 值。

协慌网

“INSERT IGNORE”vs“INSERT ... ON DUPLICATE KEY UPDATE”

答案