詳解 MySQL 的 sql_mode（SQL 模式）

一、sql_mode 核心概念

核心作用

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

二、查看當前 sql_mode

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

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

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

SELECT @@GLOBAL.sql_mode;

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

三、修改 sql_mode 的方式

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

-- 設置會話級 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 生效）

-- 設置全局級 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. 配置文件修改（永久生效，推薦生產環境）

# 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"

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

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

四、常見 sql_mode 取值詳解

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

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

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

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

   

  插入超長字符串時，MySQL 自動截斷並警告，數據仍寫入：
  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. 數據有效性校驗相關

示例：禁止全零日期

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）

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

4. 常用模式組合

五、MySQL 不同版本的默認 sql_mode

1. 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

2. MySQL 8.0+

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 及以下

六、常見問題與解決方案

1. 報錯：this is incompatible with sql_mode=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'

原因

解決方案

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

   

   

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

   
   

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

解決方案

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

七、生產環境最佳實踐

1. 啓用嚴格模式組合：

    

   推薦配置（兼容 MySQL 5.7/8.0）：
   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"
   

    
    
   核心目的：阻止無效數據寫入，保證數據一致性。
2. 避免隨意關閉核心模式：

    

   如 ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES 等，關閉後可能導致數據歧義或髒數據。
3. 開發 / 測試環境與生產保持一致：

    

   避免開發環境寬鬆、生產環境嚴格導致的 “本地正常，線上報錯”。
4. 遷移場景按需調整：
   • 從 Oracle 遷移：添加 PIPES_AS_CONCAT, ANSI_QUOTES；
   • 從舊系統遷移（含非法日期）：臨時關閉 NO_ZERO_IN_DATE, NO_ZERO_DATE，同步後修正數據再啓用。
5. 通過配置文件永久設置：

    

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

總結

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

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

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