MySQL AUTO_INCREMENT 不連續根因：唯一鍵衝突、事務回滾、批量插入預分配

MySQL AUTO_INCREMENT 不連續根因：唯一鍵衝突、事務回滾、批量插入預分配

自增 ID 不連續是正常行為，非 Bug。本文通過實測複現三大典型場景：唯一鍵衝突報錯、事務回滾、批量插入預分配，解析 InnoDB 自增鎖與序列分配機制。

實測複現過程

CREATE TABLE person_list (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(15) NOT NULL,
    sex VARCHAR(15) DEFAULT '中',
    ID_C VARCHAR(20) NOT NULL UNIQUE,  -- 唯一鍵
    vocation VARCHAR(8),
    salary FLOAT DEFAULT 0.0
) CHARSET=utf8;

-- 正常插入 1~7
INSERT ...;  -- id 1~7 連續

-- 插入 id=8 因 ID_C 衝突報錯 (Duplicate entry)
-- 再次插入 id=9 成功 → id 跳至 10

-- 故意連續 10 次衝突插入
-- 再插入成功 → id 跳至 25 (10+15)

三大根因深度解析

InnoDB 自增鎖模式 (innodb_autoinc_lock_mode)

生產預設模式 1，**不保證連續、不保證單調遞增（極罕見並發交錯）**，僅保證**唯一、遞增趨勢**。

常見誤區與對策

• ❌ 以為自增 ID 必須連續 → **業務邏輯不可依賴連續性**。
• ❌ 用 MAX(id)+1 手動分配 → 並發極易衝突、效能差。
• ✅ 業務需連續編號（如發票號）→ 獨立序列表 + 行鎖 SELECT ... FOR UPDATE 串行分配。
• ✅ 定期整理碎片 ID → 僅靜態歸檔表可考慮 ALTER TABLE AUTO_INCREMENT=N，線上表**嚴禁**。
• ✅ 監控自增上限：SHOW TABLE STATUS LIKE 't'\G 觀察 Auto_increment，防 BIGINT 耗盡。

驗證查詢

-- 查看下一個自增值
SHOW TABLE STATUS LIKE 'person_list'\G
-- Auto_increment: 25

-- 查看表結構
SHOW CREATE TABLE person_list\G

1. 上一篇文章： MySQL 忘記 Root 密碼重置全攻略：Skip-Grant-Tables、Init-File、Windows/Linux 通用
2. 下一篇文章： MySQL 5.5 升級 5.6 TIMESTAMP 行為變更兼容指南

MySQL 綜合教學

MySQL 5.7 JSON 數據類型與操作函數完整實戰指南

前言：為什麼選擇 MySQL JSON MySQL 5.7

MySQL varchar/nvarchar 存儲 Emoji 等特殊字符實戰：N 前綴與 Unicode 存儲機制

MySQL varchar/nvarchar 存儲 Emoj

無 my.ini 也能解決 MySQL 中文儲存亂碼：Workbench 圖形化設定法

問題背景 安裝 MySQL 後發現無 my.ini 設定檔，

Ubuntu 安裝 Navicat for MySQL 完整教學：下載、中文亂碼修正、試用期重置

Ubuntu 安裝 Navicat for MySQL 完整