您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL sql_mode 生產環境推薦配置：嚴格模式、Oracle 相容與避坑指南

編輯：MySQL 資料庫相關知識

為什麼要設定 sql_mode

預設空值：允許非法資料寫入 (如 '2023-00-00'、截斷不報錯)
開發/測試/生產必須一致，才能在早期發現問題
避免「開發庫跑得好，上線炸了」

核心模式值解析

生產環境推薦配置 (my.cnf)

[mysqld]
sql_mode = '
    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
'

Oracle 相容模式 (遷移專案專用)

sql_mode = '
    PIPES_AS_CONCAT,
    ANSI_QUOTES,
    IGNORE_SPACE,
    NO_KEY_OPTIONS,
    NO_TABLE_OPTIONS,
    NO_FIELD_OPTIONS,
    NO_AUTO_CREATE_USER
'

驗證與動態調整

SELECT @@sql_mode;           -- 當前會話
SELECT @@GLOBAL.sql_mode;     -- 全域
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE';  -- 臨時調整

常見遷移衝突與對策

模式	作用	建議
`ONLY_FULL_GROUP_BY`	SELECT 非聚合欄位必須出現在 GROUP BY	開啟 (SQL 標準)
`STRICT_TRANS_TABLES`	事務表非法資料直接報錯中斷	開啟 (核心嚴格模式)
`NO_ZERO_IN_DATE`	禁止月/日為 0 的日期	開啟
`NO_ZERO_DATE`	禁止 '0000-00-00' 日期	開啟
`ERROR_FOR_DIVISION_BY_ZERO`	除零報錯而非回傳 NULL	開啟
`NO_AUTO_CREATE_USER`	禁止 GRANT 建立空密碼用戶	開啟 (安全)
`NO_ENGINE_SUBSTITUTION`	引擎不可用時報錯而非靜默替換	開啟
`NO_AUTO_VALUE_ON_ZERO`	自增欄位插入 0 不產生新序號	視需求 (通常關閉)
`PIPES_AS_CONCAT`	`\|\|` 視為字串連接 (Oracle 相容)	Oracle 遷移專案開啟
`ANSI_QUOTES`	`"` 視為識別符而非字串 (Oracle 相容)	Oracle 遷移專案開啟

舊行為	嚴格模式下	修正方式
INSERT 日期 '2023-00-00'	報錯	修正資料或用 NULL
VARCHAR 超長截斷不報錯	報錯	調整欄位長度或截斷邏輯
GROUP BY 選取非聚合欄位	報錯	加入 GROUP BY 或用聚合函數
除零回傳 NULL	報錯	CASE WHEN divisor=0 THEN ...

版本差異提醒

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
5.6 需手動配置，5.5 僅部分支援
8.0 移除部分鬆散模式，嚴格更徹底