“插入忽略"vs“插入...在重复密钥更新"

2022-01-09 00:00:00 insert mysql

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

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

  • ON DUPLICATE KEY UPDATE 这意味着需要付出一些代价进行不必要的更新，或者
  • INSERT IGNORE 暗示其他类型的失败会在未经通知的情况下溜进来.
  • ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
  • INSERT IGNORE implies an invitation for other kinds of failure to slip in unannounced.

我的这些假设是否正确?简单地跳过可能导致重复的行并继续到其他行的最佳方法是什么?

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

推荐答案

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

如果您使用 INSERT IGNORE，则如果它导致重复键，则不会实际插入该行.但该语句不会产生错误.它会生成一个警告.这些案例包括:

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

  • 在具有 PRIMARY KEY 或 UNIQUE 约束的列中插入重复键.
  • 将 NULL 插入具有 NOT NULL 约束的列中.
  • 向分区表插入一行，但您插入的值未映射到分区.
  • Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
  • Inserting a NULL into a column with a NOT NULL constraint.
  • Inserting a row to a partitioned table, but the values you insert don't map to a partition.

如果你使用 REPLACE，MySQL 实际上会在内部执行 DELETE 后跟 INSERT，这会产生一些意想不到的副作用:

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

  • 分配了一个新的自增 ID.
  • 可能会删除具有外键的相关行(如果您使用级联外键)，否则会阻止 REPLACE.
  • 在 DELETE 上触发的触发器被不必要地执行.
  • 副作用也会传播到副本.
  • A new auto-increment ID is allocated.
  • Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
  • Triggers that fire on DELETE are executed unnecessarily.
  • Side effects are propagated to replicas too.

更正: REPLACE 和 INSERT...ON DUPLICATE KEY UPDATE 都是 MySQL 特有的非标准专有发明.ANSI SQL 2003 定义了一个 MERGE 语句，可以解决同样的需求(以及更多)，但 MySQL 不支持 MERGE 语句.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

一位用户试图编辑这篇文章(编辑被版主拒绝).编辑试图添加一个声明，即 INSERT...ON DUPLICATE KEY UPDATE 会导致分配一个新的自动增量 ID.确实生成了新的id，但是没有在改变的行中使用.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

参见下面的演示，使用 Percona Server 5.5.28 进行测试.配置变量innodb_autoinc_lock_mode=1(默认):

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

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 fooG
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 fooG
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，但未在行中使用.

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

而 REPLACE 确实删除了原始行并插入了一个新行，生成 并且 存储了一个新的自动增量 ID:

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

相关文章