萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 5.5+ sql_mode 配置:移除 STRICT_TRANS_TABLES 解決兼容性問題

MySQL sql_mode 配置調整:兼容性與嚴格模式平衡

MySQL 5.5+ 預設開啟 STRICT_TRANS_TABLES 嚴格模式,可能導致舊應用插入「零日期」、「超長字符串」等報錯。本文說明調整策略。

預設 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

常見兼容性問題

模式限制行為典型報錯
STRICT_TRANS_TABLES事務表嚴格模式:非法值報錯而非截斷/轉換Data truncated / Incorrect integer value
NO_ZERO_IN_DATE禁止月/日為 0 的日期Incorrect date value: '2023-00-01'
NO_ZERO_DATE禁止 '0000-00-00' 日期Incorrect date value: '0000-00-00'
ONLY_FULL_GROUP_BYGROUP BY 必須包含所有非聚合欄位Incompatible with sql_mode=only_full_group_by
NO_AUTO_CREATE_USERGRANT 不自動建用戶需先 CREATE USER

調整方案

方案一:移除 STRICT_TRANS_TABLES (快速相容舊代碼)

# my.cnf / my.ini
[mysqld]
# 原配置
# sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# 調整後:移除 STRICT_TRANS_TABLES
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# 或完全關閉嚴格模式 (不推薦生產)
# sql-mode=""

方案二:動態調整 (無需重啟,重啟失效)

-- 全局
SET GLOBAL sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 會話級 (僅當前連線)
SET SESSION sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

方案三:保留嚴格模式,修正應用代碼 (推薦長遠)

  • 日期欄位預設用 NULL 或 '1970-01-01' 而非 '0000-00-00'
  • 字符串長度驗證在應用層完成
  • GROUP BY 列出所有非聚合欄位或使用 ANY_VALUE()

版本差異提醒

版本預設 sql_mode 關鍵變化
5.5引入 STRICT_TRANS_TABLES 預設開啟
5.6新增 NO_ENGINE_SUBSTITUTION 預設開啟
5.7新增 ONLY_FULL_GROUP_BY 預設開啟 (影響最大)
8.0移除 NO_AUTO_CREATE_USER,移除 PASSWORD() 函數

查看當前模式

SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

最佳實踐建議

  1. 新專案:保留完整預設 sql_mode,代碼層面做嚴格驗證
  2. 舊專案遷移:臨時移除 STRICT_TRANS_TABLES、ONLY_FULL_GROUP_BY,並行修正代碼
  3. 分階段收斂:先會話級關閉,驗證無誤後調整全局配置
  4. 監控報錯:開啟 log_warnings=2 記錄截斷/轉換警告

sql_mode 是資料完整性的守門員,能不關就不關;必須關時,限定範圍、設定期限、同步修代碼。

copyright © 萬盛學電腦網 all rights reserved