MySQL AUTO_INCREMENT 自增 ID 起始值修改與重置完整指南

MySQL AUTO_INCREMENT 自增 ID 起始值修改與重置完整指南

許多開發者誤以為 AUTO_INCREMENT 起始值無法修改，實際上 MySQL 提供多種方式在建表時指定、建表後修改、甚至批量刪除資料後重置。本文整理所有場景的標準語法。

1. 建表時指定起始值

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_date DATETIME NOT NULL,
    cust_id INT NOT NULL,
    PRIMARY KEY (order_id)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4;
-- 起始 ID = 10000 (類似 QQ 號碼邏輯)

2. 建表後修改起始值 (ALTER TABLE)

ALTER TABLE users AUTO_INCREMENT = 123456;

3. 透過 TRUNCATE TABLE 重置 (會清空所有資料)

TRUNCATE TABLE users;
-- AUTO_INCREMENT 自動重置為 1 (或建表時指定值)

4. 透過 ALTER TABLE ... AUTO_INCREMENT = 1 重置 (保留資料)

若表內有資料，MySQL **不允許**將計數器設定為小於或等於現有最大 ID 的值。需先刪除大 ID 資料，或配合 INSERT ... SELECT 重建表。

5. 變更 AUTO_INCREMENT 步長與偏移 (主主複制/分庫分表)

-- 全域變數 (需重啟或 SET GLOBAL)
SET GLOBAL auto_increment_increment = 2;  -- 步長
SET GLOBAL auto_increment_offset = 1;     -- 起始偏移 (節點 1)
-- 節點 2 設定 offset = 2
-- 產生 ID 序列：節點1: 1,3,5... 節點2: 2,4,6... 避免衝突

6. 查看目前 AUTO_INCREMENT 值

SHOW TABLE STATUS LIKE 'users'\G
-- Auto_increment: 10000

-- 或查詢 information_schema
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA='db_name' AND TABLE_NAME='users';

常見坑點

• AUTO_INCREMENT 欄位**必須為索引** (通常是 PRIMARY KEY 或 UNIQUE KEY)。
• InnoDB 僅在記憶體中維護計數器，重啟後會以 MAX(id)+1 重新計算 (8.0+ 持久化至 redo log)。
• 手動指定 ID 插入 (如 INSERT INTO t(id) VALUES (100)) 不會推進計數器，下次自動生成仍為 MAX(id)+1。