MySQL 自增欄位插入 0 值解決方案：NO_AUTO_VALUE_ON_ZERO 模式完整指南

背景

MSSQL → MySQL 遷移，業務邏輯要求自增欄位 (PK) **明確插入 0** 代表系統內建記錄 (如 `Id=0` 為 `[SYSTEM]` 用戶)。

MSSQL 做法

SET IDENTITY_INSERT dbo.AppUsers ON;
INSERT dbo.AppUsers (Id, ...) VALUES (0, ...);
SET IDENTITY_INSERT dbo.AppUsers OFF;
DBCC CHECKIDENT ('dbo.AppUsers', RESEED, 0);

MySQL 核心機制

預設：`INSERT INTO t(id) VALUES (0)` 或 `NULL` → **自動產生下一個序列值**。

需求：`0` **不觸發自增**，僅 `NULL` 觸發。

解決：SQL Mode `NO_AUTO_VALUE_ON_ZERO`

Session 級 (單次連線)

SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
INSERT INTO AppUsers (Id, ...) VALUES (0, ...);  -- 寫入 0
INSERT INTO AppUsers (Id, ...) VALUES (NULL, ...); -- 自增產生 1

Global 級 (持久化，my.cnf / my.ini)

[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"

修改後**重啟 MySQL**。

驗證

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

mysqldump 自動支援

匯出時自動包含 `SET @SESSION.sql_mode='NO_AUTO_VALUE_ON_ZERO'`，確保還原時 0 值不被重新編碼。

跨平台同步 (Windows/Linux)

兩邊 `my.ini`/`my.cnf` 同步加入 `NO_AUTO_VALUE_ON_ZERO`，確保複寫/備份還原一致性。

官方文檔原文重點

`NO_AUTO_VALUE_ON_ZERO` suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. ... mysqldump now automatically includes ... NO_AUTO_VALUE_ON_ZERO to avoid this problem.

注意事項

• **不建議**常態插入 0，僅限特定業務語意 (系統保留記錄)
• 應用層插入普通記錄時**務必傳 NULL 或省略欄位**，不可傳 0
• 複寫場景主從 sql_mode 必須一致

結語

設定 `sql_mode=NO_AUTO_VALUE_ON_ZERO` 即可讓 MySQL 自增欄位接受 0 值而不觸發自增，**Session 級臨時用、Global 級持久化**，配合 mysqldump 自動處理，完美解決遷移與業務雙重需求。