INSERT ... ON DUPLICATE KEY（不执行任何操作）

Question

我有一个表，其中两列有一个唯一键：

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 

？

Answer 1

是的，使用 INSERT ... ON DUPLICATE KEY UPDATE id=id （即使将 id 分配给自身，它也不会触发行更新）。

如果您不关心错误（转换错误、外键错误）和自动增量字段耗尽（即使由于重复键而未插入行，它也会递增），则可以像这样使用 INSERT IGNORE ：

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

Answer 2

如何实现“如果不存在则插入”？

1. 替换为

优点：

1. 简单。

缺点：

1. 太慢。 。

2. 如果有条目匹配唯一键或主键，则自动递增键将更改（增加1），因为它会删除旧条目然后插入新条目。

2. INSERT IGNORE

优点：

1. 简单。

缺点：

1. 如果有条目匹配唯一键或主键，自动增量键不会改变，但自动增量索引会增加 1

2. ，但自动递增索引将增加1

   some其他错误/警告将被忽略，例如数据转换错误。

3. INSERT ... ON DUPLICATE KEY UPDATE

优点：

1. 您可以使用此功能轻松实现“保存或更新”功能

缺点：

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

2. 如果有条目匹配唯一键或主键，自动增量键不会改变，但自动增量索引将增加1

4. 如果有任何方法可以阻止自动增量 key 增加条目是否匹配唯一键或主键？

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

Answer 3

使用ON DUPLICATE KEY UPDATE ...，
否定：因为 UPDATE 使用第二个操作的资源。

使用INSERT IGNORE ...，
负面：如果出现问题，MySQL 不会显示任何错误，因此您无法处理错误。仅当您不关心查询时才使用它。

Answer 4

您可以使用目标表的左外连接并在WHERE target.value is null上进行过滤来实现此目的。如果没有选择插入匹配的行，您不必担心自动递增或更新。

最近，我想将用户的所有用户 ID 和首选项中不存在行的所有首选项 ID 插入到我的 user_preferences 表中。

INSERT INTO user_preferences (user_id, preferences_id, selected_value)
    SELECT DISTINCT u.id as user_id, p.id as preferences_id, p.selected_value
    FROM users u
    CROSS JOIN preferences p
    LEFT OUTER JOIN user_preferences as target on target.user_id = u.id and target.preferences_id = p.id 
    WHERE target.id is null 

在这里，我选择用户 ID 和首选项 ID 的列表（为了您的目的，忽略交叉连接，我只想为每个参考 ID 提供一个用户 ID 行），并在目标中尚不应该存在的两个值上进行左外连接表，user_preferences。

使用左外连接，右侧表中的所有行都将返回与JOIN条件中指定的左侧表匹配的值，否则将返回空。但这是完美的！由于 user_preferences 中没有与 user_id 或 preferences_id 匹配的值，因此它将匹配为空行，从而选择我们在 user_preferences 中要求的值code>SELECT 子句，意味着我们选择的内容将被插入。

但是，如果您再次运行 select 子句（没有 INSERT 语句），则用户 ID 和首选项 ID 将匹配，因为它们现在位于表中，即它们不会为空，因此它们不会匹配 target.id is null 条件，并且不会选择任何内容，因此不会插入任何内容。

如果您想对单行尝试此操作，只需使用要在连接条件中显式插入的值，就像我可以说的那样 target.user_id = 9 和 target.preferences_id = 23 ，因为它们不存在于右侧表中，我会返回一个空行，并且 select 9, 23, 'enable_dark_scheme' 将被返回并因此被插入。

非常方便的部分是您可以单独运行选择作为将插入的内容的预览。