"INSERT IGNORE"；与“在复制密钥更新时插入..."相对应； [英] "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

问题描述

在执行包含许多行的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 which 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约束的列中插入重复的键.
• 将空值插入具有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是 generate 生成的，但在更改后的行中不使用它.

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

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 |
+----+------+

这篇关于"INSERT IGNORE"；与“在复制密钥更新时插入..."相对应；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！