場景

• 測試資料清理後，希望自增 ID 從 1 重新開始。
• 刪除大量資料導致自增值遠大於實際最大 ID，浪費 ID 空間（接近上限）。
• 主從同步/遷移後需對齊自增值。

方法一：ALTER TABLE 直接重設（最常用）

ALTER TABLE tbl_name AUTO_INCREMENT = N;

• N 必須 **大於當前表中最大 ID**，否則無效（MySQL 會忽略）。
• 範例：最大 ID = 150，自增值已跑到 250，需重設為 151：
  ALTER TABLE test AUTO_INCREMENT = 151;
• 對空表可直接設為 1：ALTER TABLE test AUTO_INCREMENT = 1;

方法二：建表時指定初始值

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

方法三：TRUNCATE TABLE（連帶重置自增）

TRUNCATE TABLE tbl_name;

• **清空所有資料** 並將自增計數器重置為 1（或初始值）。
• DDL 操作，**不可回滾**，不觸發 DELETE 觸發器。
• 外鍵引用時會失效（父表不可 TRUNCATE）。

進階：修改自增步長與偏移量（主從同步避免 ID 衝突）

查看當前值

SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

動態修改（當前會話/全域，重啟失效）

SET @@auto_increment_increment = 2;  -- 步長
SET @@auto_increment_offset = 1;     -- 起始偏移（Master1）
-- Master2 設 offset=2，即可產生奇/偶 ID 不衝突

永久生效（配置檔）

[mysqld]
auto_increment_increment = 2
auto_increment_offset = 1

修改後 systemctl restart mysqld。

自增值行為細節

1. 插入 NULL 或 0 → 自動產生下一個序列值。
2. 插入指定值 >= 現有最大值 → 接受該值，下一個自增值 = 插入值 + 1（可跳號）。
3. 插入指定值 < 現有最大值 → 若衝突報錯（Duplicate entry），不衝突則接受，下一個自增值不變。
4. UPDATE 修改自增欄位同理：衝突報錯，大於最大值則推進計數器。
5. REPLACE / INSERT ... ON DUPLICATE KEY UPDATE 可能導致自增值「燒號」（預分配未使用）。

InnoDB 持久化自增計數器 (MySQL 8.0+)

• 8.0 之前重啟會丟失記憶體中的自增計數器（取 MAX(id)+1），8.0 將計數器寫入 Redo Log，**重啟不再跳號**。
• 參數 innodb_autoinc_lock_mode（預設 2：interleaved）影響並發插入時的自增分配策略。

總結

重置自增值首選 ALTER TABLE ... AUTO_INCREMENT=N（N > 當前最大 ID）；清空表可用 TRUNCATE。主從雙主/多主架構**必須配置 auto_increment_increment/offset 避免 ID 衝突**。MySQL 8.0 已解決重啟跳號問題，升級即享紅利。