MySQL AUTO_INCREMENT 列更新陷阱：MyISAM 與 InnoDB 行為差異深度解析

前言

在 MySQL 資料庫開發中，AUTO_INCREMENT 自增列常被用作主鍵。雖然插入操作行為一致，但若直接 UPDATE 自增列數值，MyISAM 與 InnoDB 兩大儲存引擎的表現卻大相徑庭。若不了解其內部機制，極易在 InnoDB 中埋下「主鍵衝突」的隱形地雷。本文透過實測案例，深入剖析兩者差異，並提供最佳實踐建議。

核心結論速覽

實測環境

• OS: RHEL 6.4 x86_64
• MySQL: 5.6.19

案例一：MyISAM 表 —— 「聰明」的計數器維護

初始數據準備

CREATE TABLE `t5` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

INSERT INTO t5 VALUES (NULL), (5), (9);
-- 當前數據: 1, 5, 9
-- SHOW CREATE TABLE 顯示 AUTO_INCREMENT=10

場景 1：由大改小 (9 → 4)

UPDATE t5 SET id=4 WHERE id=9;
-- 成功，無警告
-- SHOW CREATE TABLE: AUTO_INCREMENT 仍為 10 (合理，因最大值仍是 5)

MyISAM 僅檢查唯一性約束，不主動調整計數器，這符合預期。

場景 2：由小改大 (5 → 12)，且大於當前 AUTO_INCREMENT (10)

UPDATE t5 SET id=12 WHERE id=5;
-- 成功
-- SHOW CREATE TABLE: AUTO_INCREMENT 自動變為 13 (新最大值 12 + 1)

關鍵點：MyISAM 偵測到新值超過當前計數器，會自動同步更新 AUTO_INCREMENT 值。後續 INSERT NULL 將從 13 開始，完全安全。

案例二：InnoDB 表 —— 「固執」的計數器與隱形坑

初始數據準備 (結構同 t5，引擎換為 InnoDB)

CREATE TABLE `t6` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO t6 VALUES (NULL), (5), (9);
-- 當前數據: 1, 5, 9
-- SHOW CREATE TABLE 顯示 AUTO_INCREMENT=10

場景 1：由大改小 (9 → 4)

UPDATE t6 SET id=4 WHERE id=9;
-- 成功
-- SHOW CREATE TABLE: AUTO_INCREMENT 維持 10

行為正常，無副作用。

場景 2：由小改大 (5 → 12)，大於當前 AUTO_INCREMENT (10) —— 核心陷阱

UPDATE t6 SET id=12 WHERE id=5;
-- 成功，無任何警告！
-- 數據變為: 1, 4, 12
-- SHOW CREATE TABLE: AUTO_INCREMENT 依然是 10 (這裡埋下禍根)

災難重現：後續插入衝突

-- 正常插入兩筆，ID 分配 10, 11
INSERT INTO t6 VALUES (NULL), (NULL); 
-- 成功，數據: 1, 4, 10, 11, 12

-- 再插入一筆，預期 ID 12，但 12 已存在！
INSERT INTO t6 VALUES (NULL);
-- ERROR 1062 (23000): Duplicate entry '12' for key 'PRIMARY'

深度原理剖析：為何 InnoDB 不更新計數器？

1. MyISAM：表級鎖架構下，AUTO_INCREMENT 計數器存儲於表元數據文件 (.MYD/.MYI) 中，UPDATE 操作易於觸發元數據同步更新。
2. InnoDB (≤ 5.6/5.7 某版本前)：AUTO_INCREMENT 計數器僅存在於內存中 (由 `dict_table_t::auto_inc` 維護)，且不持久化到重做日誌。為了性能與崩潰恢復一致性，InnoDB 設計為僅在 INSERT 時才根據最大值推算/更新計數器。UPDATE 操作不觸發計數器邏輯，導致內存計數器與實際數據最大值脫節。
3. InnoDB 8.0+ 改進：引入了持久化的 AUTO_INCREMENT 值存儲於系統表 `mysql.innodb_table_stats`，重啟後可恢復，但 UPDATE 仍不會即時修改該持久化值，核心邏輯未變。

最佳實踐與規避指南

若歷史數據修正不可避免，請採用以下安全方案：

方案 A：重建表法 (推薦，最徹底)

-- 1. 建新表結構相同
CREATE TABLE t6_new LIKE t6;
-- 2. 複製數據 (可在 INSERT 中指定新 ID)
INSERT INTO t6_new SELECT * FROM t6; 
-- 3. 原子切換
RENAME TABLE t6 TO t6_old, t6_new TO t6;
-- 4. 確認無誤後刪舊表

方案 B：手動同步計數器 (僅適用於維護窗口)

-- 更新數據
UPDATE t6 SET id=12 WHERE id=5;
-- 緊接著手動修正計數器 (需 SUPER 權限)
ALTER TABLE t6 AUTO_INCREMENT = 13; -- 設為 新最大值 + 1

注意：`ALTER TABLE ... AUTO_INCREMENT=N` 在 InnoDB 中僅修改內存計數器 (8.0+ 會持久化)，若 N 小於等於當前最大值則無效。

方案 C：業務邏輯層面規避

• 使用 BIGINT 而非 INT，耗盡風險極低，無需回填空洞。
• 若需「連續號碼」 (如發票號)，請單獨建表維護序列，勿依賴主鍵自增。
• 軟刪除 + 邏輯主鍵：保留自增主鍵不變，業務唯一性用 UK 索引保證。

總結

MyISAM 對 UPDATE AUTO_INCREMENT 的「智能同步」看似友好，實則掩蓋了設計缺陷；InnoDB 的「固執」反而暴露了關係模型核心原則——主鍵一旦生成，不應被修改。理解這一差異，不僅能避免生產事故，更能倒逼我們遵循「主鍵不可變」的資料庫設計鐵律。

參考來源：Bosco 的技術博客原文實測