MySQL ON DUPLICATE KEY UPDATE 语法详解与多行更新实战

核心概念与工作原理

ON DUPLICATE KEY UPDATE 是 MySQL 的特有语法（非 SQL 标准），旨在简化 "Upsert"（Insert or Update）操作。当执行 INSERT 语句时，如果插入的行会导致表中现有的 UNIQUE 索引或 PRIMARY KEY 出现重复值，MySQL 不会抛出错误，而是执行 AFTER 子句指定的 UPDATE 操作；若未引发冲突，则正常插入新行。

第1步：配置主键或唯一索引约束

确保目标表中的列已定义为主键或拥有 UNIQUE 索引，这是触发该语法的先决条件。

第2步：构建基础插入或更新语句

以表 TABLE 为例，假设列 a 为 UNIQUE 索引或主键，且当前已存在值 1。以下两种写法效果等价：

-- 方式一：使用 ON DUPLICATE KEY UPDATE
INSERT INTO TABLE (a, c) VALUES (1, 3) 
ON DUPLICATE KEY UPDATE c = c + 1;

-- 方式二：传统 SELECT 判断后 UPDATE
UPDATE TABLE SET c = c + 1 WHERE a = 1;

返回值与状态判断

执行包含 ON DUPLICATE KEY UPDATE 的语句后，受影响行数（Affected Rows）能直观反映操作结果：

• 返回 1：表示该行作为新记录被成功插入。
• 返回 2：表示原有记录被成功更新。

注意：如果在插入前使用 SELECT ... FOR UPDATE 锁定了行，行为可能有所不同，但在常规无锁情况下适用上述规则。

多行数据批量冲突处理

在处理大批量数据导入时，通常需要同时插入多条记录。关键在于当多条待插入记录中有多条与现有数据冲突，或者不同批次数据互相冲突时，如何正确指定更新后的字段值？此时需借助 VALUES() 函数。

第3步：使用 VALUES() 引用插入值

VALUES(column_name) 函数用于引用即将插入的值（即 INSERT 部分中的值），而不是当前数据库中的旧值。

场景 A：增量累加

假设对同一主键的多行插入请求进行累加更新：

INSERT INTO TABLE (a, c) VALUES (1, 3), (1, 7) 
ON DUPLICATE KEY UPDATE c = c + 1;

执行结果分析：
由于主键 1 已存在，第二条 (1, 7) 与第一条 (1, 3) 虽然主键相同导致内部处理复杂，但在此类直接连续同主键插入中，主要看最终生效的更新逻辑。通常更推荐清晰的逐条处理或使用 INSERT IGNORE。若在多行不同主键的情况下，例如：
INSERT INTO TABLE (a, b, c) VALUES (1,2,3), (2,5,7), (3,3,6), (4,8,2)
ON DUPLICATE KEY UPDATE b = VALUES(b);

完整流程演示：
假设表中原有记录：(2, 2, 9) 和 (3, 2, 1)。
1. (1, 2, 3)：主键 1 不存在，执行插入。
2. (2, 5, 7)：主键 2 已存在（原值 2），发生冲突，执行更新。根据 b = VALUES(b)，将列 b 更新为插入值 5。结果变为 (2, 5, 9)。
3. (3, 3, 6)：主键 3 已存在（原值 2），发生冲突，执行更新。根据 b = VALUES(b)，将列 b 更新为插入值 3。结果变为 (3, 3, 1)。
4. (4, 8, 2)：主键 4 不存在，执行插入。

场景 B：直接覆盖替换

如果不希望累加，而是直接取用新传入的值覆盖旧值：

INSERT INTO TABLE (a, c) VALUES (1, 3), (1, 7) 
ON DUPLICATE KEY UPDATE c = VALUES(c);

执行结果分析：
当处理 (1, 7) 与已有记录的冲突时，c 将被直接赋值为 7，而非累加。

总结

ON DUPLICATE KEY UPDATE 极大地简化了 MySQL 中“判空插入”的逻辑分支。核心要点在于理解：
1. 触发依赖于唯一索引/主键冲突。
2. 返回值区分插入(1)与更新(2)。
3. 多行更新时，利用 VALUES(col) 获取当前批次即将插入的值进行赋值，是实现高效 Upsert 的关键。

以上就是 MySQL "ON DUPLICATE KEY UPDATE" 语法分析与多行更新实战的详细内容，更多关于 SQL 优化与数据库开发的资料请关注本站其它相关文章！