MySQL 实现存在则更新、不存在则插入 (ON DUPLICATE KEY UPDATE)

基础用法：单记录插入或更新

ON DUPLICATE KEY UPDATE 的核心逻辑是：当插入的行会导致表中某个 UNIQUE 索引或 PRIMARY KEY 出现重复值时，MySQL 不会报错，而是执行指定的 UPDATE 语句更新旧行。

假设有一张表 table_name，其中列 a 被定义为 UNIQUE。以下两条 SQL 语句具有相同的效果：

第1步：定义插入并更新的目标字段

首先编写基础的插入语句，并在末尾添加更新逻辑。例如，将 a=1 的记录中的 c 字段加 1：

INSERT INTO table_name (a,b,c) 
VALUES (1,2,3) 
ON DUPLICATE KEY UPDATE c=c+1;

或者等价的传统 Update 写法：

UPDATE table_name SET c=c+1 WHERE a=1;

关键区别与返回值：

• 如果行作为新记录被插入（无冲突），受影响行的值为 1。
• 如果原有的记录被更新（有冲突），受影响行的值为 2。

多唯一键冲突时的处理策略

如果表中有多个唯一列（例如 a 和 b 都是唯一列），INSERT ... ON DUPLICATE KEY UPDATE 的行为可能不符合预期。当插入导致多个唯一键冲突时，只有一行会被更新，具体取决于内部优化器的选择。

例如，以下语句：

INSERT INTO table_name (a,b,c) VALUES (1,2,3) 
ON DUPLICATE KEY UPDATE c=c+1;

在 a=1 OR b=2 同时成立的情况下，其效果等同于：

UPDATE table_name SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

建议：为了避免不可预知的更新结果，通常应尽量保证 ON DUPLICATE KEY UPDATE 仅针对单一主键或唯一键生效，避免对带有多个唯一关键字的复杂条件使用此子句。

高级技巧：使用 VALUES() 函数引用插入值

在 UPDATE 子句中，可以使用 VALUES(col_name) 函数来引用原本打算插入的列值。这对于需要从插入值计算更新值的场景非常有用，特别是在批量插入时。

第2步：构建多行批量插入与更新逻辑

考虑需要插入两行数据，若冲突则将 c 更新为 a+b 的和：

INSERT INTO table_name (a,b,c) 
VALUES (1,2,3),(4,5,6) 
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

逻辑解析：

• 对于第一行 (1,2,3)：如果冲突，c 将被更新为 VALUES(a)+VALUES(b) 即 1+2=3。
• 对于第二行 (4,5,6)：如果冲突，c 将被更新为 VALUES(a)+VALUES(b) 即 4+5=9。

这等价于分别执行：

INSERT INTO table_name (a,b,c) VALUES (1,2,3) 
ON DUPLICATE KEY UPDATE c=3;

INSERT INTO table_name (a,b,c) VALUES (4,5,6) 
ON DUPLICATE KEY UPDATE c=9;

注意：VALUES() 函数仅在 INSERT ... ON DUPLICATE KEY UPDATE 语句上下文中有效，在其他查询中调用会返回 NULL。

注意事项与限制

1. DELAYED 选项忽略：当使用 ON DUPLICATE KEY UPDATE 时，INSERT 语句中的 DELAYED 选项将被忽略，因为延迟插入无法正确处理潜在的键冲突。
2. AUTO_INCREMENT 变化：如果使用 LAST_INSERT_ID()，它会在发生冲突时返回旧行的 ID，而在插入新行时返回新 ID，利用这一点可以在应用中准确获取最终插入或更新后的记录 ID。
3. 触发器行为：请注意，UPDATE 部分触发的触发器类型可能与普通 INSERT 不同，需在业务设计中予以考虑。

总结

ON DUPLICATE KEY UPDATE 是 MySQL 中处理“Upsert”（更新或插入）操作的标准解决方案。它通过将逻辑集中在一条 SQL 语句中，减少了网络往返次数和并发竞争风险，尤其适合高并发下的缓存更新、计数器累加等场景。掌握 VALUES() 函数的使用，可以进一步简化多行数据的处理逻辑。

以上就是 MySQL ON DUPLICATE KEY UPDATE 实现存在则更新、不存在则插入的详细内容，更多关于 MySQL 高级查询优化的资料请关注本站其它相关文章！