問題現(xiàn)象

后臺應(yīng)用程序使用的是云上的 MySQL 服務(wù)，需要給 MySQL 數(shù)據(jù)表里 insert 一些數(shù)據(jù)，平時(shí)都是先運(yùn)行一個(gè) MySQL 的 pod：

kubectl run mysql-client --rm -it --restart='Never' --image mysql:5.7 --command -- env LANG=C.UTF-8 mysql -hx.x.x.x -uusername -ppassword

然后將 insert SQL 文件復(fù)制到 MySQL pod 里面，在 MySQL pod 中執(zhí)行 source SQL 文件命令。這次 insert 數(shù)據(jù)時(shí)發(fā)現(xiàn)環(huán)境上已經(jīng)有運(yùn)行的 MySQL pod了，就將 SQL 文件復(fù)制到已運(yùn)行的 MySQL pod中，然后通過命令進(jìn)入到MySQL pod里，再連接到云上 MySQL：

kubectl exec -it mysql-client -- bash
mysql -hx.x.x.x -uusername -ppassword

接著執(zhí)行 source SQL文件，然后通過前端頁面查看錄入的數(shù)據(jù)，發(fā)現(xiàn)是亂碼。但是在執(zhí)行 source 命令的 MySQL 客戶端 select 查詢錄入的數(shù)據(jù)卻是預(yù)期的中文字符。

問題原因

例如 source 執(zhí)行的 SQL文件中的 SQL 語句是

INSERT INTO table_1 (title) VALUES ('好');

SQL文件是UTF8編碼的，MySQL 客戶端向 MySQL 服務(wù)器發(fā)送的 title 字段值的 “好” 的 UTF8編碼字節(jié)序列，十六進(jìn)制表示是 E5A5BD。

mysql-client pod的字符集是 POSIX，MySQL 客戶端向 MySQL 服務(wù)器發(fā)送數(shù)據(jù)采用的就是 latin1編碼，MySQL 服務(wù)器收到數(shù)據(jù)后，使用 latin1 解碼 E5A5BD ，得到字符串 好。

root@mysql-client:/# locale
LANG=
LANGUAGE=
LC_CTYPE="POSIX"
LC_NUMERIC="POSIX"
LC_TIME="POSIX"
LC_COLLATE="POSIX"
LC_MONETARY="POSIX"
LC_MESSAGES="POSIX"
LC_PAPER="POSIX"
LC_NAME="POSIX"
LC_ADDRESS="POSIX"
LC_TELEPHONE="POSIX"
LC_MEASUREMENT="POSIX"
LC_IDENTIFICATION="POSIX"
LC_ALL=
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

由于數(shù)據(jù)表字段的字符集是 UTF8，MySQL 服務(wù)器再將字符串 好 用 UTF8 編碼得到字節(jié)序列 C3A5C2A5C2BD，這個(gè)可以通過如下 SQL 語句查詢證實(shí)。

select HEX(title) from table_1;

這里說一下在驗(yàn)證此過程時(shí)遇到的問題：

剛開始使用的中文字符“我”進(jìn)行驗(yàn)證，對應(yīng)的 UTF8 編碼是 e68891，88 和 91 （位于 80 和 9f 之間）在 latin1 編碼中對應(yīng)的是控制字符，手動(dòng)解碼后的字符不是正常字符，再使用 UTF8 編碼時(shí)為 C3A6C288C291，和數(shù)據(jù)表中存儲(chǔ)的 C3A6CB86E28098 不一樣（MySQL 代碼中編碼肯定對控制字符進(jìn)行了正確編碼），為了避免控制字符，想到選用不在 80 和 9f 之間的中文字符“好” E5A5BD ，這樣手動(dòng)編碼后和數(shù)據(jù)表存儲(chǔ)的都是 C3A5C2A5C2BD，這才驗(yàn)證了這個(gè)過程。

頁面查詢亂碼的原因：

前端頁面通過調(diào)用后臺接口查詢數(shù)據(jù)，后臺服務(wù)連接 MySQL 使用的字符集是 UTF8，所以character_set_results 就是 UTF8。

MySQL 服務(wù)器從數(shù)據(jù)表中查詢的字節(jié)序列是 C3A5C2A5C2BD，數(shù)據(jù)表字段的編碼也是 UTF8，和 character_set_results 一樣，發(fā)送給后臺服務(wù)客戶端的字節(jié)序列就是 C3A5C2A5C2BD。

后臺服務(wù)使用 UTF8 對 C3A5C2A5C2BD 解碼得到 好，所以前端頁面顯示的就是 好，而不是預(yù)期的中文字符 ”好“。

MySQL 命令行客戶端select 查詢正常的原因：

MySQL 命令行客戶端 session 的 character_set_results 是 latin1 。

MySQL 服務(wù)器從數(shù)據(jù)表中查詢的字節(jié)序列是 C3A5C2A5C2BD，使用 UTF8 解碼后是 好。

再使用 character_set_results 的字符集 latin1 進(jìn)行編碼得到 E5A5BD，將字符序列 E5A5BD 發(fā)送給 MySQL 命令行客戶端。

再發(fā)送給本地的圖形界面的終端模擬器 MobaXterm，MobaXterm 使用的字符集是 UTF8，使用 UTF8 對 E5A5BD 解碼輸出中文字符“好”。

解決方法

連接云上 MySQL 時(shí)指定字符集為 utf8

mysql -hx.x.x.x -uusername -ppassword --default-character-set=utf8

將 MySQL pod 的字符編碼設(shè)置為 UTF8， 這樣 MySQL 客戶端連接服務(wù)器時(shí)使用的字符集就是 utf8

export LANG=C.UTF-8

或者直接在如下命令啟動(dòng)的 MySQL 客戶端中執(zhí)行 source 命令，此命令通過 env LANG=C.UTF-8 設(shè)置了 pod的字符編碼為 UTF8：

kubectl run mysql-client --rm -it --restart='Never' --image mysql:5.7 --command -- env LANG=C.UTF-8 mysql -hx.x.x.x -uusername -ppassword

這樣，MySQL 的 character_set_client、character_set_connection、character_set_results都會(huì)設(shè)置為 utf8， 就和數(shù)據(jù)表字段的字符集保持一致，不會(huì)出現(xiàn)亂碼問題。

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MySQL 客戶端和服務(wù)器通信中的字符集處理

客戶端給服務(wù)器發(fā)送消息過程

• 如果 mysql 命令中沒有指定 --default-character-set 參數(shù)，客戶端使用操作系統(tǒng)字符集對消息編碼發(fā)送給服務(wù)器，否則使用 --default-character-set 參數(shù)的字符集對消息編碼。
• 服務(wù)器將 character_set_client、 character_set_connection、character_set_results 設(shè)置為客戶端的字符集。
• 收到客戶端的消息后，使用 character_set_client 字符集對消息解碼。
• 再用 character_set_connection 對應(yīng)的字符集對解碼后的消息編碼后處理。

服務(wù)器處理消息時(shí)要轉(zhuǎn)換為 character_set_connection 字符集進(jìn)行處理，比較規(guī)則只有 connection 有，character_set_client 和 character_set_results 都沒有：

mysql> show variables like 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+
3 rows in set (0.00 sec)

服務(wù)器給客戶端發(fā)送消息過程

• 服務(wù)器從數(shù)據(jù)表中查詢字段內(nèi)容
• 將字符內(nèi)容先使用字段的字符集解碼，再使用 character_set_results 字符集編碼后發(fā)給客戶端。
• 客戶端使用操作系統(tǒng)的字符集解碼消息進(jìn)行展示，這里對于使用本地圖形界面的終端模擬器登錄遠(yuǎn)程主機(jī)的場景來說，消息還會(huì)發(fā)送到本地圖形界面的終端模擬器，使用終端模擬器的字符集對消息解碼再展示出來。

到此這篇關(guān)于MySQL insert 記錄后查詢是亂碼問題分析的文章就介紹到這了,更多相關(guān)mysql insert 查詢亂碼內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論