MySQL 編碼亂碼根治：從伺服器、資料庫、表、欄位、連線五層統一字元集
MySQL 預設 latin1 不支援中文，導致存入亂碼、查詢亂碼、排序錯誤。徹底解決需在 伺服器、資料庫、表、欄位、連線 五層統一設為 utf8mb4 (支援 Emoji/完整 Unicode) 或 utf8 (BMP 字元)，強烈建議 utf8mb4。
一、查看當前字元集狀態
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
-- 關鍵變數
character_set_server -- 伺服器預設
character_set_database -- 當前資料庫預設
character_set_client -- 客戶端發送編碼
character_set_connection -- 連線處理編碼
character_set_results -- 結果集返回編碼
二、永久修改：配置文件 my.cnf (需重啟)
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
# skip-character-set-client-handshake # 強制忽略客戶端設定 (慎用)
character-set-server：新建資料庫/表的預設字元集。init_connect：非超級用戶連線時自動執行 SET NAMES。- 重啟後驗證：
SHOW VARIABLES LIKE 'character%'; 應全為 utf8mb4 (filesystem 除外)。
三、現有物件字元集轉換 (不重啟，但鎖表)
| 層級 | 語法 | 備註 |
|---|
| 資料庫 | ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; | 僅影響後續新建表 |
| 表 | ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | 重建表，轉換現有資料，大表鎖表久，低峰操作 |
| 欄位 | ALTER TABLE tbl_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; | 單欄轉換，同樣重建表 |
四、連線層指定 (應用程式碼)
- JDBC：
jdbc:mysql://host:3306/db?useUnicode=true&characterEncoding=utf8mb4&zeroDateTimeBehavior=convertToNull (無空格)。
- PHP PDO：
new PDO("mysql:host=...;dbname=...;charset=utf8mb4", $user, $pass);
- Python PyMySQL：
charset='utf8mb4' 參數。
- Go sqlx：
db.SetConnMaxLifetime... DSN 加 charset=utf8mb4。
五、臨時會話修改 (僅當前連線生效)
SET NAMES utf8mb4; -- 等同於設定 client/connection/results 三者
SET CHARACTER SET utf8mb4; -- 設定 client/results，connection 由伺服器決定
-- 查看會話變數
SHOW SESSION VARIABLES LIKE 'character%';
六、utf8 vs utf8mb4 選型指南
| 特性 | utf8 (utf8mb3) | utf8mb4 |
|---|
| 最大字元長度 | 3 bytes | 4 bytes |
| Emoji/補充平面 | ❌ 儲存截斷/報錯 | ✅ 完整支援 |
| 索引長度限制 | 767 bytes (InnoDB) | 767 bytes (需 innodb_large_prefix=ON 才 3072) |
| 儲存空間 | 略小 | 略大 (ASCII 同樣 1 byte) |
| 建議 | 舊系統相容 | 新專案預設 |
七、常見亂碼場景診斷
| 現象 | 原因 | 修正 |
|---|
| 存入 ??? / | 伺服器/表為 latin1，連線為 utf8 | 統一 utf8mb4，重建表轉換資料 |
| 存入亂碼但查詢正常 | 雙重編碼 (utf8→latin1→utf8) | 檢查應用層 SET NAMES、JDBC 參數 |
| ORDER BY 中文排序錯誤 | Collation 不支援中文 (如 latin1_swedish_ci) | 改用 utf8mb4_unicode_ci 或 utf8mb4_zh_0900_as_cs |
| Emoji 截斷/報錯 | 使用 utf8 (3 bytes) | 升級 utf8mb4，調整 VARCHAR 長度 |
