MySQL INSERT ... ON DUPLICATE KEY（什么都不做）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4596390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT ... ON DUPLICATE KEY (do nothing)
提问by ufk
I have a table with a unique key for two columns:
我有一个表，其中有两列的唯一键：
CREATE TABLE `xpo`.`user_permanent_gift` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`fb_user_id` INT UNSIGNED NOT NULL ,
`gift_id` INT UNSIGNED NOT NULL ,
`purchase_timestamp` TIMESTAMP NULL DEFAULT now() ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `user_gift_UNIQUE` (`fb_user_id` ASC, `gift_id` ASC) );
I want to insert a row into that table, but if the key exists, to do nothing! I don't want an error to be generated because the keys exist.
我想在该表中插入一行，但如果键存在，什么都不做！我不希望因为密钥存在而产生错误。
I know that there is the following syntax:
我知道有以下语法：
INSERT ... ON DUPLICATE KEY UPDATE ...
but is there something like:
但是有没有类似的东西：
INSERT ... ON DUPLICATE KEY DO NOTHING
?
?
回答by ceejayoz
Yes, use INSERT ... ON DUPLICATE KEY UPDATE id=id(it won't trigger row update even though idis assigned to itself).
是的，使用INSERT ... ON DUPLICATE KEY UPDATE id=id（即使id分配给它自己也不会触发行更新）。
If you don't care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it's incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE.
如果您不关心错误（转换错误、外键错误）和自动增量字段耗尽（即使由于重复键没有插入行，它也会增加），然后使用INSERT IGNORE.
回答by Kim
HOW TO IMPLEMENT 'insert if not exist'?
如何实现“如果不存在则插入”？
1. REPLACE INTO
1. REPLACE INTO
pros:
优点：
- simple.
- 简单的。
cons:
缺点：
too slow.
auto-increment key will CHANGE(increase by 1) if there is entry matches
unique keyorprimary key, because it deletes the old entry then insert new one.
太慢了。
如果有条目匹配或，自动递增键将更改（增加 1），因为它删除旧条目然后插入新条目。
unique keyprimary key
2. INSERT IGNORE
2. INSERT IGNORE
pros:
优点：
- simple.
- 简单的。
cons:
缺点：
auto-increment key will not change if there is entry matches
unique keyorprimary keybut auto-increment index will increase by 1some other errors/warnings will be ignored such as data conversion error.
如果有条目匹配
unique key或primary key自增索引将增加1，则自增键不会改变一些其他错误/警告将被忽略，例如数据转换错误。
3. INSERT ... ON DUPLICATE KEY UPDATE
3. INSERT ... ON DUPLICATE KEY UPDATE
pros:
优点：
- you can easily implement 'save or update' function with this
- 您可以使用此轻松实现“保存或更新”功能
cons:
缺点：
looks relatively complex if you just want to insert not update.
auto-increment key will not change if there is entry matches
unique keyorprimary keybut auto-increment index will increase by 1
如果您只想插入而不是更新，则看起来相对复杂。
如果有条目匹配
unique key或primary key自增索引将增加1，则自增键不会改变
4. Any way to stop auto-increment key increasing if there is entry matches unique keyor primary key?
4.如果有条目匹配unique key或，有primary key什么方法可以停止自动递增键的增加？
As mentioned in the comment below by @toien: "auto-increment column will be effected depends on innodb_autoinc_lock_modeconfig after version 5.1" if you are using innodbas your engine, but this also effects concurrency, so it needs to be well considered before used. So far I'm not seeing any better solution.
正如@toien在下面的评论中提到的：innodb_autoinc_lock_mode如果您innodb用作引擎，“自动增量列将取决于5.1版之后的配置” ，但这也会影响并发性，因此在使用前需要仔细考虑。到目前为止，我没有看到任何更好的解决方案。
回答by Abdul Aziz Al Basyir
Use ON DUPLICATE KEY UPDATE ...,
Negative : because the UPDATEuses resources for the second action.
Use ON DUPLICATE KEY UPDATE ...,
Negative ：因为UPDATE第二个动作使用资源。
Use INSERT IGNORE ...,
Negative : MySQL will not show any errors if something goes wrong, so you cannot handle the errors. Use it only if you don't care about the query.
使用INSERT IGNORE ...，
否定：如果出现问题，MySQL 不会显示任何错误，因此您无法处理错误。仅当您不关心查询时才使用它。
