MySQL AUTO_INCREMENT 自增鍵深度解析：各引擎差異、重用規則、修改起始值與序列生成替代方案

建表示例

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

各存儲引擎自增特性對比

InnoDB 關鍵注意事項（主流引擎）

• 僅支持整數類型，建議 UNSIGNED 擴大範圍
• 必須有唯一索引（通常為 PK），防止重複
• 必須 NOT NULL
• 最大值受類型限制：TINYINT 127/255，INT 21 億/42 億，BIGINT 9×10¹⁸
• 不可通過 CREATE/ALTER TABLE 修改起始值（5.6 前），5.6+ 支持 ALTER TABLE t AUTO_INCREMENT=n（n ≥ 當前最大值+1）
• 不支持復合索引多序列

常見操作陷阱

全表刪除導致自增歸 1

DELETE FROM t;        -- 觸發優化：實際 DROP+重建表
TRUNCATE TABLE t;     -- 同效果

保留序列：DELETE FROM t WHERE 1; 強制逐行判斷，不觸發優化。

強制不重用已用值：獨立序列表模式

CREATE TABLE seq (id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);
INSERT INTO seq VALUES (NULL);  -- 生成新 ID
INSERT INTO main SET main_id = LAST_INSERT_ID();  -- 業務表使用

修改自增起始值

ALTER TABLE tablename AUTO_INCREMENT = num;

num 必須 ≥ 當前最大值+1，否則語法成功但無效果。

不依賴 AUTO_INCREMENT 的序列生成替代方案

CREATE TABLE seq_table (id INT UNSIGNED NOT NULL);
INSERT INTO seq_table VALUES (0);

-- 獲取下一序列（步長 1）
UPDATE seq_table SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

-- 步長 10
UPDATE seq_table SET id = LAST_INSERT_ID(id + 10);
SELECT LAST_INSERT_ID();

優點：無需鎖表/事務，高並發下生成唯一序列，可構建多計數器（多行記錄不同計數）。

重新排列現有序列

最簡單：ALTER TABLE t DROP id; ALTER TABLE t ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; MySQL 自動生成連續編號。