MySQL AUTO_INCREMENT 深度解析：六大引擎差異、重用規則與進階用法

基本語法

CREATE TABLE t (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(15) NOT NULL
) AUTO_INCREMENT = 100;  -- 指定起始值

六大儲存引擎行為差異表

複合索引多序列實戰 (MyISAM/BDB)

CREATE TABLE animals (
  grp ENUM('fish','mammal','bird') NOT NULL,
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  PRIMARY KEY (grp, id)   -- grp 為第一鍵，id 為第二鍵
) ENGINE=MyISAM;

INSERT INTO animals (grp, name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','shark'),
('bird','ostrich'),('mammal','whale');

SELECT * FROM animals ORDER BY grp, id;
-- 結果：
-- bird   1 penguin
-- bird   2 ostrich
-- fish   1 shark
-- mammal 1 dog
-- mammal 2 cat
-- mammal 3 whale
-- 每個 grp 內部 id 獨立遞增

關鍵限制與最佳實踐

1. 僅限整數類型（TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT）。
2. 建議 UNSIGNED：正數序列容量翻倍（TINYINT 255 vs 127）。
3. 必須有唯一索引（通常為 PRIMARY KEY 或 UNIQUE KEY），防重複。
4. 必須 NOT NULL。
5. 上限受數據類型限制：到達上限後 AUTO_INCREMENT 失效，需變更類型或重設計。
6. TRUNCATE TABLE / DELETE FROM tbl (無 WHERE) 會重置計數器從 1 開始。
   • 若需保留最大值：DELETE FROM tbl WHERE 1（逐行評估，較慢但保留序列）。
7. 不重用序列的強制方案：建立專用序列表 seq(id INT UNSIGNED AUTO_INCREMENT)，插入 NULL 取得 ID，再 LAST_INSERT_ID() 寫入主表。
8. ALTER TABLE t DROP id, ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY 可重新排列連續編號。

不使用 AUTO_INCREMENT 生成序列：LAST_INSERT_ID(expr)

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

-- 取得下一序列 (步長 1)
UPDATE seq SET id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();  -- 返回 1, 2, 3...

-- 步長 10
UPDATE seq SET id = LAST_INSERT_ID(id + 10);
SELECT LAST_INSERT_ID();  -- 返回 10, 20, 30...

優點：無需鎖表/事務，並發安全，適合計數器、票號生成器。

總結

MyISAM/InnoDB 為主流選擇，MyISAM 重用、InnoDB 不重用 是最大行為差異。複合索引多序列僅 MyISAM/BDB 支援。設計時務必評估「是否允許重用」、「最大值上限」、「並發缺口容忍度」，並配合 LAST_INSERT_ID() 正確獲取新 ID。