插入……ON重复键(什么都不做)

我有一个表的唯一键为两列:

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) );

我想插入一行到那个表，但如果键存在，什么都不做!我不希望因为键存在而产生错误。

我知道有以下语法:

INSERT ... ON DUPLICATE KEY UPDATE ...

但是有没有这样的东西:

INSERT ... ON DUPLICATE KEY DO NOTHING

?

203703 次浏览
小开
最佳答案

是的，使用INSERT ... ON DUPLICATE KEY UPDATE id=id(即使id被赋值给自己，它也不会触发行更新)。

如果你不关心错误(转换错误，外键错误)和自动递增字段耗尽(即使由于重复键而没有插入行，它也会递增)，那么像这样使用INSERT IGNORE:

INSERT IGNORE INTO <table_name> (...) VALUES (...)
小开

使用ON DUPLICATE KEY UPDATE ...，
否定:因为UPDATE为第二个动作使用了资源

使用INSERT IGNORE ...，
负面:MySQL将不会显示任何错误，如果出现错误，所以你不能处理错误。只有当你不关心查询时才使用它

小开

如何实现'插入如果不存在'?

1. REPLACE INTO

优点:

  1. 简单。

缺点:

  1. 太慢了。

  2. 如果有匹配unique key或primary key的条目，auto-increment key将CHANGE(增加1)，因为它会删除旧条目，然后插入新条目。

2. INSERT IGNORE

优点:

  1. 简单。

缺点:

  1. 如果有匹配unique key或primary key的条目，自动递增键将不会改变，但自动递增索引将增加1

  2. 其他一些错误/警告将被忽略，如数据转换错误。

3.INSERT ... ON DUPLICATE KEY UPDATE

优点:

  1. 你可以很容易地实现“保存或更新”功能

缺点:

  1. 如果你只是想插入而不是更新，看起来相对复杂。

  2. 如果有匹配unique key或primary key的条目，自动递增键将不会改变，但自动递增索引将增加1

4. 如果有匹配unique key或primary key的条目，有什么方法可以停止自动递增键的增加?

正如下面@toien的评论中提到的:“自动递增列将在5.1版后依赖于innodb_autoinc_lock_mode配置”，如果你使用innodb作为引擎，但这也会影响并发性，所以在使用之前需要充分考虑。到目前为止，我还没有看到任何更好的解决方案。


提交答案