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

Question

提问by Thomas G Henry

While executing an INSERTstatement 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:

在执行INSERT包含多行的语句时，我想跳过否则会导致失败的重复条目。经过一些研究，我的选择似乎是使用：

ON DUPLICATE KEY UPDATEwhich implies an unnecessary update at some cost, or
INSERT IGNOREwhich implies an invitation for other kinds of failure to slip in unannounced.

ON DUPLICATE KEY UPDATE这意味着以某种代价进行不必要的更新，或
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?

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

Answer 1

回答by Bill Karwin

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. But the statement won't generate an error. It generates a warning instead. These cases include:

如果您使用INSERT IGNORE，那么如果导致重复键，则实际上不会插入该行。但该语句不会产生错误。相反，它会生成警告。这些案例包括：

Inserting a duplicate key in columns with PRIMARY KEYor UNIQUEconstraints.
Inserting a NULL into a column with a NOT NULLconstraint.
Inserting a row to a partitioned table, but the values you insert don't map to a partition.

在带有PRIMARY KEY或UNIQUE约束的列中插入重复键。
将 NULL 插入具有NOT NULL约束的列中。
向分区表插入一行，但您插入的值不会映射到分区。

If you use REPLACE, MySQL actually does a DELETEfollowed by an INSERTinternally, which has some unexpected side effects:

如果你使用REPLACE，MySQL 实际上DELETE在INSERT内部做了一个后跟一个，这有一些意想不到的副作用：

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 DELETEare executed unnecessarily.
Side effects are propagated to replicas too.

分配了一个新的自增 ID。
带有外键的相关行可能会被删除（如果您使用级联外键）或者阻止REPLACE.
触发的触发器DELETE被不必要地执行。
副作用也会传播到副本。

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

修正：既REPLACE和INSERT...ON DUPLICATE KEY UPDATE是非标准的，私有的发明具体到MySQL。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 UPDATEcauses 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.

用户试图编辑此帖子（编辑被版主拒绝）。编辑试图添加INSERT...ON DUPLICATE KEY UPDATE导致分配新的自动增量 ID 的声明。确实生成了新的 id ，但是在更改的行中没有使用它。

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

请参阅下面的演示，使用 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

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

上面演示了 IODKU 语句检测到重复项，并调用更新来更改的值u。请注意，这AUTO_INCREMENT=3表示生成了 id，但未在行中使用。

Whereas REPLACEdoes delete the original row and inserts a new row, generating andstoring 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 |
+----+------+

Answer 2

回答by Paulus Maximus

In case you want to see what this all means, here is a blow-by-blow of everything:

如果您想了解这一切意味着什么，这里是所有内容的详细介绍：

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

Primary key is based on both columns of this quick reference table. A Primary key requires unique values.

主键基于此快速参考表的两列。主键需要唯一值。

Let's begin:

让我们开始：

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

note, the above saved too much extra work by setting the column equal to itself, no update actually needed

注意，上面通过将列设置为等于自身节省了太多额外的工作，实际上不需要更新

REPLACE INTO users_partners (uid,pid) VALUES (1,1)
...2 row(s) affected

and now some multiple row tests:

现在进行一些多行测试：

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

no other messages were generated in console, and it now has those 4 values in the table data. I deleted everything except (1,1) so I could test from the same playing field

控制台中没有生成其他消息，现在表数据中有这 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

So there you have it. Since this was all performed on a fresh table with nearly no data and not in production, the times for execution were microscopic and irrelevant. Anyone with real-world data would be more than welcome to contribute it.

所以你有它。由于这一切都是在几乎没有数据且不在生产中的新表上执行的，因此执行时间是微观且无关紧要的。任何拥有真实世界数据的人都非常欢迎贡献它。

Answer 3

回答by Jens

Something important to add: When using INSERT IGNORE and you do have key violations, MySQL does NOT raise a warning!

补充一点很重要：当使用 INSERT IGNORE 并且您确实有密钥违规时，MySQL 不会发出警告！

If you try for instance to insert 100 records at a time, with one faulty one, you would get in interactive mode:

例如，如果您尝试一次插入 100 条记录，其中一条有错误，您将进入交互模式：

Query OK, 99 rows affected (0.04 sec)
Records: 100 Duplicates: 1 Warnings: 0

As you see: No Warnings! This behavior is even wrongly described in the official Mysql Documentation.

如您所见：没有警告！这种行为甚至在官方 Mysql 文档中被错误地描述。

If your script needs to be informed, if some records have not been added (due to key violations) you have to call mysql_info() and parse it for the "Duplicates" value.

如果需要通知您的脚本，如果某些记录尚未添加（由于密钥冲突），您必须调用 mysql_info() 并将其解析为“重复”值。

Answer 4

回答by David Z

I routinely use INSERT IGNORE, and it sounds like exactly the kind of behavior you're looking for as well. As long as you know that rows which would cause index conflicts will not be inserted and you plan your program accordingly, it shouldn't cause any trouble.

我经常使用INSERT IGNORE，这听起来也正是您正在寻找的行为类型。只要您知道不会插入会导致索引冲突的行并相应地规划您的程序，就不应该造成任何麻烦。

Answer 5

回答by Chris

I know this is old, but I'll add this note in case anyone else (like me) arrives at this page while trying to find information on INSERT..IGNORE.

我知道这是旧的，但我会添加此注释，以防其他人（如我）在尝试查找有关 INSERT..IGNORE 的信息时到达此页面。

As mentioned above, if you use INSERT..IGNORE, errors that occur while executing the INSERT statement are treated as warnings instead.

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

One thing which is not explicitly mentioned is that INSERT..IGNORE will cause invalid values will be adjusted to the closest values when inserted (whereas invalid values would cause the query to abort if the IGNORE keyword was not used).

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

Answer 6

回答by Chris KL

ON DUPLICATE KEY UPDATE is not reallyin the standard. It's about as standard as REPLACE is. See SQL MERGE.

ON DUPLICATE KEY UPDATE 并不是真正的标准。它与 REPLACE 一样标准。请参阅SQL 合并。

Essentially both commands are alternative-syntax versions of standard commands.

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

Answer 7

回答by IEnumerator

ReplaceInto seems like an option. Or you can check with

Replace进入似乎是一种选择。或者你可以检查

IF NOT EXISTS(QUERY) Then INSERT

This will insert or delete then insert. I tend to go for a IF NOT EXISTScheck first.

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

Answer 8

回答by LOL

Potential danger of INSERT IGNORE. If you are trying to insert VARCHAR value longer then column was defined with - the value will be truncated and inserted EVEN IF strict mode is enabled.

INSERT IGNORE 的潜在危险。如果您尝试插入更长的 VARCHAR 值，则列定义为 - 该值将被截断并插入，即使启用了严格模式。

Answer 9

回答by Ray Foss

If using insert ignorehaving a SHOW WARNINGS;statement at the end of your query set will show a table with all the warnings, including which IDs were the duplicates.

如果使用insert ignore具有SHOW WARNINGS;在查询集的末尾账单会显示一个表，所有的警告，包括哪个ID是重复的。

Answer 10

回答by Dilraj Singh

If you want to insert in the table and on the conflict of the primary key or unique index it will update the conflicting row instead of inserting that row.

如果你想在表中插入和主键或唯一索引的冲突，它将更新冲突的行而不是插入该行。

Syntax:

句法：

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

Now here, this insert statement may look different what you have seen earlier. This insert statement trying to insert a row in table1 with the value of a and b into column column1 and column2 respectively.

现在，这个插入语句可能与您之前看到的有所不同。这个插入语句试图将表 1 中的行与 a 和 b 的值分别插入列 column1 和 column2。

Let's understand this statement in depth:

让我们深入理解这个说法：

For example: here column1 is defined as the primary key in table1.

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

Now if in table1 there is no row having the value “a” in column1. So this statement will insert a row in the table1.

现在，如果在 table1 中，column1 中没有值为“a”的行。所以这条语句会在table1中插入一行。

Now if in table1 there is a row having the value “a” in column2. So this statement will update the row's column2 value with “c” where the column1 value is “a”.

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

So if you want to insert a new row otherwise update that row on the conflict of the primary key or unique index.
Read more on this link

因此，如果您想插入新行，则在主键或唯一索引冲突时更新该行。
在此链接上阅读更多信息

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

提问by Thomas G Henry

回答by Bill Karwin

回答by Paulus Maximus

回答by Jens

回答by David Z

回答by Chris

回答by Chris KL

回答by IEnumerator

回答by LOL

回答by Ray Foss

回答by Dilraj Singh

相关推荐

最近更新

标签

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

提问by Thomas G Henry

回答by Bill Karwin

回答by Paulus Maximus

回答by Jens

回答by David Z

回答by Chris

回答by Chris KL

回答by IEnumerator

回答by LOL

回答by Ray Foss

回答by Dilraj Singh

相关推荐

MySQL：删除一行忽略外键约束

MySQL MySQL查询GROUP BY日/月/年

MySQL 错误代码 1166。列名不正确

MySQL 获取两个日期之间的日期列表

相关推荐

最近更新

标签