交接變救火？SQL Mode 與資料溢位的隱藏陷阱
Limit
Gentle and strong, Limit shines.
Limit
Gentle and strong, Limit shines.
「dev 能跑、prod 會炸，是這世界的命運……等等，這次是反過來？」
在軟體開發中，我們通常擔心的是「dev 能跑但 prod 會炸」。但今天要分享的案例更詭異：dev 環境擋下了錯誤，但 production 卻「成功」寫入了不合法的資料。這個真實案例揭示了 MySQL SQL Mode 設定的重要性。
分享目標
- 分享真實的 SQL Mode 設定差異案例
- 理解 MySQL 如何處理資料溢位與驗證
- 建立正確的資料驗證策略
真實案例：只是交個接，怎麼變我在救火？
情境描述
- RD 交接與測試某個內部 CRM 系統的功能
- 在 dev 環境模擬 prod 環境某筆 OpenAPI 的 API call 紀錄，試圖建立相似 payload，並 call 內部 API
- 發現建立時被擋下，server 端收到「ERR_DATA_TOO_LONG」的資訊，建立失敗（來自 MySQL client）
- 追查後發現是
tbl_example.quotation_id溢位 - 但檢查 prod 監控 chat 群組並無出現此錯誤，推測 production 環境是有建立成功的
- 和有 prod 環境權限的同仁確認該筆紀錄有建立成功
系統架構
附註：由於公司內部架構屬於機敏資訊，本文將經過一定的抽象化處理。
問題分析
- Schema 沒問題
- Production 環境的 SQL Mode 較寬鬆
- Dev 環境的 SQL Mode 較嚴格
Dev 環境的 SQL Mode：
- ONLY_FULL_GROUP_BY
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- ERROR_FOR_DIVISION_BY_ZERO
- NO_ENGINE_SUBSTITUTION
dev 太嚴、prod 太鬆：我在地獄兩端來回穿梭
當 dev 比 prod 嚴，世界就開始錯亂了。
幸與不幸
- 幸運的是：前人有存
quotation_link，因此 user 沒有回報變成 production issue - 不幸的是：後續要做 Data Fix（從 quotation_link 拔出來）
- 更不幸的是：這種「沉默的資料錯誤」可能已經累積了一段時間
資料完整性的危機
這個案例暴露了一個嚴重問題：
- Production 環境「成功」寫入了不符合 schema 定義的資料
- 資料被無聲地截斷，沒有任何警告
- 如果沒有備用欄位，資料就永久損失了
現象說明
VARCHAR(20) 寫入長度 26 的字串為什麼沒報錯？
- SQL mode 沒有 Strict mode 只會跳 Warning，不會噴錯
- → 不會觸發 transaction rollback
- → 資料被截斷成 20 個字元「成功」寫入
dev 跑不過，prod 卻「正常」寫入？
- 我以為只有 prod 會炸，沒想到 dev SQL Mode 根本煉獄級
- 在沒有
STRICT_TRANS_TABLES的環境中：- 超長字串 → 截斷
- 無效日期 → 自動轉換為
0000-00-00 - 除以零 → 返回 NULL
不同 sql_mode 的行為差異
| SQL Mode | 寫入超長字串時 | 備註 |
|---|---|---|
無 STRICT_* | ✅ 寫入成功（但被截斷） | silent fail |
STRICT_TRANS_TABLES | ❌ 拋出錯誤 | 建議啟用 |
TRADITIONAL | ❌ 更嚴格檢查 | 與 STRICT 類似但更嚴格 |
查看 SQL Mode 指令
已複製！SELECT @@sql_mode;
設定 SQL Mode（session）
已複製！SET sql_mode = 'STRICT_TRANS_TABLES';
實際測試範例
已複製！-- 建立測試表格 CREATE TABLE test_table ( id INT PRIMARY KEY, name VARCHAR(5) ); -- 測試不同 SQL Mode 下的行為 SET sql_mode = ''; -- 非嚴格模式 INSERT INTO test_table (id, name) VALUES (1, 'Hello World'); -- 會成功但截斷為 'Hello' SET sql_mode = 'STRICT_TRANS_TABLES'; -- 嚴格模式 INSERT INTO test_table (id, name) VALUES (2, 'Hello World'); -- 會失敗並拋出錯誤
實務建議
資料庫層面
- Production 務必啟用
STRICT_TRANS_TABLES- 寧願當下失敗，也不要沉默地損壞資料
- 配合適當的錯誤處理機制
- 所有環境（dev, staging, prod）使用相同的 SQL Mode
- 在 Docker 或 CI/CD 環境中明確設定 SQL Mode
Schema 設計
- Schema 設計保留適當的 buffer
VARCHAR(20)→ 考慮改為VARCHAR(50)或更大- 根據實際業務需求評估合理的長度
- 使用
TEXT或JSON類型儲存不確定長度的內容
應用層驗證
- 不要完全依賴資料庫的限制
- 搭配後端驗證，避免依賴 DB 自行截斷
- 實作完整的輸入驗證邏輯
已複製！
// Backend validation example if (quotationId.length > 20) { throw new ValidationError('quotation_id length cannot exceed 20 characters'); }
監控與告警
- 監控 MySQL warnings 的數量
- 定期檢查是否有資料被截斷的情況
- 建立資料完整性檢查機制
小結
- SQL Mode 的差異會導致完全不同的資料處理行為
- 寬鬆的 SQL Mode 會讓錯誤「沉默」，造成資料完整性問題
- 明確啟用
STRICT_TRANS_TABLES是保護資料完整性的第一道防線 - 環境設定一致性至關重要
- 永遠不要假設資料庫會幫你攔截所有錯誤
延伸閱讀
Q&A
你曾遇過：
- 寫入資料「成功但資料不對」的情況？
- 因為 SQL Mode 差異導致的環境問題？
- 資料被無聲截斷而導致的 bug？
歡迎在下方留言分享你的經驗！