默認 sql_mode=''(空)允許非法數據寫入(如 0 日期、截斷不報錯、分組不完整),生產環境**必須啟用嚴格模式**,開發測試環境同步配置,提前攔截問題。
| 模式 | 作用 | 生產建議 |
|---|---|---|
STRICT_TRANS_TABLES | 事務表非法數據報錯中斷,非事務表截斷警告 | 必開 |
STRICT_ALL_TABLES | 所有表非法數據報錯 | 推薦,更嚴格 |
ONLY_FULL_GROUP_BY | SELECT 非聚合列必須出現在 GROUP BY 中 | 必開,防止語義歧義 |
NO_ZERO_DATE | 禁止 '0000-00-00' 日期 | 必開 |
NO_ZERO_IN_DATE | 禁止月/日為 0 的日期 | 必開 |
ERROR_FOR_DIVISION_BY_ZERO | 除零報錯而非返回 NULL | 必開 |
NO_AUTO_CREATE_USER | 禁止 GRANT 隱式創建空密碼用戶 | 必開(5.7.7+ 默認開啟) |
NO_ENGINE_SUBSTITUTION | 引擎不可用時報錯而非靜默替換 | 必開 |
NO_AUTO_VALUE_ON_ZERO | 自增列插入 0 不生成下一值 | 視業務,通常關閉 |
PIPES_AS_CONCAT | || 作為字符串拼接而非 OR | Oracle 兼容需開啟 |
ANSI_QUOTES | 雙引號作為標識符而非字符串 | Oracle 兼容需開啟 |
[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'
[mysqld]
sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,
NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,
NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'
配合 lower_case_table_names=1、default-storage-engine=InnoDB,可最大程度降低遷移成本。
-- 查看當前
SELECT @@sql_mode;
SHOW VARIABLES LIKE 'sql_mode';
-- 會話級臨時調整
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE';
-- 全局動態調整(需 SUPER,重啟失效)
SET GLOBAL sql_mode = '...';
| 問題 | 原因 | 解決 |
|---|---|---|
| INSERT 日期 '0000-00-00' 報錯 | NO_ZERO_DATE | 應用層修正數據、或用 NULL 代替 |
| GROUP BY 報錯 | ONLY_FULL_GROUP_BY | 補全 GROUP BY 列、或用 ANY_VALUE() |
| 除零不報錯返回 NULL | 缺 ERROR_FOR_DIVISION_BY_ZERO | 開啟該模式 |
| 隱式創建無密碼用戶 | 缺 NO_AUTO_CREATE_USER | 開啟、審計現有用戶 |
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_SUBSTITUTIONNO_AUTO_CREATE_USER(合併到創建用戶語法)