最近开发的过程中，使用ai生成代码，写了一条这样的SQL：INSERT … ON DUPLICATE KEY UPDATE，然后发现一个奇怪的现象：

为什么使用这个语法后，自增主键（AUTO_INCREMENT）的值会跳跃甚至失效？

一、问题复现

假设我们有如下 users 表：

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,code VARCHAR(50) UNIQUE,name VARCHAR(50),age INT
);

现在我们执行如下语句：

INSERT INTO users (id, code, name, age)
VALUES(NULL, 'yiyiwu', '依依吾', 99),(8, 'kekeke', '可可克', 100)
ON DUPLICATE KEY UPDATEcode = VALUES(code),name = VALUES(name),age = VALUES(age);

运行后发现：

表里只有一条新的数据（id 是 10）

下一次插入时，主键跳到了 11，而不是 9

二、执行过程分析

来看一下每条数据的执行流程：

第一条：(NULL, ‘yiyiwu’, …)
id=NULL，触发自增主键

当前自增值为 10，则插入成功，id=10

自增值推进到 11

第二条：(8, ‘kekeke’, …)
手动指定了主键 id=8，但如果 id=8 已存在，会触发主键冲突

执行 ON DUPLICATE KEY UPDATE 逻辑，改为更新操作

不插入，但仍然“尝试”执行 insert

自增值 不会倒退

三、MySQL 自增机制揭秘

MySQL 的自增策略：

在执行 INSERT 时，无论是否冲突，都先分配一个自增 ID

即使最后走的是 UPDATE，这个 ID 也被“浪费掉”了

所以你会发现：

自增主键中间有“缺口”

连续插入过程中 ID 会跳跃

四、解决方案建议

1. 不使用 INSERT … ON DUPLICATE，改为“先查后插”：

SELECT id FROM users WHERE code = 'xxx';

-- 不存在再插入
INSERT INTO users (code, name, age) VALUES (...);

注意并发时需配合唯一索引或分布式锁。

2. 通过逻辑主键控制唯一性，而不是依赖自增主键

比如使用 code 作为业务唯一标识，而 id 仅作为内部排序标识。

看来ai写的代码有些时候并不可靠哇，还是要人工仔细审查的