MySQL insert 记录后查询是乱码问题分析

时间:2024-10-16 14:49:46

问题现象

后台应用程序使用的是云上的 MySQL 服务,需要给 MySQL 数据表里 insert 一些数据,平时都是先运行一个 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 文件复制到 MySQL pod 里面,在 MySQL pod 中执行 source SQL 文件命令。这次 insert 数据时发现环境上已经有运行的 MySQL pod了,就将 SQL 文件复制到已运行的 MySQL pod中,然后通过命令进入到MySQL pod里,再连接到云上 MySQL:

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

接着执行 source SQL文件,然后通过前端页面查看录入的数据,发现是乱码。但是在执行 source 命令的 MySQL 客户端 select 查询录入的数据却是预期的中文字符。

问题原因

例如 source 执行的 SQL文件中的 SQL 语句是

INSERT INTO table_1 (title) VALUES ('好');
  1. SQL文件是UTF8编码的,MySQL 客户端向 MySQL 服务器发送的 title 字段值的 “好” 的 UTF8编码字节序列,十六进制表示是 E5A5BD。

  2. mysql-client pod的字符集是 POSIX,MySQL 客户端向 MySQL 服务器发送数据采用的就是 latin1编码,MySQL 服务器收到数据后,使用 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)
    
    
  3. 由于数据表字段的字符集是 UTF8,MySQL 服务器再将字符串 好 用 UTF8 编码得到字节序列 C3A5C2A5C2BD,这个可以通过如下 SQL 语句查询证实。

    select HEX(title) from table_1;
    

这里说一下在验证此过程时遇到的问题:

刚开始使用的中文字符“我”进行验证,对应的 UTF8 编码是 e68891,88 和 91 (位于 80 和 9f 之间)在 latin1 编码中对应的是控制字符,手动解码后的字符不是正常字符,再使用 UTF8 编码时为 C3A6C288C291,和数据表中存储的 C3A6CB86E28098 不一样(MySQL 代码中编码肯定对控制字符进行了正确编码),为了避免控制字符,想到选用不在 80 和 9f 之间的中文字符“好” E5A5BD ,这样手动编码后和数据表存储的都是 C3A5C2A5C2BD,这才验证了这个过程。

页面查询乱码的原因:

前端页面通过调用后台接口查询数据,后台服务连接 MySQL 使用的字符集是 UTF8,所以character_set_results 就是 UTF8。

MySQL 服务器从数据表中查询的字节序列是 C3A5C2A5C2BD,数据表字段的编码也是 UTF8,和 character_set_results 一样,发送给后台服务客户端的字节序列就是 C3A5C2A5C2BD。

后台服务使用 UTF8 对 C3A5C2A5C2BD 解码得到 好,所以前端页面显示的就是 好,而不是预期的中文字符 ”好“。

MySQL 命令行客户端select 查询正常的原因:

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

MySQL 服务器从数据表中查询的字节序列是 C3A5C2A5C2BD,使用 UTF8 解码后是 好。

再使用 character_set_results 的字符集 latin1 进行编码得到 E5A5BD,将字符序列 E5A5BD 发送给 MySQL 命令行客户端。

再发送给本地的图形界面的终端模拟器 MobaXterm,MobaXterm 使用的字符集是 UTF8,使用 UTF8 对 E5A5BD 解码输出中文字符“好”。

解决方法

  • 连接云上 MySQL 时指定字符集为 utf8

    mysql -hx.x.x.x -uusername -ppassword --default-character-set=utf8
    
  • 将 MySQL pod 的字符编码设置为 UTF8, 这样 MySQL 客户端连接服务器时使用的字符集就是 utf8

    export LANG=C.UTF-8
    

    或者直接在如下命令启动的 MySQL 客户端中执行 source 命令,此命令通过 env LANG=C.UTF-8 设置了 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都会设置为 utf8, 就和数据表字段的字符集保持一致,不会出现乱码问题。

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 客户端和服务器通信中的字符集处理

客户端给服务器发送消息过程

  1. 如果 mysql 命令中没有指定 --default-character-set 参数,客户端使用操作系统字符集对消息编码发送给服务器,否则使用 --default-character-set 参数的字符集对消息编码。

  2. 服务器将 character_set_client、 character_set_connection、character_set_results 设置为客户端的字符集。

  3. 收到客户端的消息后,使用 character_set_client 字符集对消息解码。

  4. 再用 character_set_connection 对应的字符集对解码后的消息编码后处理。

    服务器处理消息时要转换为 character_set_connection 字符集进行处理,比较规则只有 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)
    

服务器给客户端发送消息过程

  1. 服务器从数据表中查询字段内容
  2. 将字符内容先使用字段的字符集解码,再使用 character_set_results 字符集编码后发给客户端。
  3. 客户端使用操作系统的字符集解码消息进行展示,这里对于使用本地图形界面的终端模拟器登录远程主机的场景来说,消息还会发送到本地图形界面的终端模拟器,使用终端模拟器的字符集对消息解码再展示出来。