前言：SQL Mode 是 MySQL 的「行為憲法」
sql_mode 定義了 MySQL 支援的 SQL 語法規則、數據驗證嚴格程度、錯誤處理語義。它是**會話級/全局級動態變量**，可為不同應用定制行為模式。本文系統梳理所有模式值、核心模式行為差異、嚴格模式深度機制、遷移兼容策略與生產環境配置建議，助您在「寬容」與「嚴格」間找到業務平衡點。
一、配置與查看：雙通道動態控制
-- 啟動配置文件
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
-- 運行時動態修改（全局需 SUPER）
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'ANSI,STRICT_TRANS_TABLES';
-- 查看當前模式
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
-- 重置為空（無模式，最大兼容，最寬容）
SET SESSION sql_mode = '';
- 模式值以逗號分隔，順序無關。
- 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。
- 8.0 移除
NO_AUTO_CREATE_USER（已內建）。
二、核心模式深度解析
1. 組合模式：ANSI、TRADITIONAL
| 模式 | 包含子模式 | 行為特徵 |
|---|
ANSI | REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE + 部分語法調整 | 盡量符合標準 SQL，識別符用雙引號，|| 為字符串連接 |
TRADITIONAL | STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER | **「傳統」數據庫行為：錯誤即報錯，不警告。** 非事務表錯誤不回滾，可能導致部分寫入（慎用） |
2. 嚴格模式雙雄：STRICT_TRANS_TABLES vs STRICT_ALL_TABLES
定義：啟用後，非法/缺失數據值**報錯終止**，而非「調整值+警告」。
| 場景 | STRICT_TRANS_TABLES (事務表) | STRICT_ALL_TABLES (所有表) |
|---|
| 事務表 (InnoDB) | 報錯、回滾、終止語句 | 報錯、回滾、終止語句 |
| 非事務表 (MyISAM) - 第 1 行錯誤 | 報錯、終止、表不變 | 報錯、終止、表不變 |
| 非事務表 - 第 2+ 行錯誤 | 轉換為最接近合法值、插入、警告、繼續 | 報錯、忽略剩餘行、前面行**已寫入**（部分更新風險） |
生產建議：強制啟用 STRICT_TRANS_TABLES。非事務表多行插入錯誤時「修正繼續」比「部分寫入」更安全；單行語句可避免部分更新。
3. 日期時間嚴格控制三劍客
| 模式 | 行為 | 示例 |
|---|
NO_ZERO_DATE | 嚴格模式下拒絕 '0000-00-00'，非嚴格模式警告接受 | INSERT ... '0000-00-00' → Error |
NO_ZERO_IN_DATE | 嚴格模式下拒絕月/日為 0 的日期，非嚴格模式警告接受並存為零日期 | '2024-00-01', '2024-01-00' → Error |
ALLOW_INVALID_DATES | 嚴格模式下**僅檢查**月 1~12、日 1~31，**不檢查**日期合法性（如 2月30日），配合上二用 | Web 三字段組裝日期，用戶輸入不校驗 |
4. 數據完整性守門員
| 模式 | 行為 | 典型場景 |
|---|
ERROR_FOR_DIVISION_BY_ZERO | 嚴格模式下除零報錯；非嚴格返回 NULL+警告 | 財務計算、比率分析，防靜默 NULL 污染 |
NO_AUTO_CREATE_USER | GRANT 不自動創建用戶，需顯式 IDENTIFIED BY | 防誤建空密碼賬戶 (5.7 默認開啟，8.0 內建) |
NO_AUTO_VALUE_ON_ZERO | 自增列插入 0 **不**產生新序列，僅 NULL 觸發 | mysqldump 導出含 0 值自增列，導入保持原值 |
NO_ENGINE_SUBSTITUTION | 指定引擎不可用時**報錯**，而非靜默回退默認引擎 | 防建表時 InnoDB 不可用卻靜默建成 MyISAM |
5. 語法兼容性模式
| 模式 | 行為 | 影響 |
|---|
ANSI_QUOTES | " 為識別符引號，非字符串引號 | 字符串必須用單引號，遷移 Oracle/PostgreSQL 時常用 |
PIPES_AS_CONCAT | || 為字符串連接 (CONCAT)，非邏輯或 | Oracle/PostgreSQL 兼容 |
IGNORE_SPACE | 函數名與 ( 間允許空格，函數名成保留字 | 需引用同名表/列 SELECT "User" FROM mysql."user" |
HIGH_NOT_PRECEDENCE | NOT 優先級高於 BETWEEN (標準行為) | 舊版 NOT a BETWEEN b AND c 解析為 (NOT a) BETWEEN... |
6. 可移植模式：mysqldump 專用
NO_FIELD_OPTIONS、NO_KEY_OPTIONS、NO_TABLE_OPTIONS：SHOW CREATE TABLE 輸出不含 MySQL 專屬選項（ENGINE、AUTO_INCREMENT、COMMENT 等），便於異構遷移。
三、嚴格模式下的「逃生艙」
-- 單語句級禁用嚴格檢查
INSERT IGNORE INTO t (col) VALUES (invalid_val);
UPDATE IGNORE t SET col=invalid_val WHERE ...;
-- 會話級臨時寬容
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');
-- 操作完成後恢復
SET SESSION sql_mode = @@GLOBAL.sql_mode;
IGNORE 將錯誤降級為警告，非法值按非嚴格模式處理（調整/截斷/零值）。- 慎用：可能掩蓋數據質量問題。
四、遷移兼容策略：從寬容到嚴格的演進路徑
- 現狀基線：
SELECT @@GLOBAL.sql_mode 記錄當前模式。
- 影子驗證：測試庫啟用目標模式（如 5.7 默認），跑全量回歸/壓測，收集
SHOW WARNINGS/報錯日誌。
- 應用適配：
- 補全
NOT NULL 列默認值/應用層賦值。
- 修正日期邏輯（拒絕零日期/無效日期）。
- 處理除零分支（
CASE WHEN divisor=0 THEN ...）。
- 調整
GROUP BY 滿足 ONLY_FULL_GROUP_BY（SELECT 列必須出現在 GROUP BY 或聚合函數中）。
- 灰度發布：讀寫分離架構下，從庫先啟用新模式驗證，主庫後跟進。
- 全量切換：配置文件固化，
SET PERSIST 同步。
五、生產環境推薦配置（5.7/8.0）
[mysqld]
# 核心嚴格模式：事務表嚴格、日期嚴格、除零報錯、引擎不回退、自增列 0 不生成
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"
# 可選增強（視遷移成本）
# ONLY_FULL_GROUP_BY -- 強制標準 GROUP BY，防止非確定性結果
# ANSI_QUOTES -- 若遷移自 Oracle/PostgreSQL
# NO_AUTO_CREATE_USER -- 5.7 默認，8.0 內建
結語
SQL Mode 非「開啟越多越好」，而是**「業務需要什麼保護，就開啟什麼模式」**。從理解 STRICT_TRANS_TABLES 在非事務表多行插入時的「修正繼續」語義，到識別 ONLY_FULL_GROUP_BY 對舊查詢的破壞性；從 mysqldump 依賴 NO_AUTO_VALUE_ON_ZERO 保持自增值，到 IGNORE 關鍵字作為臨時逃生艙——每一個模式的取捨，都在為**數據質量、應用兼容、遷移平滑**投票。願本文解析助您制定屬於業務的「SQL 行為憲法」。
