MySQL "Data truncated for column" 錯誤根因分析與解決方案

錯誤現象

ERROR 1265 (01000): Data truncated for column 'col_name' at row 1
-- 或警告模式
Warning 1265: Data truncated for column 'col_name' at row 1

核心原因：數據超出列定義範圍

SQL Mode 影響行為差異

-- 查看當前模式
SELECT @@sql_mode;

-- 嚴格模式 (5.7+ 預設開啟 STRICT_TRANS_TABLES)
-- 行為：報錯、回滾事務、數據不入庫

-- 非嚴格模式
-- 行為：截斷數據、寫入截斷後值、產生 Warning、事務繼續

排查與定位步驟

1. 獲取完整錯誤上下文： 應用日誌、慢查詢日誌、SHOW WARNINGS; (緊跟失敗語句執行)
2. 對比數據與定義： SHOW CREATE TABLE t; 檢查列類型、長度、字符集
3. 定位具體行/值： 簡化測試用例、二分法縮小範圍、抓包分析網絡傳輸字節
4. 檢查應用層處理： 參數綁定類型、字符串截斷邏輯、編碼轉換、前端輸入驗證

解決方案

方案一：修正列定義 (治本、需評估影響)

-- 擴大長度
ALTER TABLE t MODIFY COLUMN col VARCHAR(500) CHARSET utf8mb4;

-- 修正數值類型
ALTER TABLE t MODIFY COLUMN col INT UNSIGNED;

-- 升級字符集支援 Emoji
ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

方案二：應用層截斷/驗證 (快速、無 DDL 風險)

// Java 範例
public String safeSubstring(String input, int maxLen) {
    if (input == null) return null;
    // 按字符數截斷 (非字節)，避免截斷多字節字符產生亂碼
    return input.length() <= maxLen ? input : input.substring(0, maxLen);
}

// 插入前驗證
if (value > 255) throw new IllegalArgumentException("Value exceeds TINYINT UNSIGNED range");

方案三：調整 SQL Mode (臨時規避、不推薦生產長期使用)

-- 會話級關閉嚴格模式
SET SESSION sql_mode = REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', '');
SET SESSION sql_mode = REPLACE(@@sql_mode, 'STRICT_ALL_TABLES', '');

-- 全域 (需重啟生效 my.cnf)
[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION  # 僅保留必要模式

預防體系

1. Schema 變更評審： 列長度、類型、字符集由 DBA/架構師把關
2. 應用層輸入驗證： 前端長度限制、後端 Bean Validation (@Size, @Max, @Pattern)
3. 測試數據覆蓋邊界： 單元測試包含最大長度、最大值、特殊字符、Emoji、NULL
4. 監控 Warning： SHOW WARNINGS 入庫、日誌告警「Data truncated」關鍵字
5. 統一 utf8mb4： 避免字符集不支援導致的隱性截斷

結語

Data truncated for column 本質是「數據不匹配定義」。嚴格模式下是硬錯誤保護數據質量；非嚴格模式下是隱性數據損壞源頭。解決路徑優先級：**修正定義 (DDL) > 應用層驗證 > 關閉嚴格模式**。建立「Schema 變更評審 + 應用驗證雙層 + Warning 監控」三道防線，從源頭杜絕截斷風險。