如何通过ON DUPLICATE KEY UPDATE在MySQL中实现数据不存在即插入，存在即更新操作？

本文共计922个文字，预计阅读时间需要4分钟。

它依赖于唯一约束（UNIQUE）或主键（PRIMARY KEY）。当触发冲突时，它不是依赖于业务层再次判断，而是直接插入某列违反了唯一索引。此时，MySQL会抛出错误，转而执行UPDATE子句——这个机制本质上不检查表、不锁定（除非必要的外锁），比INSERT ... SELECT WHERE NOT EXISTS或先SELECT再分支逻辑更原子上、更高效。

注意：ON DUPLICATE KEY UPDATE 不会响应普通 WHERE 条件匹配，只响应**索引冲突**。没建唯一索引？这条语句就退化成普通 INSERT，冲突也不会触发更新。

必须提前建好唯一索引，否则 ON DUPLICATE KEY UPDATE 不生效

常见错误是写了 ON DUPLICATE KEY UPDATE 却始终走插入、从不更新，根本原因是目标列上没定义 UNIQUE 或 PRIMARY KEY 约束。

• 想按 email 去重？得先执行：

  ALTER TABLE users ADD UNIQUE (email);

• 想按 tenant_id + config_key 联合去重？得建联合唯一索引：

  ALTER TABLE configs ADD UNIQUE (tenant_id, config_key);

• 主键天然满足条件，所以用 id 冲突也能触发更新，但通常这不是你想要的“业务去重维度”

UPDATE 子句里不能直接引用 VALUES() 中未在 INSERT 列表出现的字段

VALUES(col_name) 只能取当前 INSERT 语句中明确列出的列值。如果漏写了某列，又在 UPDATE 里写 col_name = VALUES(col_name)，MySQL 会报错：Column 'col_name' cannot be null 或 Unknown column 'col_name' in 'field list'。

正确写法示例（以用户表为例，按 email 去重）：

INSERT INTO users (email, name, status) VALUES ('alice@example.com', 'Alice', 'active') ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status), updated_at = NOW();

• email 是唯一键，必须出现在 INSERT 列表中（否则无法触发冲突）
• name 和 status 出现在 INSERT 列表里，才能用 VALUES(name)
• updated_at 没在 INSERT 里出现，所以不能写 VALUES(updated_at)，得显式赋值，比如 NOW()

影响行数返回值容易误解：成功插入返回 1，更新返回 2，重复主键冲突且无更新字段时可能返回 0

MySQL 的 mysql_affected_rows()（或客户端对应 API）对 ON DUPLICATE KEY UPDATE 的返回值有特殊规则：

• 新插入一行 → 返回 1
• 触发更新（哪怕 SET 的值和原值一样）→ 返回 2
• 触发冲突，但 UPDATE 子句里所有字段都设为当前值（例如 name = name），且没有实际变更 → 返回 0（注意：不是报错，是“0 行受影响”）

这意味着不能单靠返回值是否为 0 来判断“是否发生冲突”，更不能把它当成“是否执行了更新”的可靠依据。真要区分场景，得结合日志、业务字段（如 updated_at 是否变化）或额外查一次。

实际中最容易被忽略的一点：当多个唯一索引同时存在时，MySQL 只响应**第一个匹配的唯一冲突**，不会因为另一个唯一索引也冲突就多触发一次更新——顺序取决于索引定义顺序，而非 SQL 里写的条件。