MySQL INSERT ... ON DUPLICATE KEY UPDATE 怎样区分插入和更新

我在做幂等写入时，最容易误判的不是 SQL 能不能执行，而是执行完之后不知道这次到底新增了记录，还是命中了唯一键并更新了旧记录。MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 已经提供了可用的判断信号，关键是不要只看“没有报错”。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/insert-on-duplicate.html

单行写入时，可以先用受影响行数判断：默认情况下 1 表示插入新行，2 表示重复键后确实更新了旧行，0 表示重复键分支执行了但最终值没有变化。若连接启用了 CLIENT_FOUND_ROWS，最后一种情况会返回 1，因此驱动配置必须纳入判断。

先把“冲突对象”固定下来

这条语句不是“先查再决定插入还是更新”。MySQL 会尝试插入；如果待插入值与 UNIQUE 或 PRIMARY KEY 冲突，就转入 UPDATE 分支。因此，业务上想判断“是同一个用户的幂等写入”，必须先保证冲突键确实表达了这个身份。

-- user_id 是幂等身份；首次写入插入，重复写入更新显示名
CREATE TABLE user_profile (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL UNIQUE,
    display_name VARCHAR(80) NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

-- 新值通过 row alias 传给 UPDATE 分支，避免继续依赖已弃用的 VALUES() 写法
INSERT INTO user_profile (user_id, display_name, updated_at)
VALUES (42, '林默', CURRENT_TIMESTAMP) AS new
ON DUPLICATE KEY UPDATE
    display_name = new.display_name,
    updated_at = new.updated_at;

这里的 user_id 是判断冲突的边界，id 是数据库内部主键。不要因为 UPDATE 分支执行了，就把它当成“插入失败”；它是这条幂等写入语句设计的一部分。

用 affected rows 区分单行结果

对单行语句，默认连接行为可以按下面的表理解。这里的“2”不是更新了两行，而是 MySQL 用一个受影响行数值表示“已有行被更新”。

可以把判断集中在数据访问层，不要让控制器直接猜测 SQL 分支：

-- 先执行单行幂等写入，再在同一连接读取受影响行数
INSERT INTO user_profile (user_id, display_name, updated_at)
VALUES (42, '林默', CURRENT_TIMESTAMP) AS new
ON DUPLICATE KEY UPDATE
    display_name = new.display_name,
    updated_at = new.updated_at;

-- 这里的读取必须使用同一数据库连接
SELECT ROW_COUNT() AS affected_rows;

如果驱动已经把受影响行数作为执行结果返回，优先使用驱动结果；如果需要用 ROW_COUNT()，必须保证它紧跟在同一个连接、同一个会话里执行。连接池切换连接后再读取，得到的就不是这条写入的结果。

CLIENT_FOUND_ROWS 会改变“无变化”判断

官方文档特别指出，启用 CLIENT_FOUND_ROWS 后，原本“更新为当前值”返回 0 的情况会返回 1。也就是说，不能把“返回 1”永远解释为新增。

实际排查时，我会先确认驱动的连接选项，再决定是否使用下面这张判断表：

因此，在启用该选项的系统里，新增与无变化都可能是 1。若业务必须区分这两者，可以额外比较写入前后的业务字段，或者让 SQL 更新一个明确的版本字段并由数据访问层返回更细的状态；不要仅凭一个数字做绝对推断。

需要行 ID 时，用 LAST_INSERT_ID 做第二个信号

如果表有 AUTO_INCREMENT 主键，官方文档说明这条语句插入或更新一行后，LAST_INSERT_ID() 可以返回该行的自增值。这个信号适合把“结果类型”和“目标行 ID”一起交给上层。

-- id 是 AUTO_INCREMENT 主键；语句和读取必须保持同一连接
INSERT INTO user_profile (user_id, display_name, updated_at)
VALUES (42, '林默', CURRENT_TIMESTAMP) AS new
ON DUPLICATE KEY UPDATE
    display_name = new.display_name,
    updated_at = new.updated_at;

-- 读取本次语句关联的自增主键
SELECT LAST_INSERT_ID() AS profile_id;

这里有两个容易忽略的前提：第一，读取必须在同一会话中完成；第二，业务代码不能把“拿到 ID”误当成“本次一定新增”。更新分支同样可能拿到旧行的自增 ID，所以状态仍应由 affected rows 或其他业务信号判断。

多唯一键和自冲突不能当普通更新

如果表上有多个唯一索引，待插入数据可能同时撞上多个候选行。MySQL 官方文档提醒，这类 ON DUPLICATE KEY UPDATE 通常只会更新一行，因此不适合用来表达含糊的“任意一个唯一键都可以匹配”的业务规则。

另一个危险情况是：插入值先命中一个唯一键，而 UPDATE 赋值又制造了另一个唯一键冲突。这不是“返回 2 的普通更新”，而是应按错误处理；是否使用 IGNORE 还会改变后续多行写入的行为，不能为了让接口不报错就直接加上。

-- 更新后的 b 不能撞上另一条唯一键；先明确错误处理策略
CREATE TABLE account_alias (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    login_name VARCHAR(80) NOT NULL UNIQUE,
    external_code VARCHAR(80) NOT NULL UNIQUE
);

-- 可能先命中 login_name，再让 external_code 撞上其他行
INSERT INTO account_alias (login_name, external_code)
VALUES ('linmo', 'partner-002') AS new
ON DUPLICATE KEY UPDATE external_code = new.external_code;

这类表更适合先在模型层定义唯一身份，或者拆开不同的同步路径，保证一次幂等写入只对应一个明确的冲突键。

在驱动层落成可解释的状态

最终不要把数据库的数字原样暴露给业务调用方，可以在数据访问层转换成明确状态：

• 默认连接且单行写入：1 映射为 created，2 映射为 updated，0 映射为 unchanged。
• 启用了 CLIENT_FOUND_ROWS：1 只能表示“插入或无变化”，需要额外业务字段或版本信号继续区分。
• 需要关联主键：在同一连接读取 LAST_INSERT_ID()，不要重新取连接后再读。
• 出现唯一键错误：按冲突错误处理，不要把重试后的结果伪装成普通更新。
• 多行写入：不要直接套用单行的 1/2/0 表格，按驱动对 affected rows 的定义处理聚合结果。

我自己的复查顺序是：先确认唯一键表达的业务身份，再确认连接选项，接着记录 affected rows 和目标 ID，最后才给接口返回 created、updated 或 unchanged。这样排查时每个状态都有来源，也能快速定位是 SQL 分支、驱动配置还是表结构造成的误判。

常见问题

返回 1 就一定是插入吗？

不一定。默认连接下，1 通常表示插入；但启用 CLIENT_FOUND_ROWS 后，更新为相同值也可能返回 1。

返回 2 是更新了两行吗？

不是。对单行语句，它表示一个已有行发生了更新，数值 2 是 MySQL 的受影响行数语义。

为什么更新分支也能拿到 LAST_INSERT_ID？

对于包含 AUTO_INCREMENT 列的表，MySQL 文档说明插入或更新后都可以返回该行的自增值；它代表目标行，不代表本次一定新建。

可以先 SELECT 再决定 INSERT 或 UPDATE 吗？

如果目标是幂等写入，通常不需要把判断拆成两个请求。用唯一键和单条 upsert 让数据库处理冲突，再读取这条语句的结果信号更直接；具体事务要求仍要结合业务并发模型设计。

记住这条边界就够了：ON DUPLICATE KEY UPDATE 负责把唯一键冲突转成更新机会，affected rows 负责描述单行结果，LAST_INSERT_ID 负责带回目标行标识；驱动选项和表上的多个唯一键，则决定这些信号能不能直接解释。