最近開發的過程中，使用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寫的代碼有些時候并不可靠哇，還是要人工仔細審查的