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

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

  • ON DUPLICATE KEY UPDATE ,这意味着要付出一定的代价进行不必要的更新,或者
  • INSERT IGNORE ,它暗示其他类型的失败会自动发生。

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

答案

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

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

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

如果使用REPLACE ,MySQL 实际上会在内部进行DELETE并随后INSERT ,这会有一些意外的副作用:

  • 分配了一个新的自动增量 ID。
  • 带有外键的相关行可能会被删除(如果您使用级联外键),否则可能会导致无法REPLACE
  • 触发执行DELETE触发器是不必要的。
  • 副作用也传播到副本。

修正: REPLACEINSERT...ON DUPLICATE KEY UPDATE都是针对 MySQL 的非标准专有技术。 ANSI SQL 2003 定义了一个MERGE语句,可以解决相同的需求(甚至更多),但是 MySQL 不支持MERGE语句。


一位用户试图编辑此帖子(主持人拒绝了该编辑)。该编辑尝试添加一个声明,即INSERT...ON DUPLICATE KEY UPDATEINSERT...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

控制台中没有其他消息生成,并且现在表数据中具有这四个值。我删除了(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 ,这听起来也正是您想要的行为。只要您知道将导致索引冲突的行不会被插入并且您对程序进行相应的计划,就不会造成任何麻烦。

我知道这很旧,但是我会添加此注释,以防其他人(例如我)在试图找到有关 INSERT..IGNORE 的信息时到达此页面。

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

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

Replace为似乎是一个选择。或者你可以检查

IF NOT EXISTS(QUERY) Then INSERT

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

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

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

INSERT IGNORE 的潜在危险。如果尝试插入 VARCHAR 值的时间更长,则使用 - 定义了列 - 该值将被截断,并且即使启用了严格模式也可以插入。

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

如果要插入表中并且在主键或唯一索引冲突时,它将更新冲突的行,而不是插入该行。

句法:

insert into table1 set column1 = a, column2 = b on duplicate update column2 = c;

现在,在这里,该插入语句看上去可能与您之前看到的有所不同。此插入语句尝试将 table1 中具有 a 和 b 值的行分别插入列 column1 和 column2 中。

让我们深入了解以下语句:

例如:这里 column1 被定义为 table1 中的主键。

现在,如果在表 1 中,在列 1 中没有行具有值 “a”。因此,该语句将在 table1 中插入一行。

现在,如果在表 1 中,在列 2 中有一行具有值 “a” 的行。因此,此语句将使用 “c” 更新行的 column2 值,其中 column1 值为 “a”。

因此,如果要插入新行,则在主键或唯一索引发生冲突时更新该行。
阅读更多有关此链接的信息