MySQL 客戶端中文亂碼終極解決：character_set_client/results 即時調整法

MySQL 客戶端中文亂碼終極解決：character_set_client/results 即時調整法

現象：INSERT 中文資料後，客戶端 SELECT 顯示亂碼 (如 鐜嬪煿鍧?)，但應用程式 (Java/PHP/Python) 讀取卻正常。原因在於客戶端連線字元集與資料庫儲存字元集不匹配。

診斷步驟

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | gbk                         |
| character_set_database   | gbk                         |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | gbk                         |
| character_set_system     | utf8                        |
| character_sets_dir       | D:\mysql6.0\share\charsets\ |
+--------------------------+-----------------------------+

關鍵發現：character_set_client 與 character_set_results 為 utf8，但 character_set_connection/database/server 為 gbk。客戶端以 UTF-8 送出資料，Server 以 GBK 解碼存儲，再以 UTF-8 回傳，導致雙重編碼錯位。

即時修正 (僅影響當前 Session)

SET character_set_client = gbk;
SET character_set_results = gbk;

再次查詢：

mysql> SHOW VARIABLES LIKE 'char%';
| character_set_client     | gbk                         |
| character_set_results    | gbk                         |
| character_set_connection | gbk                         |
| character_set_database   | gbk                         |
| character_set_server     | gbk                         |
...
mysql> SELECT * FROM test;
+------+--------+
| id   | name   |
+------+--------+
| 1    | 李曉峰 |
| 2    | 王曉峰 |
...
+------+--------+

中文即時正常顯示！

永久生效配置 (my.cnf / my.ini)

[client]
default-character-set = gbk

[mysql]
default-character-set = gbk

[mysqld]
character_set_server = gbk
collation_server = gbk_chinese_ci
character_set_client = gbk
character_set_connection = gbk
character_set_results = gbk

重啟服務後所有新連線自動套用。

最佳實務建議：統一使用 UTF-8 (utf8mb4)

GBK 僅支援中文，國際化專案必須遷移至 utf8mb4：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_results = utf8mb4

資料庫/表/欄位層級同步修改：

ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

應用層連線字串同步設定

• JDBC：jdbc:mysql://host:3306/db?characterEncoding=utf8mb4&useUnicode=true
• PHP PDO：charset=utf8mb4 in DSN
• Python PyMySQL：charset='utf8mb4'

快速驗證清單

1. SHOW VARIABLES LIKE 'character_set%'; 確認 client/connection/results/database/server 皆一致。
2. INSERT 中文 → SELECT 客戶端顯示正常。
3. 應用程式讀寫中文無亂碼、無截斷。
4. HEX(col) 檢查儲存位元組是否符合預期編碼。