MySQL SQL Mode 詳解：ANSI、STRICT、TRADITIONAL、NO_ENGINE_SUBSTITUTION 等模式對行為的影響

MySQL SQL Mode 詳解：ANSI、STRICT、TRADITIONAL、NO_ENGINE_SUBSTITUTION 等模式對行為的影響

SQL Mode 定義 MySQL 支援的 SQL 標準兼容度、數據驗證嚴格程度、錯誤處理行為。可在啟動參數、配置檔、會話級動態調整。本文系統梳理核心模式、嚴格模式邏輯、常見組合與生產環境推薦配置。

一、設定方式

-- 啟動參數
mysqld --sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

-- 配置檔 my.cnf
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"

-- 會話級動態修改
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ANSI_QUOTES';
SET GLOBAL sql_mode = '...';  -- 需 SUPER 權限，影響後續新連線

-- 查看當前
SELECT @@sql_mode;  -- 或 SHOW VARIABLES LIKE 'sql_mode';

二、核心模式分類速查

三、嚴格模式深度邏輯

3.1 事務表

• 啟用 STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES → 非法/缺失值 → **報錯、整個語句回滾**。

3.2 非事務表

• STRICT_TRANS_TABLES：首行非法 → 報錯停止；後續行非法 → **截斷/調整為最接近合法值 + 警告**，語句繼續執行。
• STRICT_ALL_TABLES：任何行非法 → **報錯停止**，前面已寫入的行**不回滾**（非事務表無回滾能力），造成部分寫入風險。

3.3 日期零值處理

• 嚴格模式 + NO_ZERO_DATE + NO_ZERO_IN_DATE → 完全拒絕零日期/零月日。
• 需插入零值時用 INSERT IGNORE 或 UPDATE IGNORE 降級為警告。

四、生產環境推薦組合

# MySQL 5.7/8.0 現代嚴格兼容模式（官方預設）
STRICT_TRANS_TABLES,
NO_ENGINE_SUBSTITUTION,
NO_ZERO_DATE,
NO_ZERO_IN_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
ONLY_FULL_GROUP_BY

# 等同於 5.7+ 預設：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• **不建議** 直接用 TRADITIONAL：含 STRICT_ALL_TABLES，非事務表部分寫入風險大。
• 遷移舊系統時，可暫時關閉 ONLY_FULL_GROUP_BY、NO_ZERO_DATE 過渡，逐步修正應用 SQL。

五、常見坑點與對策

六、版本演進提示

• 5.6：預設 NO_ENGINE_SUBSTITUTION。
• 5.7：預設加入 STRICT_TRANS_TABLES、NO_ZERO_DATE、NO_ZERO_IN_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER、ONLY_FULL_GROUP_BY。
• 8.0：移除 NO_AUTO_CREATE_USER（內建強制）、PAD_CHAR_TO_FULL_LENGTH；NO_AUTO_VALUE_ON_ZERO 廢棄。

精準掌控 SQL Mode，讓 MySQL 在數據完整性、標準兼容、遷移平滑三者間取得最佳平衡。