MySQL AUTO_INCREMENT 深度解析：各儲存引擎行為差異、邊界條件與最佳實務

基本語法與建表示例

CREATE TABLE `test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(15) NOT NULL
) AUTO_INCREMENT = 100;

AUTO_INCREMENT 僅適用於整數類型，且必須具備 NOT NULL 與唯一索引（通常為 PRIMARY KEY）。建議加 UNSIGNED 將正數範圍翻倍。

各儲存引擎行為差異表

核心行為細節

• 插入 NULL/0：觸發自動生成下一序號
• 插入指定值：
  • 值衝突 → 錯誤（唯一鍵衝突）
  • 值 > 當前最大值 → 接受並將下一序號設為該值+1（可跳號）
• UPDATE 修改自增列：同插入指定值邏輯
• REPLACE 語句：
  • 以自增列為條件 → 值不變
  • 以其他唯一鍵為條件 → 自增列若為 NULL 則重新生成
• LAST_INSERT_ID()：回傳當前連線最近一次自動生成的值，跨連線不干擾
• TRUNCATE TABLE：重置自增計數器歸 1
• DELETE FROM table（無 WHERE）：同 TRUNCATE，重置計數器
• DELETE FROM table WHERE 1：逐列刪除，保留計數器

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

CREATE TABLE `orders` (
    `shop_id` INT NOT NULL,
    `order_seq` INT NOT NULL AUTO_INCREMENT,
    `amount` DECIMAL(10,2),
    PRIMARY KEY (`shop_id`, `order_seq`)  -- shop_id 為前綴，order_seq 為自增尾部
) ENGINE=MyISAM;

每個 shop_id 獨立擁有 1,2,3... 序列，互不干擾。

進階技巧：外部序列表法（繞過引擎限制）

-- 專用序列表（永不刪除記錄）
CREATE TABLE `seq_gen` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

-- 取號並寫入主表
INSERT INTO `seq_gen` VALUES (NULL);
INSERT INTO `main_table` (`main_id`, `data`) VALUES (LAST_INSERT_ID(), 'xxx');

優點：不需鎖表/事務，高併發下無阻塞，序列永不重用。

替代方案：使用 LAST_INSERT_ID(expr) 自定義步長

CREATE TABLE `counter` (`name` VARCHAR(20) PRIMARY KEY, `val` INT UNSIGNED NOT NULL);
INSERT INTO `counter` VALUES ('order_no', 0);

-- 步長 10 取號
UPDATE `counter` SET `val` = LAST_INSERT_ID(`val` + 10) WHERE `name` = 'order_no';
SELECT LAST_INSERT_ID();  -- 返回新值

適用於計數器、分庫分表全域唯一 ID 生成。

常見陷阱避坑指南

1. 類型上限：TINYINT 上限 127/255，INT 上限 21億/42億，超限報錯
2. 主從複寫：auto_increment_offset + auto_increment_increment 避免雙主衝突
3. 批次匯入：SET @id := 0; UPDATE t SET id = @id := @id + 1; 重新排序連續號

精通 AUTO_INCREMENT 在各引擎的細微差異，能避免主鍵衝突、序列重用、複寫衝突等生產事故。