MySQL AUTO_INCREMENT 完全指南：各引擎差異、進階用法與避坑清單

前言：自增主鍵看似簡單，細節決定成敗

AUTO_INCREMENT 是 MySQL 最常用卻最易被忽視的特性。不同儲存引擎（MyISAM、InnoDB、MEMORY、BDB、Archive）行為差異巨大，配合 LAST_INSERT_ID()、複合索引、重置策略等進階用法，能解決分散式 ID、熱點爭用、審計追蹤等棘手問題。本文系統梳理全引擎行為差異、核心限制、進階技巧與生產級避坑指南。

基礎建表語法與關鍵屬性

CREATE TABLE `t_auto` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100;  -- 指定起始值

黃金法則：UNSIGNED（翻倍容量）+ NOT NULL + PRIMARY KEY / UNIQUE KEY。

五大引擎行為差異對照表

複合索引實現多獨立序列（MyISAM/BDB 專屬）

CREATE TABLE `multi_seq` (
  `group_id` INT NOT NULL,
  `seq` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `data` VARCHAR(100),
  PRIMARY KEY (`group_id`, `seq`)  -- 複合主鍵，seq 為最後一列
) ENGINE=MyISAM;

INSERT INTO `multi_seq` (`group_id`, `data`) VALUES (1, 'A'), (1, 'B'), (2, 'X'), (2, 'Y');
-- group_id=1 序列：1,2  |  group_id=2 序列：1,2  互不干擾

InnoDB 不支援此特性，需改用分表或應用層號段分配。

核心函式：LAST_INSERT_ID() 精準獲取自增值

• 連線級隔離：Session A 插入不影響 Session B 的返回值
• 語句級穩定：多行 INSERT ... VALUES (),(),() 返回第一行自增值
• 可傳參數：LAST_INSERT_ID(expr) 設定下一次返回值，實現自定義步長序列

避坑清單：生產環境必讀

1. 類型上限截斷：TINYINT 上限 127/255，INT 上限 21 億/42 億。預估 3 年增長量選型，預留 10 倍餘量
2. TRUNCATE 重置陷阱：TRUNCATE TABLE / DELETE FROM table（無 WHERE）會重置自增計數器。保留序列請用 DELETE FROM table WHERE 1 強制逐行刪除
3. 主從複寫自增衝突：雙主/環狀複寫必須設定 auto_increment_increment + auto_increment_offset 錯開序列
4. 隱式轉換效能損：應用層用 Long/BigInt 接收，避免 INT 溢位或隱式轉換
5. 備份還原序列斷層：mysqldump 包含 AUTO_INCREMENT=xxx，還原後新插入從 xxx+1 開始，若需銜接舊序列需手動 ALTER TABLE ... AUTO_INCREMENT=新值

進階模式：專用序列表 + LAST_INSERT_ID() 實現無鎖全域唯一 ID

CREATE TABLE `seq_global` (`id` BIGINT UNSIGNED NOT NULL) ENGINE=InnoDB;
INSERT INTO `seq_global` VALUES (0);

-- 取號
UPDATE `seq_global` SET `id` = LAST_INSERT_ID(`id` + 1);
SELECT LAST_INSERT_ID();  -- 返回新 ID

-- 步長 100 號段分配（分散式服務適用）
UPDATE `seq_global` SET `id` = LAST_INSERT_ID(`id` + 100);
SELECT LAST_INSERT_ID();  -- 返回號段起始值，應用層本地 +1 分配

優點：無表鎖、無事務、高併發、彈性步長。缺點：單點寫入（可改用多序列表分片）。

重新排列現有序列（填補空洞）

ALTER TABLE `t` DROP `id`;
ALTER TABLE `t` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

警告：外鍵關聯表會報錯，需先刪除外鍵。生產環境極不建議，破壞審計追蹤與資料關聯完整性。

結語

AUTO_INCREMENT 不只是「自動加 1」。深度理解引擎差異、掌握 LAST_INSERT_ID() 進階用法、建立避坑檢查清單，才能在高併發、分散式、長期演進的系統中穩健落地。選對引擎、定好類型、備好號段策略——這才是專業工程師的態度。