問題描述
在執行包含多行的 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 UPDATEwhich implies an unnecessary update at some cost, orINSERT IGNOREimplies 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 KEYorUNIQUEconstraints. - 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.
如果你使用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上觸發的觸發器. - 副作用也會傳播到副本.
更正: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 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 |
+----+------+
這篇關于“插入忽略"與“插入......在重復的密鑰更新上"的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！