解決 MySQL ERROR 1366 (HY000): Incorrect string value 字符集不匹配終極方案

解決 MySQL ERROR 1366 (HY000): Incorrect string value 字符集不匹配終極方案

報錯 ERROR 1366 (HY000): Incorrect string value: '\xXX\xXX...' for column 'COL' at row 1 是典型的**字符集不匹配**：客戶端發送編碼 (如 UTF-8) 與目標列/表/庫/連接/服務端實際編碼 (如 Latin1/GBK) 不一致，導致無法正確解碼存儲。本文從根因分析到全鏈路統一給出徹底解決方案。

一、 錯誤現場還原

INSERT INTO prod_product VALUES (5,'ATS','1','最終事物一致','','最終事物一致','1','1',NULL,'https://...',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
ERROR 1366 (HY000): Incorrect string value: '\xE6\x9C\x80\xE7\xBB\x88\xE4\xBA\x8B\xE7\x89\xA9\xE4\xB8\x80\xE8\x87\xB4' for column 'PROD_NAME' at row 1

\xE6\x9C\x80... 是 UTF-8 編碼的「最終事物一致」，但目標列無法識別。

二、 排查定位：全鏈路字符集檢查

-- 1. 服務端默認
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- 2. 數據庫/表/列級
SHOW CREATE DATABASE portaldb;
SHOW CREATE TABLE prod_product;
-- 關注 DEFAULT CHARSET=... COLLATE=...

-- 3. 當前連接
SELECT @@character_set_client, @@character_set_connection, @@character_set_results;

典型不匹配場景

三、 徹底解決：統一全鏈路為 UTF-8MB4

1. 修改服務端配置 my.cnf (核心)：

   [mysqld]
   character_set_server = utf8mb4
   collation_server = utf8mb4_general_ci
   init_connect = 'SET NAMES utf8mb4'  -- 強制連接初始化
   
   [client]
   default-character-set = utf8mb4
   
   [mysql]
   default-character-set = utf8mb4

   重啟 MySQL 生效。

2. 修正現有庫/表/列：

   -- 庫級
   ALTER DATABASE portaldb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
   
   -- 表級 (含列級聯轉換)
   ALTER TABLE prod_product CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
   
   -- 僅修改表默認不轉換現有列 (慎用)
   ALTER TABLE prod_product CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

3. 應用層連接配置：
   • JDBC: jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useSSL=false
   • Python (PyMySQL): charset='utf8mb4'
   • PHP (PDO): charset=utf8mb4 in DSN
   • 通用: 連接建立後立即執行 SET NAMES utf8mb4
4. 驗證插入：

   INSERT INTO prod_product (PROD_NAME) VALUES ('最終事物一致😀'); -- 支援 emoji
   SELECT * FROM prod_product WHERE PROD_NAME LIKE '%最終%';

四、 為什麼曾嘗試「只改表」失敗？

原文嘗試 ALTER TABLE ... CHARSET=utf8 後錯誤從 20 條減至 5 條，因為：

• ALTER TABLE ... CHARSET= **僅修改表默認字符集**，**不轉換現有列數據**。
• 列級仍保持舊編碼 (如 COLLATE utf8_bin)，新插入數據按列編碼處理，舊編碼列無法存儲 4 字節字符或與連接編碼衝突。
• 必須用 CONVERT TO CHARACTER SET 重建表並轉換數據。

五、 避坑指南：統一標準化清單

1. **新建項目**：庫/表/列/連接/配置文件 **全鏈路 utf8mb4**。
2. **遷移舊項目**：備份 → 修改 my.cnf 重啟 → CONVERT TO 所有表 → 更新應用連接參數 → 全量測試。
3. **拒絕 GBK/Latin1**：除非極少數 legacy 系統強制要求，否則統一 UTF-8MB4 避免跨平台亂碼。
4. **監控**：定期巡檢 SHOW VARIABLES LIKE 'character_set_%' 確保無偏移。

六、 總結

ERROR 1366 本質是**編碼斷層**。徹底治癒只有一條路：**服務端配置 + 現有對象轉換 + 連接參數** 三位一體統一為 utf8mb4。一次徹底配置，終身免受字符集之苦。