MySQL 對無效數據的約束機制詳解

在 MySQL 5.0.2 版本之前，資料庫對非法或不當數值的處理相當寬鬆，甚至會強制將其轉換為合法值以便數據輸入。自 MySQL 5.0.2 起，雖然保留了原有的預設行為，但引入了更傳統、嚴格的 SQL 模式，讓伺服器能夠拒絕並終止包含不良值的語句執行。

寬鬆模式下的預設行為

若未啟用嚴格模式，MySQL 會採取「盡最大可能修正」的策略：

• 數值型欄位：插入超出範圍的數值時，會存入 0（最小值）或該類型允許的最大值。
• 字串型欄位：存入空字串，或截斷字串以適應欄位長度。
• 非數值開頭的字串存入數值欄位：自動轉為 0。
• 日期時間欄位：允許儲存「2000-02-31」這類無效日期；若日期完全超出儲存範圍，則存入「0000-00-00」。
• NOT NULL 欄位插入 NULL：單行 INSERT 報錯；多行 INSERT 或 INSERT ... SELECT 則存入該類型的隱含預設值（數值為 0、字串為空字串、日期時間為「zero」值）。
• 未指定值的欄位：若有顯式 DEFAULT 子句則用其值，否則用隱含預設值。

這種設計源於早期存儲引擎不支援回滾，若執行到一半發現錯誤無法回滾，會導致「半數據更新」的最壞情況，因此選擇「盡力做到最好」然後繼續執行。

嚴格 SQL 模式

MySQL 5.0.2+ 提供兩種嚴格模式：

STRICT_TRANS_TABLES

• 事務引擎：語句中任何位置出現不良值，全部回滾。
• 非事務引擎：若錯誤發生在第 1 行，放棄語句（視同未修改）；第 1 行之後的錯誤則轉為警告並調整數值繼續執行。

STRICT_ALL_TABLES

對所有引擎一視同仁：任何行出錯都導致語句放棄。非事務表可能出現「前面幾行已寫入、後面失敗」的部分更新狀態。建議搭配單行語句或使用 INSERT IGNORE/UPDATE IGNORE 將錯誤降級為警告。

最佳實踐建議

最安全、通常也最快的方式是讓應用層負責數據驗證，僅向資料庫傳遞合法值。嚴格模式作為第二道防線，配合 IGNORE 關鍵字可靈活控制錯誤處理行為。