mysql的字符集问题

问题背景

以前一直没有对mysql的字符集做研究，用起来也没有感觉到有问题，这次出现了一次乱码后好好的对字符集做了测试和研究，这里记录一下

原始的问题是：安装某个第三方的服务，发现页面打开内容是乱码的，一开始怀疑是web端问题，但是检查后无异常，接着就想到是数据方面的问题了，果然在查询数据库数据后，发现内容都是乱码，又查看导入mysql的sql原始文件，内容是正常的，而且sql文件中表字符集是 utf8，且数据库肯定是utf8，这到底是什么问题呢？

排查过程

1、在服务器使用mysql本地命令进入库内查询表，显示内容正常，怀疑是客户端字符集与服务端不一致导致，了解到 show variables like "%char%" 可以查询客户端使用的字符集，查询出来的内容如下:

 mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

以上结果比较奇怪的是除了操作系统显示的 utf8，其他的字符集都是 latin1，从本地查询内容显示正常来反推，可以知道 client、connection、results、server 都是一致，那么到底是哪个参数影响呢？更重要的是建表语句明明是 utf8，那么这里使用 latin1，为什么又是正常的？

基于这几个问题和查询显示的结果，又进行了一些思考和测试，如下：

我在服务器使用默认的mysql客户端登录查询没有问题，但是使用其他软件查询就存在问题，所以怀疑也跟客户端有一些关系（字符集）
所以我分别测试了客户端使用 --default-character-set 来指定 utf8 或者 latin1，或者登录后使用 set names latin1/utf8 来设置字符集，发现 utf8 的时候则出现乱码的问题，latin1 的时候则查询正常
那么这个问题原因可以被定位了，当前客户端字符集与当前数据不一致导致的，而不是当前客户端字符集与server的字符集不一致，所以当前的结论是当初导入数据的时候客户端使用了latin1的字符集导致，而跟表使用什么字符集关系不大了，重要的是数据是 latin1 的编码
对于mysql来说，库、表、数据都可以分别使用不同的字符集编码
还有一个问题是，为什么服务器本地登录的mysql字符集是 latin1？通过 mysql -V 看到当前是mysql8，于是我分别指定了本机的mysql客户端5.7和mysql8登录查看默认的字符集设置，发现了如下的情况

mysql5.7客户端登录

mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

mysql8客户端登录

mysql> show variables like "%char%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

可以看到mysql5.7客户端登录的时候是符合我们预期的，并且符合日常的使用规范，而mysql8登录的时候则变成了latin1，想到这里的配置与my.cnf有关系，于是我在多个配置文件 ~/.my.cnf /etc/my.cnf 中都添加配置

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

但mysql8登录后仍然是 latin1，完全没有效果

又对比测试了mysql8客户端登录另一个mysql8服务端，可以发现下面是正常的

mysql> show variables like "%char%";
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| character_set_client     | utf8mb4                              |
| character_set_connection | utf8mb4                              |
| character_set_database   | utf8mb4                              |
| character_set_filesystem | binary                               |
| character_set_results    | utf8mb4                              |
| character_set_server     | utf8mb4                              |
| character_set_system     | utf8mb3                              |
| character_sets_dir       | /opt/software/8.0.30/share/charsets/ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)

那么问题可以进一步定位：当初使用了 mysql8 的客户端登录了 mysql5.7 的服务端导致默认的字符集配置无效（mysql8客户端默认为utf8mb4），

但使用时又变成了latin1，会是原因是什么呢？(为什么默认的utf8mb4没有生效，是使用的latin1？)

查找资料后，有了如下发现:

https://github.com/PyMySQL/mysqlclient/issues/504

该文章与我遇到的问题一模一样，也是mysql8客户端连接mysql5.7服务端，出现的字符集问题，大意是mysql8连接mysql5.7的时候握手协商字符集，发现mysql5.7不支持（mysql5.7不知道utf8mb4_0900_ai_ci，它的版本是utf8mb4_general_ci），于是使用了服务端的character_set_server默认字符集，而我们mysql5.7的服务端是latin1，配置文件中没有设置为utf8，所以是默认的

至此，我们已经知道了所有导致的原因，进行如下总结：

mysql5.7服务端没有设置字符集，那么 character_set_server 默认就会使用latin1
mysql8客户端协商登录的时候，因为两个大版本代号不一致，导致无法正常协商设置客户端的字符集为utf8mb4，从而退回使用服务端默认的字符集，也就是latin1
接着此客户端（当前登录的字符集是latin1）导入了sql文本数据，那么数据字符集变成latin1，与表和库的字符集都没有关系了
而后续mysql8使用正常，是因为数据是latin1，而它因为握手协商问题也是latin1，所以显示正常。如果其他客户端使用的utf8则查询显示乱码

关于mysql5.7的字符集

使用mysql5.7客户端登录mysql5.7服务端的时候，发现字符集正确被设置为utf8（没有在任何配置文件中设置客户端字符集为utf8，服务端也为设置，登录后看到为latin1），而mysql8开始客户端默认的字符集才为utf8mb4，所以这里也算一个小疑问，通过排查检索后发现，服务器的 LANG 也会影响字符集设置，经测试mysql5.7客户端登录mysql5.7服务端被正确设置是因为 LANG 是 en_US.utf8 或者 zh_CN.utf8，如果去掉utf8，则登录后变为latin1

至此，此次问题的根本原因已经清楚，所以需要恢复数据正常，应该做如下操作：解决办法：当前数据使用latin1的客户端导出，保证文本内容都正确，再使用 utf8的客户端导入

mysql字符集的后续注意事项：