"INSERT IGNORE"vs"INSERT ... ON DUPLICATE KEY UPDATE"

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

ON DUPLICATE KEY UPDATE 这意味着需要花费一些不必要的更新,或者

INSERT IGNORE 这意味着邀请其他类型的未能在未经宣布的情况下失败.

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

1> Bill Karwin..：

---

我建议使用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 |
+----+------+

---

我刚刚看了一个已经填充了很多`INSERT ... ON DUPLICATE KEY UPDATE ...`语句的表.许多数据都是重复的,并且导致AI PK的一个实例在两行之间从17029,941增加到46,271,740.每次生成新的AI都意味着您的范围可以很快填满,您需要清理.这张桌子只有两周大了!

---

@AntTheKnee,啊,大数据时代的工作挑战.

---

我想知道mysql开发团队是否有意从ANSI SQL 2003采用MERGE？

---

哦,我可以补充一点,它确实会为无效的类型不匹配生成警告(而不是错误),但它不会为重复的复合主键生成警告.

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

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

3> 小智..：

---

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

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

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

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

如果需要通知您的脚本,如果没有添加某些记录(由于密钥违规),您必须调用mysql_info()并解析它以获取"Duplicates"值.

---

如果您正在使用PHP,则需要使用[`mysqli_affected_rows()`](http://php.net/manual/es/mysqli.affected-rows.php)来了解`INSERT`是否实际发生过.

---

@Floris该错误是由*外键约束*引起的,而不是由于*重复键*.我正在使用MySQL 5.5.28.使用`INSERT IGNORE`时,忽略重复键,没有错误或警告.

4> David Z..：

---

我经常使用INSERT IGNORE,这听起来就像你正在寻找的那种行为.只要您知道不会插入会导致索引冲突的行并且您相应地规划您的程序,它就不会造成任何麻烦.

---

我担心我会忽略除重复之外的错误.这是正确的还是INSERT IGNORE只忽略了忽略重复失败？谢谢!

---

它会将任何错误转换为警告.在我的回答中查看此类案例的列表.

5> 小智..：

---

我知道这是旧的,但我会添加这个注释,以防其他人(像我一样)到达此页面时尝试查找有关INSERT..IGNORE的信息.

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

没有明确提到的一件事是INSERT..IGNORE将导致无效值在插入时将被调整为最接近的值(而如果未使用IGNORE关键字,则无效值将导致查询中止).

---

我不确定你的意思是什么"无效价值"并纠正于什么？你能提供一个例子或进一步解释吗？

---

这意味着如果在使用"INSERT IGNORE"时将错误的数据类型插入到字段中,则将修改数据以匹配字段的数据类型,并且将插入可能无效的值,然后查询将继续运行.仅使用"INSERT",将引发有关错误数据类型的错误,并且将中止查询.将数字插入varchar或文本字段可能没问题,但是将文本字符串插入到具有数字数据类型的字段中会导致数据错误.

---

@Marenz另一个例子:如果你的表有一个非空列而你的"INSERT IGNORE"查询没有为该列指定一个值,那么无论是否启用了严格的sql_mode,该行都将在该列中插入零值.

6> IEnumerator..：

---

Replace进入似乎是一种选择.或者您可以查看

IF NOT EXISTS(QUERY) Then INSERT

这将插入或删除然后插入.我倾向于先去IF NOT EXISTS检查.

---

NTuplip - 该解决方案仍然可以通过并发事务从插入开始竞争条件.

7> Chris KL..：

---

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

基本上这两个命令都是标准命令的替代语法版本.