MySQL AUTO_INCREMENT 唯一編號機制深度解析

核心概念

AUTO_INCREMENT 是整數列屬性，自動生成唯一遞增序列，常用於主鍵。僅適用於整數類型，且必須包含唯一索引（通常為主鍵）。

各存儲引擎差異對比

MyISAM 復合索引多序列實戰

建表語句：

CREATE TABLE t (
  grp_id INT NOT NULL,
  seq INT NOT NULL AUTO_INCREMENT,
  data VARCHAR(20),
  PRIMARY KEY (grp_id, seq)
) ENGINE=MyISAM;

效果：每個 grp_id 獨立維護一個從 1 開始的 seq 序列。

關鍵使用注意事項

1. 聲明 UNSIGNED：正數序列可擴大一倍範圍（TINYINT UNSIGNED 最大 255）。
2. NOT NULL 必須：自增列隱含非空約束。
3. 唯一索引必要：防止序列重複。
4. 全表刪除重置序列：TRUNCATE TABLE 或無 WHERE 的 DELETE 會重置計數器。保留序列請用 DELETE FROM t WHERE 1（逐行刪除，較慢）。
5. 達到類型上限後失效：需提前規劃列類型（推薦 BIGINT UNSIGNED）。

進階技巧

1. 永不重用序列的「序列表」模式

CREATE TABLE seq_gen (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MyISAM;

-- 獲取新 ID
INSERT INTO seq_gen VALUES (NULL);
SELECT LAST_INSERT_ID();  -- 返回新 ID

-- 業務表使用
INSERT INTO main_tbl (id, ...) VALUES (LAST_INSERT_ID(), ...);

2. 重新排列現有序列

ALTER TABLE t DROP COLUMN id;
ALTER TABLE t ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

3. 不依賴 AUTO_INCREMENT 的計數器模式

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

-- 獲取下一值（步長可調）
UPDATE counter SET val = LAST_INSERT_ID(val + 10) WHERE name = 'order_no';
SELECT LAST_INSERT_ID();  -- 返回 10, 20, 30...

優點：無需表鎖/事務，高併發下性能極佳，可維護多個獨立計數器。

LAST_INSERT_ID() 關鍵行為

• 連接級別：僅返回當前會話生成的值，不受其他連接影響。
• 語句級別：多行 INSERT 僅返回第一行生成的 ID。
• LAST_INSERT_ID(expr) 可手動設置下一次返回值。