MySQL 無法創建自增字段：STRICT_TRANS_TABLES 導致的隱形阻礙

現象描述

執行建表語句報錯：

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);
ERROR 1067 (42000): Invalid default value for 'id'

或 ALTER TABLE t ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY 同樣失敗。

根因深度解析

MySQL 5.0.2+ 引入 STRICT_TRANS_TABLES，嚴格模式下：

1. AUTO_INCREMENT 列隱含 NOT NULL
2. 嚴格模式禁止 NOT NULL 列無顯式默認值 (除非有 AUTO_INCREMENT 屬性)
3. 但某些版本/配置下，解析器在檢查默認值時未正確識別 AUTO_INCREMENT 的特殊性，誤判為「無默認值」而拋錯

解決方案

方案一：移除 STRICT_TRANS_TABLES (快速生效，需重啟)

# /etc/my.cnf 或 /etc/mysql/my.cnf
[mysqld]
# 原配置
# sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# 修改為
sql_mode = "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# 或完全清空 (不推薦)
# sql_mode = ""

重啟驗證：SELECT @@sql_mode; 不含 STRICT_TRANS_TABLES 即可。

方案二：會話級臨時關閉 (無需重啟，適合腳本/遷移)

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
-- 執行建表/加列語句
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY);
-- 完成後恢復
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

方案三：顯式指定 DEFAULT (繞過解析器檢查)

-- 手動給自增列一個默認值 (雖然永遠不會用到)
CREATE TABLE t (
    id INT NOT NULL AUTO_INCREMENT DEFAULT 0 PRIMARY KEY
);

或 ALTER TABLE t MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT DEFAULT 0 PRIMARY KEY;

版本差異說明

MySQL 8.0 已優化解析器邏輯，AUTO_INCREMENT 在嚴格模式下正常工作。若在 5.7 遇到此問題，優先升級或用方案三繞過。

最佳實踐建議

1. 保持嚴格模式開啟：生產環境強烈建議保留 STRICT_TRANS_TABLES，保障數據質量。
2. 遷移腳本兼容：Flyway/Liquibase 遷移腳本開頭加 SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; 結尾恢復。
3. 統一建表規範：主鍵定義 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY，不依賴隱式默認值。
4. SHOW VARIABLES LIKE 'sql_mode'; 納入巡檢項目，確保集群一致性。

理解 SQL Mode 與 DDL 解析的交互細節，能讓你在版本升級、跨平台遷移中從容應對各類「詭異」報錯。