MySQL 嚴格模式詳解：從寬鬆預設到拒絕非法資料的遷移指南

MySQL 5.0.2+ 嚴格模式（Strict SQL Mode）完整解析

預設行為（非嚴格模式）：「盡力而為」

• 超範圍數值 → 存 0 或最大值
• 字串過長 → 截斷存入
• 非數字字串存數值欄位 → 存 0
• 不合法日期（2000-02-31）→ 存入；完全非法 → 存 0000-00-00
• NULL 存入 NOT NULL 欄位：單行報錯，多行/子查詢 → 存隱含預設值（0/空字串/零日期）
• 未指定值且無 DEFAULT → 存隱含預設值

設計哲學： 執行中才發現問題，回滾成本高（引擎可能不支援），寧可「做到最好」繼續執行。

嚴格模式兩種等級

啟用方式

-- my.cnf 永久生效
[mysqld]
sql_mode = STRICT_TRANS_TABLES,STRICT_ALL_TABLES,...

-- 會話層級臨時生效
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';

遷移建議

1. **應用端先行驗證**：最安全、通常最快，只傳合法值給資料庫
2. 測試環境先啟用嚴格模式，觀察 SHOW WARNINGS 找出問題 SQL
3. 需保留舊行為時，用 INSERT IGNORE / UPDATE IGNORE 將錯誤降級為警告
4. 生產環境建議啟用 STRICT_TRANS_TABLES（InnoDB 為主），兼顧資料品質與可用性

常見 sql_mode 組合

-- 傳統嚴格模式
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

-- 完整嚴格（含除零錯誤、日期檢查等）
STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

結語： 嚴格模式是資料品質的守門員，配合應用端驗證，才能從源頭杜絕髒資料。