您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL AUTO_INCREMENT 自增序列完整指南：六大引擎差異、重用規則與進階生成技巧

編輯：MySQL 資料庫相關知識

自增序列是資料庫主鍵生成的標準機制。MySQL各儲存引擎對AUTO_INCREMENT行為有顯著差異，本文系統梳理六大引擎特性、邊界條件與進階應用模式。

ISAM（基準引擎，其它引擎以此為基礎）

插入NULL或0 → 自動生成下一序號（從1開始，步長1）。
插入指定值：
— 衝突 → 錯誤
— 大於現有最大值 → 接受並**重設序號起點**為該值+1
刪除最大值 → **重用**該序號。
UPDATE自增欄：同插入指定值邏輯。
REPLACE：
— 依自增欄匹配 → 序號不變
— 依其他PK/UNIQUE匹配 → 序號**重新生成**（若設NULL）
LAST_INSERT_ID()：取得本連線最後生成序號，未生成則回傳0。

引擎差異總覽表

特性	MyISAM	HEAP/MEMORY	BDB	InnoDB
刪除最大值後重用	不重用	不重用	重用	不重用
`AUTO_INCREMENT=n` 建表指定初值	支援	支援 (4.1+)	不支援	不支援
`ALTER TABLE ... AUTO_INCREMENT=n` 重設初值	支援	支援	不支援	不支援
複合索引建立多序列	支援	不支援	支援	不支援

MyISAM 複合索引多序列實戰

CREATE TABLE invoices (
    branch_id INT NOT NULL,
    inv_id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (branch_id, inv_id)
) ENGINE=MyISAM;

每個branch_id擁有獨立遞增序列：分店1 → 1,2,3...；分店2 → 1,2,3...

通用使用守則

僅適用**整數型別**欄位。
建議加UNSIGNED：正數序列容量翻倍（TINYINT UNSIGNED上限255 vs 127）。
**必須**有唯一索引（通常為PK）。
**必須**NOT NULL。
上限受資料型別限制，達上限後自增失效。

全表刪除與序號保留

DELETE FROM t / TRUNCATE TABLE t → MySQL優化為**重建表**，序號歸零。
保留序號：**DELETE FROM t WHERE 1** 強制逐行評估條件，避免優化重建。

進階模式：專用序號表 + LAST_INSERT_ID()

CREATE TABLE seq (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO seq VALUES (NULL);           -- 生成序號
INSERT INTO main (id, ...) VALUES (LAST_INSERT_ID(), ...);

優點：主表永不刪除序號表記錄，絕對不重用；無需鎖表/事務，併發效能極高。

無 AUTO_INCREMENT 生成序列：帶參數 LAST_INSERT_ID(expr)

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

UPDATE seq_table SET seq = LAST_INSERT_ID(seq + 1);
SELECT LAST_INSERT_ID();  -- 回傳 seq+1
-- 步長控制：seq + 10 → 步長10

適用於計數器、多行獨立序列，無鎖無事務，不阻塞其他操作。