mysql - “ INSERT IGNORE”与“ INSERT…ON DUPLICATE KEY UPDATE”(“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”)

Question

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure.

(在执行包含多行的INSERT语句时，我想跳过重复的条目，否则它们会导致失败。)

After some research, my options appear to be the use of either:

(经过研究后，我的选择似乎是使用以下任一方法：)

• ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or

  (ON DUPLICATE KEY UPDATE ，这意味着要付出一定的代价进行不必要的更新，或者)

• INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced.

  (INSERT IGNORE ，这暗示了其他类型的失败通知。)

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?

(跳过可能导致重复的行并继续其他行的最佳方法是什么？)

  ask by Thomas G Henry translate from so

Answer 1

I would recommend using INSERT...ON DUPLICATE KEY UPDATE .

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

If you use INSERT IGNORE , then the row won't actually be inserted if it results in a duplicate key.

(如果使用INSERT IGNORE ，那么如果该行导致重复的键，则实际上不会插入该行。)

But the statement won't generate an error.

(但是该语句不会产生错误。)

It generates a warning instead.

(而是生成警告。)

These cases include:

(这些情况包括：)

• Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.

  (在具有PRIMARY KEY或UNIQUE约束的列中插入重复键。)

• Inserting a NULL into a column with a NOT NULL constraint.

  (将NULL插入具有NOT NULL约束的列中。)

• Inserting a row to a partitioned table, but the values you insert don't map to a partition.

  (在分区表中插入一行，但是您插入的值不会映射到分区。)

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

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

• A new auto-increment ID is allocated.

  (分配了一个新的自动增量ID。)

• Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE .

  (带有外键的相关行可能会被删除（如果您使用级联外键），否则可能会导致无法REPLACE 。)

• Triggers that fire on DELETE are executed unnecessarily.

  (触发执行DELETE触发器是不必要的。)

• Side effects are propagated to replicas too.

  (副作用也传播到副本。)

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL.

(修正： REPLACE和INSERT...ON DUPLICATE KEY UPDATE都是针对MySQL的非标准专有技术。)

ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

(ANSI SQL 2003定义了一个MERGE语句，该语句可以解决相同的需求（甚至更多），但是MySQL不支持MERGE语句。)

---

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.

(该编辑尝试添加一个声明，即INSERT...ON DUPLICATE KEY UPDATE中INSERT...ON DUPLICATE KEY UPDATE会导致分配新的自动增量ID。)

It's true that the new id is generated , but it is not used in the changed row.

(确实会生成新的id，但是更改后的行中不会使用它。)

See demonstration below, tested with Percona Server 5.5.28.

(请参见下面的演示，该演示已通过Percona Server 5.5.28测试。)

The configuration variable innodb_autoinc_lock_mode=1 (the default):

(配置变量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 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

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u .

(上面的示例说明IODKU语句检测到重复项，并调用更新以更改u的值。)

Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

(请注意， AUTO_INCREMENT=3表示生成了一个ID，但未在行中使用。)

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment 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 |
+----+------+