MySQL sql_mode 完整指南：嚴格模式、兼容模式與最佳配置實踐

sql_mode 是控制 MySQL 數據校驗、語法解析、錯誤處理行為的核心系統變量。正確配置能防止「髒數據」入庫、提升應用穩定性；錯誤配置則可能導致數據截斷、靜默失敗等難以排查的問題。本文系統梳理所有模式值、組合模式、版本差異與生產環境建議配置。

核心概念：嚴格模式 vs 非嚴格模式

常用單項模式詳解

數據完整性相關

• STRICT_TRANS_TABLES：事務表啟用嚴格模式，非事務表盡力嚴格（推薦基礎項）
• STRICT_ALL_TABLES：所有表嚴格模式
• NO_ZERO_DATE：禁止 '0000-00-00' 作為合法日期
• NO_ZERO_IN_DATE：禁止月/日為 0 的日期（如 '2023-00-15'）
• ERROR_FOR_DIVISION_BY_ZERO：除零報錯而非返回 NULL
• NO_AUTO_CREATE_USER：GRANT 不自動創建用戶（5.7+ 默認開啟）

語法兼容性相關

• ANSI_QUOTES：雙引號作為標識符引號，不可用於字符串
• PIPES_AS_CONCAT：|| 作為字符串連接而非 OR
• IGNORE_SPACE：函數名與括號間允許空格
• HIGH_NOT_PRECEDENCE：NOT 優先級恢復舊版本行為

數據類型相關

• REAL_AS_FLOAT：REAL 視為 FLOAT 而非 DOUBLE
• NO_AUTO_VALUE_ON_ZERO：AUTO_INCREMENT 列插入 0 不生成序列號
• NO_BACKSLASH_ESCAPES：禁用反斜線轉義

遷移/導出相關

• NO_FIELD_OPTIONS、NO_KEY_OPTIONS、NO_TABLE_OPTIONS：SHOW CREATE TABLE 不輸出 MySQL 專屬選項，利於跨庫遷移

組合模式（速查表）

版本演進關鍵點

• MySQL 5.6：默認 NO_ENGINE_SUBSTITUTION
• MySQL 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
• MySQL 8.0：默認同 5.7，NO_AUTO_CREATE_USER 移除（已內建）

生產環境推薦配置

[mysqld]
# 核心嚴格模式
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 日期嚴格控制（視業務兼容性決定是否開啟）
# sql_mode = ...,NO_ZERO_DATE,NO_ZERO_IN_DATE

# 群組查詢規範（5.7+ 默認開啟，舊應用可能需關閉）
# ONLY_FULL_GROUP_BY

常見坑與排查

1. 「Data truncated」Warning 變 Error：開啟嚴格模式後，字符串超長、數值越界會直接報錯
2. GROUP BY 語義變化：ONLY_FULL_GROUP_BY 要求 SELECT 列必須出現在 GROUP BY 或聚合函數中
3. 日期零值插入失敗：舊代碼插入 '0000-00-00' 需改用 NULL 或有效日期
4. 主從複製不一致：主從 sql_mode 必須一致，否則同步報錯

動態調整與驗證

-- 會話級臨時調整（測試用）
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE';

-- 全局永久調整（需配置文件持久化）
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

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

最佳實踐：新項目從 5.7/8.0 默認嚴格模式開始，避免歷史包袱；老項目升級前務必全量掃描 SQL、測試數據遷移，分階段開啟嚴格項。

1. 上一篇文章： MySQL 預處理語句性能實測：是否真能提升查詢效率？
2. 下一篇文章： MySQL 分表唯一 ID 生成方案：基於 LAST_INSERT_ID 的集中式 ID 服務

MySQL 資料庫相關知識

MySQL 常見錯誤排查手冊：#1067 Invalid default value 與 UTF-8 中文亂碼實戰解決

錯誤一：#1067 - Invalid default va

Linux MySQL 簡易教程（一）：安裝、啟動、建庫建表、切換庫

安裝與啟動 sudo apt-get install -y

MySQL SQL 語言特性分類與擴展：從 DDL 到缺失功能的演進視角

MySQL SQL 語言特性全景：分類、擴展、缺失與演進路線

MySQL-MMM 高可用架構部署全攻略：多主同步、虛擬 IP、自動故障轉移

架構總覽 MMM (Multi-Master Replica