詳解 MySQL 的 sql_mode（SQL 模式）

一、sql_mode 核心概念

sql_mode 是 MySQL 中語法校驗、數據校驗、行為兼容的核心配置，它定義了 MySQL 對 SQL 語法的解析規則、數據有效性的校驗標準，以及與其他數據庫（如 Oracle、SQL Server）的兼容策略。

簡單來説：sql_mode 決定了 MySQL 是 “寬鬆模式” 還是 “嚴格模式”，以及支持哪些 SQL 語法、拒絕哪些非法數據。

核心作用

1. 規範 SQL 語法：限制或支持特定的 SQL 語法（如是否允許非標準標識符引用）；
2. 數據有效性校驗：阻止無效數據插入 / 更新（如非法日期、除以零、字符串截斷等）；
3. 兼容其他數據庫：模擬其他數據庫的 SQL 行為（如 Oracle 的字符串連接符 ||）；
4. 避免歧義行為：明確 SQL 執行邏輯（如分組查詢的字段限制）。

二、查看當前 sql_mode

1. 查看會話級 sql_mode（當前連接生效）

會話級僅對當前數據庫連接有效，斷開後失效：

sql

SELECT @@SESSION.sql_mode;
-- 或簡寫
SELECT @@sql_mode;

2. 查看全局級 sql_mode（所有新連接生效）

全局級對所有新建立的連接生效，但不影響已存在的連接：

sql

SELECT @@GLOBAL.sql_mode;

3. 配置文件位置（永久生效）

MySQL 的默認配置文件（my.cnf 或 my.ini）中，sql_mode 可通過配置項直接設置（後續詳解）。

三、修改 sql_mode 的方式

1. 會話級修改（臨時生效，重啓 / 斷開連接失效）

僅對當前連接有效，適合臨時測試場景：

sql

-- 設置會話級 sql_mode（示例：嚴格模式+分組限制）
SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';

2. 全局級修改（需重啓 MySQL 生效）

對所有新連接生效，但需重啓 MySQL 才能完全生效（或執行 FLUSH PRIVILEGES 刷新權限）：

sql

-- 設置全局級 sql_mode
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO';

-- 刷新權限（無需重啓，新連接立即生效）
FLUSH PRIVILEGES;

3. 配置文件修改（永久生效，推薦生產環境）

在 MySQL 配置文件（my.cnf 或 my.ini）中添加 / 修改 sql_mode，重啓 MySQL 後永久生效：

ini

# Linux/Mac（my.cnf 通常在 /etc/my.cnf 或 /etc/mysql/my.cnf）
# Windows（my.ini 通常在 MySQL 安裝目錄的 bin 文件夾）
[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

修改後重啓 MySQL：

bash

運行

# Linux 重啓命令
systemctl restart mysqld
# 或
service mysqld restart

# Windows 重啓命令（cmd 管理員模式）
net stop mysql
net start mysql

四、常見 sql_mode 取值詳解

sql_mode 支持多值組合（用逗號分隔），以下是最常用的模式值，按功能分類説明：

1. 嚴格模式相關（核心推薦生產啓用）

嚴格模式是數據校驗的核心，阻止無效數據寫入，避免髒數據。

示例：嚴格模式 vs 寬鬆模式

假設表結構：

sql

CREATE TABLE test_strict (
  id INT,
  name VARCHAR(5)  -- 姓名最長 5 個字符
) ENGINE=InnoDB;

• 寬鬆模式（未啓用 STRICT_TRANS_TABLES）：
  
  
  
  
  
  
  sql
  
  

INSERT INTO test_strict VALUES (1, 'abcdefgh'); -- 字符串長度 8 > 5
-- 警告：Data truncated for column 'name' at row 1
-- 結果：name 字段值為 'abcde'（截斷後）

• 嚴格模式（啓用 STRICT_TRANS_TABLES）：
  
  
  
  
  
  
  sql
  
  

INSERT INTO test_strict VALUES (1, 'abcdefgh');
-- 報錯：Data truncation: Data too long for column 'name' at row 1

2. 數據有效性校驗相關

示例：禁止全零日期

啓用 NO_ZERO_DATE + 嚴格模式：

sql

INSERT INTO test_date (create_time) VALUES ('0000-00-00');
-- 報錯：Invalid datetime value: '0000-00-00' for column 'create_time' at row 1

3. 語法兼容與規範相關

示例 1：ONLY_FULL_GROUP_BY（重點）

• 禁用 ONLY_FULL_GROUP_BY（寬鬆）：
  分組查詢允許非分組字段出現在 SELECT 中，結果可能隨機（同一分組下取第一條數據）：
  
  
  
  
  sql
  
  

SELECT name, age FROM user GROUP BY name; -- age 未分組，未聚合
-- 結果：返回每個 name 對應的第一條 age（不確定）

• 啓用 ONLY_FULL_GROUP_BY（嚴格）：
  
  
  
  
  
  
  sql
  
  

-- 正確：age 用聚合函數 AVG() 包裹
SELECT name, AVG(age) FROM user GROUP BY name;

-- 錯誤：age 未分組且未聚合
SELECT name, age FROM user GROUP BY name;
-- 報錯：Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

示例 2：PIPES_AS_CONCAT（兼容 Oracle）

啓用 PIPES_AS_CONCAT 後：

sql

SELECT 'Hello' || ' ' || 'MySQL' AS result; -- 等同於 CONCAT('Hello', ' ', 'MySQL')
-- 結果：result = 'Hello MySQL'

4. 常用模式組合

MySQL 提供了一些預定義的模式組合（本質是多值拼接）：

五、MySQL 不同版本的默認 sql_mode

1. MySQL 5.7+（推薦生產版本）

默認啓用嚴格模式組合，核心包含：

plaintext

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

（注：NO_AUTO_CREATE_USER 在 MySQL 8.0 中被移除，因為 8.0 不再支持 GRANT 語句自動創建用户，必須顯式執行 CREATE USER）

2. MySQL 8.0+

默認模式簡化（移除 NO_AUTO_CREATE_USER）：

plaintext

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

3. MySQL 5.6 及以下

默認是寬鬆模式（無 STRICT_*、ONLY_FULL_GROUP_BY 等），容易產生髒數據，不推薦直接使用。

六、常見問題與解決方案

1. 報錯：this is incompatible with sql_mode=only_full_group_by

原因

分組查詢中 SELECT 包含非分組、非聚合字段，違反 ONLY_FULL_GROUP_BY 規則。

解決方案

• 優先方案：優化 SQL，將非分組字段用聚合函數（SUM/AVG/MAX/ANY_VALUE()）包裹：
  
  
  
  
  sql
  
  

-- 用 ANY_VALUE() 取任意值（適合非核心字段）
SELECT name, ANY_VALUE(age) FROM user GROUP BY name;

• 不推薦方案：臨時關閉 ONLY_FULL_GROUP_BY（會犧牲數據一致性）：
  
  
  
  
  sql
  
  

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

2. 報錯：Invalid datetime value: '0000-00-00'

原因

啓用了 NO_ZERO_DATE + 嚴格模式，禁止插入全零日期。

解決方案

• 修正數據：將 '0000-00-00' 改為合法日期（如 '1970-01-01'）；
• 臨時關閉 NO_ZERO_DATE（不推薦生產）：
  
  
  
  
  sql
  
  

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'NO_ZERO_DATE', ''));

3. 遷移 Oracle 數據時，|| 無法連接字符串

解決方案

啓用 PIPES_AS_CONCAT 模式：

sql

SET GLOBAL sql_mode = CONCAT(@@GLOBAL.sql_mode, ',PIPES_AS_CONCAT');
FLUSH PRIVILEGES;

七、生產環境最佳實踐

1. 啓用嚴格模式組合：
   
   
   
   
   
   
   ini
   
   

sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

核心目的：阻止無效數據寫入，保證數據一致性。

1. 避免隨意關閉核心模式：
     如 ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES 等，關閉後可能導致數據歧義或髒數據。
2. 開發 / 測試環境與生產保持一致：
    
   
3. 遷移場景按需調整：

• 從 Oracle 遷移：添加 PIPES_AS_CONCAT, ANSI_QUOTES；
• 從舊系統遷移（含非法日期）：臨時關閉 NO_ZERO_IN_DATE, NO_ZERO_DATE，同步後修正數據再啓用。

4. 通過配置文件永久設置：
     避免使用 SET GLOBAL 臨時修改（MySQL 重啓後失效），優先在 my.cnf/my.ini 中配置。
5. 臨時設置模式組合：  set @@sql_mode = 'ANSI';  或者  set @@sql_mode = 'TRADITIONAL,STRICT_ALL_TABLES'; 

總結

sql_mode 是 MySQL 數據質量和語法兼容的核心配置，生產環境建議啓用嚴格模式 + 必要的兼容模式，通過規範 SQL 語法和校驗數據有效性，避免髒數據和歧義行為。理解各模式值的作用，結合業務場景（如遷移、舊系統兼容）靈活調整，是 MySQL 運維和開發的關鍵技能。

==================================================

MySQL 默認 sql_mode 不包含 STRICT_ALL_TABLES（即不完整套用 TRADITIONAL 組合），本質是 “平衡嚴格性與兼容性、適配主流場景”；而模式組合（如 TRADITIONAL、ANSI）的核心用處是 “提供預定義的、場景化的配置模板，簡化用户選型成本”。

下面分兩部分詳細拆解你的問題：

一、為什麼 MySQL 默認 sql_mode 缺少 TRADITIONAL 中的 STRICT_ALL_TABLES？

要理解這個設計，必須先回顧兩個關鍵前提：

1. STRICT_TRANS_TABLES vs STRICT_ALL_TABLES 的核心差異（之前提到過，這裏聚焦 “默認選擇邏輯”）：

• STRICT_TRANS_TABLES：僅對 事務表（如 InnoDB） 嚴格（無效數據報錯），對非事務表（如 MyISAM）寬鬆（僅警告、截斷數據）；
• STRICT_ALL_TABLES：對 所有表（事務 / 非事務） 嚴格（無效數據均報錯）。

2. MySQL 的 默認存儲引擎演變：

• MySQL 5.5 後默認引擎改為 InnoDB（事務表），至今仍是主流；
• 早期非事務表（MyISAM）逐漸被淘汰，但仍有舊系統依賴。

基於這兩個前提，MySQL 默認不選 STRICT_ALL_TABLES 的原因的是 “避免過度嚴格導致的副作用，兼顧主流場景與歷史兼容”：

1. 主流場景已被 STRICT_TRANS_TABLES 覆蓋

現在絕大多數業務用的是 InnoDB 事務表，STRICT_TRANS_TABLES 已經能滿足 “嚴格校驗數據、阻止髒數據” 的核心需求 —— 事務表支持回滾，一旦數據無效，報錯後整個事務回滾，不會出現 “部分數據寫入成功、部分失敗” 的情況，數據一致性有保障。

而 STRICT_ALL_TABLES 針對的是 非事務表（MyISAM），但這類表現在極少用，沒必要為了小眾場景讓所有用户承擔 “過度嚴格” 的成本。

2. STRICT_ALL_TABLES 對非事務表存在 “數據一致性風險”

非事務表的特性是 不支持回滾，如果啓用 STRICT_ALL_TABLES，會出現嚴重問題：

假設用 MyISAM 表批量插入 100 條數據，前 99 條有效，第 100 條無效 —— 此時 STRICT_ALL_TABLES 會直接報錯，但前 99 條數據已經寫入表中（無法回滾），導致 “部分數據成功、部分失敗” 的髒數據狀態。

而 STRICT_TRANS_TABLES 對非事務表的處理是 “寬鬆模式（警告 + 截斷）”，雖然會允許部分不嚴重的無效數據（如字符串截斷），但避免了 “批量插入中斷導致的數據碎片化”—— 這是 MySQL 權衡後的選擇：對小眾的非事務表，優先保證 “插入不中斷”，而非 “絕對嚴格”。

3. 歷史兼容：避免升級後舊系統大面積報錯

MySQL 5.6 及以下默認是 “完全寬鬆模式”，很多舊系統（尤其是依賴 MyISAM 表的系統）可能存在 “字符串截斷、無效日期” 等不規範數據插入邏輯。

如果默認啓用 STRICT_ALL_TABLES，這些舊系統升級後會直接大面積報錯，遷移成本極高。而 STRICT_TRANS_TABLES 只針對 InnoDB 嚴格，對舊系統的非事務表影響極小，兼顧了 “逐步收緊嚴格性” 和 “歷史系統兼容”。

二、模式組合（如 TRADITIONAL、ANSI）的實際用處是什麼？

模式組合的本質是 “MySQL 官方預定義的、經過場景優化的 sql_mode 集合”—— 它不強制用户使用，但能幫用户 “快速選型、減少配置錯誤”，核心用處有 3 點：

1. 簡化配置：一鍵啓用 “場景化規則”，不用手動拼接

如果沒有模式組合，用户要啓用 “嚴格模式”，需要手動拼接 5-6 個模式值：

sql

-- 手動拼接嚴格模式（容易漏寫、寫錯）
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

而用 TRADITIONAL 組合，一句話就能實現 “近似效果”（官方已幫你拼好核心嚴格規則）：

sql

-- 一鍵啓用“傳統嚴格模式”（包含上述所有規則+STRICT_ALL_TABLES）
SET GLOBAL sql_mode = 'TRADITIONAL';

對新手或快速部署場景，模式組合能避免 “漏配關鍵模式”（如忘記加 ERROR_FOR_DIVISION_BY_ZERO），降低配置門檻。

2. 場景化適配：快速對齊目標行為（兼容其他數據庫 / 嚴格校驗）

模式組合是為特定場景設計的 “模板”，用户可以根據需求直接選用，不用逐個研究單個模式的作用：

舉個實際例子：

如果你的業務需要從 Oracle 遷移到 MySQL，直接啓用 ANSI + PIPES_AS_CONCAT 組合，就能直接使用 Oracle 的 || 字符串連接語法，不用把所有 a||b 改成 CONCAT(a,b)，極大降低遷移成本。

3. 統一規範：團隊 / 系統間保持一致的 SQL 行為

模式組合是 “官方認證” 的配置模板，比團隊手動約定的 sql_mode 更權威、更合理。

比如團隊約定 “所有環境啓用 TRADITIONAL 模式”，就能避免出現 “開發環境用寬鬆模式、測試環境用嚴格模式” 的不一致問題，減少 “本地正常、線上報錯” 的排查成本。

補充：模式組合不是 “固定不變” 的，支持自定義擴展

模式組合是 “基礎模板”，用户可以根據需求修改 —— 比如你需要 TRADITIONAL 的嚴格性，但不想用 STRICT_ALL_TABLES（因為有少量 MyISAM 表），可以這樣配置：

ini

-- 基於 TRADITIONAL 組合，去掉 STRICT_ALL_TABLES
sql_mode = "TRADITIONAL,STRICT_TRANS_TABLES"

（原理：後配置的模式會覆蓋組合中衝突的規則，最終等效於 TRADITIONAL 去掉 STRICT_ALL_TABLES，保留其他嚴格規則）

總結

1. 默認 sql_mode 缺少 STRICT_ALL_TABLES 的原因：
     為了 “適配主流 InnoDB 事務表、避免非事務表的數據一致性風險、兼容舊系統”，MySQL 選擇了 “適度嚴格” 的 STRICT_TRANS_TABLES，而非 “過度嚴格” 的 STRICT_ALL_TABLES，是平衡後的最優解。
2. 模式組合的核心用處：
    
   

簡單説：模式組合是 “給用户的快捷選項”，而默認 sql_mode 是 “MySQL 為大多數用户選的最優默認選項”—— 兩者互補，用户可根據自身場景（如是否用非事務表、是否需要兼容其他數據庫）選擇直接用組合模式，或基於默認值微調。