MySQL 字符集与乱码与collation设置的问题？

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, Oceanbase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共1460人左右 1 + 2 + 3 + 4） 3群突破 460已关闭自由申请如需加入请提前说明，新人会进4群（130）,另欢迎 OpenGauss 的技术人员加如入。

问题是这样的，最近有同学问关于MySQL 的字符乱码的问题，说从一个数据库到另一个数据库，配置相同，但是为什么这个数据库的中文就是中文，到另一个数据库就是乱码？

说到这个问题，我们先对MySQL的字符集的配置部分进行一个系统化的分析，这里我们那下面的这些配置来详细的说说字符和字符乱码的问题

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_set_client 这里官方的解释是，客户端发送的语句时所用的字符集,这里的意思就是MySQL 或类MySQL的数据库产品，会认为在接受到客户端的发来的信息后，客户端的编码是 utf8mb4 .

同时MySQL如何处理这些发来的数据要用 character_set_connection 中设置的通过character_set_connection 中设置的字符集来进行数据的处理，最后character_set_results 是最后一个环节，在数据处理完毕后，将数据返回给客户端的字符集编码。

character_set_database

character_set_server

character_set_system 

剩下的三个主要 character_set_database 参数控制了数据库的默认的字符集合，character_set_server 为数据库服务器启动时的默认字符集编码，character_set_system 则是这里唯一的一个变量，他为数据库系统所在的操作系统的字符集这个是无法进行设置的，他与操作系统当前的字符集有关。

那么什么情况会导致输入的数据的字符到了数据库里面是错误的

案例1  

JDBC中使用的指定的字符集不对，导致MySQL接受到的数据与预设客户的字符集不对，导致的乱码。这里需要注意，在JDBC URL中需要使用connector/J 8.020 后的JDBC 才能支持utf8mb4 ,否则只能支持到utf8。

同理当character_set_results的字符集和JDBC的应用字符集设置的不同，也会导致传回应用的字符是乱码的情况。

String url = "jdbc:mysql://127.0.0.1:3306/shop?useUnicode=true&characterEncoding=utf8mb4&autoReconnect=true&allowMultiQueries=true";

所以必须去确认JDBC 设置的字符集和你的MySQL的 character_set_client 和 character_set_result 的字符集是一致的。这里稍微模拟一下，character_set_result 和 character_set_client 的字符集不一致导致的乱码的问题。 

mysql> select * from test;
+----+--------+--------+
| id | name   | title  |
+----+--------+--------+
|  1 | 小李 | 嘩蠧 |
|  2 | 小李 | 嘩蠧 |
+----+--------+--------+
2 rows in set (0.00 sec)
 
mysql> set character_set_results = 'big5';
Query OK, 0 rows affected (0.00 sec)
 
mysql> 
mysql> 
mysql> 
mysql>  insert into test (name,title) values ('小李','嘩蠧');
Query OK, 1 row affected (0.00 sec)
 
mysql> 
mysql> 
mysql> select * from test;
+----+-------+-------+
| id | name  | title |
+----+-------+-------+
|  1 | ?ʡ剠| ???   |
|  2 | ?ʡ剠| ???   |
|  3 | ?ʡ剠| ???   |
+----+-------+-------+
3 rows in set (0.00 sec)

所以在此基础上，需要正确设置这些参数，保证数据库和客户端在字符集设置上是一致的。

说完字符集，下面的说说字符比较规则的问题，字符在进行数据处理中需要进行时间，collation_connection 是MySQL服务器的一个系统变量，用于指定当前连接字符比较的规则，字符比较规则包含字符集和校对规则，决定了在执行字符比较和排序时要使用的规则，我们可以通过show varaibles like '%coll%' 来调取当前与collation有关的值。

collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb4_general_ci
collation_server utf8mb4_general_ci
default_collation_for_utf8mb4 utf8mb4_0900_ai_ci

default_collation_for_utf8mb4 是默认数据库和表创建是的校对规则，另外需要注意的校对规则在数据库建立后，不能随意改变，这将产生与应用程序预想的一些业务处理逻辑相悖的结果。

我们举一个简单的例子

mysql> 
mysql> show variables like 'coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
 
mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)
 
mysql> set collation_connection = utf8_bin;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)
 
mysql>

最后总结MySQL 的字符集和字符排序的问题，

1 弄清楚当前数据库的字符集和字符排序的设置

2 出现乱码去发现当前的数据的字符集和数据库链接和结果的字符集与应用的设置是否匹配一致。

3  排序规则不能轻易变换，轻易变换后会可能会出现业务逻辑错误的问题。