用 INSERT … ON DUPLICATE KEY UPDATE 实现幂等写入

消息队列重投、用户连点两次提交、定时任务因为上一次超时被重新调度——这些场景都会让同一条业务数据被写入两次。如果写入逻辑是「先 SELECT 判断存在再 INSERT」，在并发下会稳定地漏判：两个事务同时 SELECT 都没查到，然后都去 INSERT，最后要么产生两条脏数据，要么撞唯一索引报 Duplicate entry。把判断和写入压进一条 SQL，交给 InnoDB 的唯一索引和行锁去保证原子性，才是可靠的做法。

MySQL 提供的 INSERT ... ON DUPLICATE KEY UPDATE（下称 IODKU）就是这样一个工具：插入时命中唯一索引或主键冲突，就改为执行 UPDATE 分支。它在单条语句内完成「存在则更新、不存在则插入」，天然具备幂等性。但这条语句有几个必须提前搞清楚的坑：返回值语义、自增主键跳号、并发下的死锁、以及 VALUES() 函数的废弃。下面按实际落地顺序展开。

一、先把表和唯一索引设计对

IODKU 的冲突判定完全依赖唯一索引，没有唯一索引它就不会走 UPDATE 分支，而是老老实实插入一条新行。所以第一步是确认业务上的「幂等键」在表结构上有对应的唯一约束。

CREATE TABLE `order_daily_stat` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `stat_date` DATE NOT NULL COMMENT '统计日期',
  `shop_id` BIGINT UNSIGNED NOT NULL COMMENT '店铺ID',
  `order_cnt` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单数',
  `gmv` DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT '成交金额',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_date_shop` (`stat_date`, `shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这里 uk_date_shop 就是幂等键：同一天同一家店只允许一行。任何重复写入都会命中这个唯一索引，转入 UPDATE 分支。

几个设计要点：

• 唯一键要尽量短。唯一索引的列越少、类型越紧凑，冲突检测越快，间隙锁的范围也越小。用 VARCHAR(64) 存业务流水号比用 VARCHAR(255) 好得多。
• 不要用可空列做唯一键。MySQL 唯一索引允许多个 NULL 值，(stat_date, shop_id) 里只要 shop_id 为 NULL，重复插入就不会冲突，幂等直接失效。唯一键列一律 NOT NULL。
• 字符集与排序规则统一。如果唯一键是字符串，而应用传入的值和列排序规则不一致（例如列是 utf8mb4_0900_ai_ci 大小写不敏感，应用以为 ABC 和 abc 是两条），会出现意料之外的「被合并」。反过来，如果列是 utf8mb4_bin 而应用做了大小写归一化，又可能漏判。

二、基础写法与增量更新的正确姿势

最简单的形态是覆盖式更新：

INSERT INTO order_daily_stat (stat_date, shop_id, order_cnt, gmv)
VALUES ('2025-03-01', 1001, 1, 99.00)
ON DUPLICATE KEY UPDATE
  order_cnt = VALUES(order_cnt),
  gmv       = VALUES(gmv);

但业务上更常见的是累加，比如每来一笔订单就把当日订单数 +1、GMV 累加。这时应该写成基于当前行的自增：

INSERT INTO order_daily_stat (stat_date, shop_id, order_cnt, gmv)
VALUES ('2025-03-01', 1001, 1, 99.00)
ON DUPLICATE KEY UPDATE
  order_cnt = order_cnt + 1,
  gmv       = gmv + 99.00;

注意这里直接用了字面量 99.00，而不是 VALUES(gmv)。原因是：UPDATE 分支里引用的是「当前已存在的行」的列值，而 VALUES(col) 引用的是本次 INSERT 想插入的值。两者含义不同，混淆会导致重复累加或累加错误的值。

从 MySQL 8.0.19 起，官方推荐用行别名替代 VALUES() 函数，因为 VALUES() 已被标记为 deprecated，在 8.0.20+ 会产生告警，未来版本会移除：

INSERT INTO order_daily_stat (stat_date, shop_id, order_cnt, gmv)
VALUES ('2025-03-01', 1001, 1, 99.00) AS new_row
ON DUPLICATE KEY UPDATE
  order_cnt = order_daily_stat.order_cnt + new_row.order_cnt,
  gmv       = order_daily_stat.gmv + new_row.gmv;

如果版本低于 8.0.19，只能用 VALUES()。可以先用 SELECT VERSION(); 确认，再决定写法。

常见误写：order_cnt = VALUES(order_cnt) + 1。这在插入新行时结果正确（0+1），但在已有行上会把本次的插入值当作基数，导致数值被重置而非累加。累加场景请把「当前值」和「增量」分开写清楚。

三、返回值语义与自增主键跳号

很多人用 getGeneratedKeys() 或 JDBC 的 executeUpdate() 返回值来判断「这次到底是插入还是更新」，这里有两个坑。

3.1 受影响行数的三种取值

对 IODKU，MySQL 返回的受影响行数（affected rows）规则是：

结果	affected rows	说明
插入新行	1	正常插入
更新已有行，且值确实变化	2	一次更新算两行
更新已有行，但新值与旧值完全相同	0	MySQL 认为没有变化

所以不要用 affected == 1 判断「是否新插入」——当 UPDATE 分支的值与原值相同时返回 0，会被误判成失败。更稳妥的做法是读取自增主键：插入时 LAST_INSERT_ID() 是新的 id；走 UPDATE 分支时，如果 id = LAST_INSERT_ID(id) 被显式写进 UPDATE 子句，LAST_INSERT_ID() 会返回该行已有的 id。

INSERT INTO order_daily_stat (stat_date, shop_id, order_cnt, gmv)
VALUES ('2025-03-01', 1001, 1, 99.00) AS new_row
ON DUPLICATE KEY UPDATE
  id        = LAST_INSERT_ID(order_daily_stat.id),
  order_cnt = order_daily_stat.order_cnt + new_row.order_cnt,
  gmv       = order_daily_stat.gmv + new_row.gmv;

Java 里对应写法：

String sql = "INSERT INTO order_daily_stat (stat_date, shop_id, order_cnt, gmv) " +
             "VALUES (?, ?, ?, ?) AS new_row " +
             "ON DUPLICATE KEY UPDATE " +
             "id = LAST_INSERT_ID(order_daily_stat.id), " +
             "order_cnt = order_daily_stat.order_cnt + new_row.order_cnt, " +
             "gmv = order_daily_stat.gmv + new_row.gmv";

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setDate(1, java.sql.Date.valueOf("2025-03-01"));
    ps.setLong(2, 1001L);
    ps.setInt(3, 1);
    ps.setBigDecimal(4, new java.math.BigDecimal("99.00"));
    ps.executeUpdate();
    try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next()) {
            long rowId = rs.getLong(1); // 插入或命中的已有行 id
            System.out.println("row id = " + rowId);
        }
    }
}

3.2 自增主键为什么会跳号

InnoDB 在 IODKU 执行前会先向自增计数器申请一个值，即使最终走的是 UPDATE 分支、这个值被丢弃，计数器也不会回退。结果是：频繁命中冲突的表，id 会出现大段不连续。这不是 bug，是设计使然（innodb_autoinc_lock_mode 的默认值 2 下更明显）。

如果业务对外暴露 id 并要求连续（例如订单号），不要拿自增主键当业务单号，用独立的序列服务或雪花算法生成。自增 id 只做物理主键，跳号完全可以接受。

另外要注意 innodb_autoinc_lock_mode 的取值影响：

• 0（traditional）：语句级表锁，最安全，并发最差。
• 1（consecutive）：批量插入时保证连续，单条 IODKU 仍可能跳号。
• 2（interleaved，8.0 默认）：并发最高，自增值可能交错且跳号，但 InnoDB 保证唯一。

四、并发与死锁：为什么加了唯一索引还报 Deadlock

IODKU 在冲突时需要给已存在的行加排他锁，同时在唯一索引上做插入意向锁与间隙锁的判定。当多个事务以不同顺序写同一批唯一键时，很容易形成环形等待。典型报错：

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

成因举例：事务 A 先写 (2025-03-01, 1001) 再写 (2025-03-01, 1002)，事务 B 顺序相反。两个事务各自持有第一行的锁，又都在等对方的第二行，InnoDB 检测到环后回滚其中一个。

处理办法：

1. 固定写入顺序。批量 IODKU 前，在应用层对唯一键排序，保证所有事务按同一顺序加锁。这是成本最低、效果最好的手段。
2. 控制批量大小。一条 IODKU 塞几百个 VALUES 会持有大量行锁、拉长事务，建议单批控制在 100~500 行，并按主键或唯一键有序排列。
3. 捕获并重试。死锁（1213）和锁等待超时（1205）都属于可重试错误，应用层加有限次退避重试即可。注意：重试必须保证 UPDATE 分支是幂等的（如 + new_row.x 这类增量在重试时会重复累加，需要改成覆盖式赋值或用唯一流水号表去重）。
4. 缩短事务。IODKU 前后不要夹带 RPC 调用、大事务里的其他 DML，尽快提交。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个报错说明等待超过了 innodb_lock_wait_timeout（默认 50 秒）。先用 SHOW ENGINE INNODB STATUS\G 看 LATEST DETECTED DEADLOCK 段落，或者 SELECT * FROM performance_schema.data_locks; / data_lock_waits; 定位是谁在持锁。

五、生产环境的几个细节

5.1 只更新需要更新的列

UPDATE 子句里列得越多，binlog 越大、触发的二级索引维护越多。只写真正会变的列，避免把 created_at 之类不该动的列带进去。

5.2 避免在 UPDATE 分支里做子查询

虽然语法上允许，但 UPDATE 分支的子查询在并发下会引入额外的共享锁，且无法保证读到的是最新提交的数据。统计值一律用 col = col + 增量 的方式推给数据库做。

5.3 与 REPLACE INTO、INSERT IGNORE 的取舍

语句	行为	适用场景
INSERT ... ON DUPLICATE KEY UPDATE	冲突则更新指定列，自增 id 保守	需要保留原行、做增量或部分字段更新
REPLACE INTO	冲突则先 DELETE 再 INSERT	整行覆盖、且行上没有外键/触发器依赖
INSERT IGNORE	冲突直接丢弃，不更新	只关心「首次写入」，后续重复一律忽略

REPLACE INTO 会删掉旧行，导致自增 id 变化、外键级联删除、以及 UPDATE 触发器不触发等一系列副作用，线上慎用。INSERT IGNORE 还会把数据截断、类型转换等错误一起吞掉，容易掩盖真实问题。

5.4 确认唯一索引真的生效

上线前用 EXPLAIN 或直接手工插两条相同唯一键验证：

SHOW INDEX FROM order_daily_stat WHERE Key_name = 'uk_date_shop';
-- 或者查看唯一键是否存在
SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME = 'order_daily_stat'
  AND NON_UNIQUE = 0
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

小结

IODKU 能把「判断 + 写入」压成一条原子语句，前提是唯一索引设计正确、唯一键列 NOT NULL、UPDATE 分支用的是当前列值而不是插入值。并发写同一批唯一键时用固定顺序 + 有限重试压住死锁，自增 id 跳号属于正常现象不必处理。MySQL 8.0.19+ 优先使用行别名而非已废弃的 VALUES() 函数。

FAQ

Q1：为什么我的 IODKU 一直在插入新行，没有走更新？

九成是唯一索引没建、建在了别的列上，或者唯一键里有 NULL。用第五节的 information_schema.STATISTICS 查询确认 NON_UNIQUE = 0 的索引列组合是否与 INSERT 的列一致。另一种可能是列排序规则做了大小写不敏感合并，导致你以为不同的两个值被判成同一个（或者相反）。

Q2：UPDATE 分支返回 0 行，是失败了吗？

不是。当新值与已有值完全相同时，MySQL 返回受影响行数为 0，这表示「没有实际变更」。判断成功与否应看语句是否抛异常，而不是看 affected rows。

Q3：用 IODKU 做计数累加，重试会不会重复计数？

会。order_cnt = order_cnt + 1 在事务回滚重试时会再执行一次，如果第一次已经提交而客户端没收到响应就重试，就多算了一次。对计数类场景，正确做法是引入一张以业务流水号为唯一键的去重表：先 INSERT ... ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id) 拿到「本次是否首次」，只有首次才去累加统计表，并把两步放进同一个事务。

Q4：MySQL 8.0 报 VALUES function is deprecated 怎么办？

这是告警不是错误，8.0.20 起出现，功能仍可用。升级写法：在 VALUES (...) 后加行别名 AS new_row（别名不能与表名重名），UPDATE 分支用 new_row.col 引用插入值。注意行别名语法要求 MySQL 8.0.19+，低版本不支持。

Q5：一张表有多个唯一索引，IODKU 会命中哪个？

只要命中了任意一个唯一索引（或主键），就会进入 UPDATE 分支，且 MySQL 不保证告诉你命中的是哪一个。多唯一索引的表要特别小心：可能因为第二个唯一键冲突而更新了本不该更新的行。设计上尽量让一张表只有一个业务唯一键。