<返回更多

彻底解决MySQL中的乱码问题

2020-04-11    
加入收藏

字符集转换概述

我们有必要说明一下， 字符 其实是面向人类的一个概念，计算机可并不关心字符是什么，它只关心这个字符对应的字节编码是什么。对于一个字节序列，计算机怎么知道它是使用什么字符集编码的呢？计算机不知道，所以其实在计算机中表示一个字符串时，都需要附带上它对应的字符集是什么，就像这样（以C++语言为例）：

class String {
    byte* content;
    CHARSET_INFO* charset;
}

比方说我们现在有一个以 utf8 字符集编码的汉字 '我' ，那么意味着计算机中不仅仅要存储 '我' 的utf8编码 0xE68891 ，还需要存储它是使用什么字符集编码的信息，就像这样：

{
    content: 0xE68891;
    charset: utf8;
}

计算机内部包含将一种字符集转换成另一种字符集的函数库，也就是某个字符在某种字符集下的编码可以很顺利的转换为另一种字符集的编码，我们将这个过程称之为 字符集转换 。比方说我们可以将上述采用utf8字符集编码的字符'我'，转换成gbk字符集编码的形式，就变成了这样：

{
    content: 0xCED2;
    charset: gbk;
}

小贴士：我们上边所说的'编码'可以当作动词，也可以当作名词来理解。当作动词的话意味着将一个字符映射到一个字节序列的过程，当作名词的话意味着一个字符对应的字节序列。大家根据上下文理解'编码'的含义。

MySQL客户端和服务器是怎么通信的

MySQL客户端发送给服务器的请求以及服务器发送给客户端的响应其实都是遵从一定格式的，我们把它们通信过程中事先规定好的数据格式称之为MySQL通信协议，这个协议是公开的，我们可以简单的使用wireshark等截包软件十分方便的分析这个通信协议。在了解了这个通信协议之后，我们甚至可以动手制作自己的客户端软件。市面上的MySQL客户端软件多种多样，我们并不想各个都分析一下，现在只选取在MySQL安装目录的 bin 目录下自带的 mysql 程序（此处的 mysql 程序指的是名字叫做 mysql 的一个可执行文件），如图所示：

我们在计算机的黑框框中执行该可执行文件，就相当于启动了一个客户端，就像这样：

彻底解决MySQL中的乱码问题

 

小贴士：我们这里的'黑框框'指的是windows操作系统中的cmd.exe或者UNIX系统中的Shell。

我们通常是按照下述步骤使用MySQL的：

  1. 启动客户端并连接到服务器
  2. 客户端发送请求。
  3. 服务器接收到请求
  4. 服务器处理请求
  5. 服务器处理请求完毕生成对该客户端的响应
  6. 客户端接收到响应

下边我们就详细分析一下每个步骤中都影响到了哪些字符集。

启动客户端并连接到服务器过程

每个MySQL客户端都维护者一个客户端默认字符集，这个默认字符集按照下边的套路进行取值：

在确认了MySQL客户端默认字符集之后，客户端就会向服务器发起登陆请求，传输一些诸如用户名、密码等信息，在这个请求里就会包含客户端使用的默认字符集是什么的信息，服务器收到后就明白了稍后客户端即将发送过来的请求是采用什么字符集编码的，自己生成的响应应该以什么字符集编码了（剧透一下：其实服务器在明白了客户端使用的默认字符集之后，就会将 character_set_client 、 character_set_connection 以及 character_set_result 这几个系统变量均设置为该值）。

客户端发送请求

登陆成功之后，我们就可以使用键盘在黑框框中键入我们想要输入的MySQL语句，输入完了之后就可以点击回车键将该语句当作请求发送到服务器，可是客户端发送的语句（本质是个字符串）到底是采用什么字符集编码的呢？这其实涉及到应用程序和操作系统之间的交互，我们的MySQL客户端程序其实是一个应用程序，它从黑框框中读取数据其实是要调用操作系统提供的读取接口。在不同的操作系统中，调用的读取接口其实是不同的，我们还得分情况讨论一下：

服务器接收请求

服务器接收到到的请求本质上就是一个字节序列，服务器将其看作是采用系统变量 character_set_client 代表的字符集进行编码的字节序列。 character_set_client 是一个SESSION级别的系统变量，也就是说每个客户端和服务器建立连接后，服务器都会为该客户端维护一个单独的 character_set_client 变量，每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的 character_set_client 。

我们可以使用SET命令单独修改 character_set_client 对应的值，就像这样：

SET character_set_client=gbk;

需要注意的是， character_set_client 对应的字符集一定要包含请求中的字符，比方说我们把 character_set_client 设置成 ascii ，而请求中发送了一个汉字 '我' ，将会发生这样的事情：

mysql> SET character_set_client=ascii;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+------------------------------------------------------+
| Variable_name            | Value                                                |
+--------------------------+------------------------------------------------------+
| character_set_client     | ascii                                                |
| 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/local/Cellar/mysql/5.7.21/share/mysql/charsets/ |
+--------------------------+------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SELECT '我';
+-----+
| ??? |
+-----+
| ??? |
+-----+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGSG
*************************** 1. row ***************************
  Level: Warning
   Code: 1300
Message: Invalid ascii character string: '\xE6\x88\x91'
1 row in set (0.00 sec)

如图所示，最后提示了 'E6、88、91' 并不是正确的ascii字符。

小贴士：可以将character_set_client设置为latin1，看看还会不会报告WARNINGS，以及为什么～

服务器处理请求

服务器在处理请求时会将请求中的字符再次转换为一种特定的字符集，该字符集由系统变量 character_set_connection 表示，该系统变量也是SESSION级别的。每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的 character_set_connection 。

不过我们之后可以通过SET命令单独修改这个 character_set_connection 系统变量。比方说客户端发送给服务器的请求中包含字节序列 0xE68891 ，然后服务器针对该客户端的系统变量 character_set_client 为 utf8 ，那么此时服务器就知道该字节序列其实是代表汉字 '我' ，如果此时服务器针对该客户端的系统变量 character_set_connection 为gbk，那么在计算机内部还需要将该字符转换为采用gbk字符集编码的形式，也就是 0xCED2 。

有同学可能会想这一步有点儿像脱了裤子放屁的意思，但是大家请考虑下边这个查询语句：

mysql> SELECT 'a' = 'A';

请问大家这个查询语句的返回结果应该是TRUE还是FALSE？其实结果是不确定。这是因为我们并不知道比较两个字符串的大小到底比的是什么！我们应该从两个方面考虑：

MySQL 中支持若干种字符集，我们可以使用 SHOW CHARSET 命令查看，如下图所示（太多了，只展示几种，具体自己运行一下该命令）：

mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.04 sec)

其中每一种字符集又对应着若干种比较规则，我们以utf8字符集为例（太多了，也只展示几个）：

mysql> SHOW COLLATION WHERE Charset='utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)

其中 utf8_general_ci 是utf8字符集默认的比较规则，在这种比较规则下是不区分大小写的，不过 utf8_bin 这种比较规则就是区分大小写的。

在我们将请求中的字节序列转换为 character_set_connection 对应的字符集编码的字节序列后，也要配套一个对应的比较规则，这个比较规则就由 collation_connection 系统变量来指定。我们现在通过SET命令来修改一下和 collation_connection 的值分别设置为 utf8 和 utf8_general_ci ，然后比较一下 'a' 和 'A' ：

mysql> SET character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SET collation_connection=utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

可以看到在这种情况下这两个字符串就是相等的。

我们现在通过SET命令来修改一下和 collation_connection 的值分别设置为 utf8 和 utf8_bin ，然后比较一下 'a' 和 'A' ：

mysql> SET character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> SET collation_connection=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

可以看到在这种情况下这两个字符串就是不相等的。

当然，如果我们并不需要单独指定将请求中的字符串采用何种字符集以及比较规则的话，并不用太关心 character_set_connection 和 collation_connection 设置成啥，不过需要注意一点，就是 character_set_connection 对应的字符集必须包含请求中的字符。

服务器处理请求完毕生成对该客户端的响应

为了故事的顺利发展，我们先创建一个表：

CREATE TABLE t (
    c VARCHAR(100)
) ENGINE=INNODB CHARSET=utf8;

然后向这个表插入一条记录：

INSERT INTO t VALUE('我');

现在这个表中的数据就如下所示：

mysql> SELECT * FROM t;
+------+
| c    |
+------+
| 我   |
+------+
1 row in set (0.00 sec)

我们可以看到该表中的字段其实是使用 utf8 字符集编码的，所以底层存放格式是： 0xE68891 ，将它读出后需要发送到客户端，是不是直接将 0xE68891 发送到客户端呢？这可不一定，这个取决于 character_set_result 系统变量的值，该系统变量也是一个SESSION级别的变量。服务器会将该响应转换为 character_set_result 系统变量对应的字符集编码后的字节序列发送给客户端。每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的 character_set_result 。

我们也可以使用SET命令来设置 character_set_result 的值。不过也需要注意， character_set_result 对应的字符集应该包含响应中的字符。

这里再强调一遍， character_set_client 、 character_set_connection 和 character_set_result 这三个系统变量是服务器的系统变量，每个客户端在与服务器建立连接后，服务器都会为这个连接维护这三个变量，如图所示（我们假设连接1的这三个变量均为 utf8 ，连接1的这三个变量均为 gbk ，连接1的这三个变量均为 ascii ，）：

彻底解决MySQL中的乱码问题

 

一般情况下 character_set_client 、 character_set_connection 和 character_set_result 这三个系统变量应该和客户端的默认字符集相同， SET names 命令可以一次性修改这三个系统变量：

SET NAMES 'charset_name'

该语句和下边三个语句等效：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

不过这里需要大家特别注意， SET names 语句并不会改变客户端的默认字符集！

客户端接收到响应

客户端收到的响应其实仍然是一个字节序列。客户端是如何将这个字节序列写到黑框框中的呢，这又涉及到应用程序和操作系统之间的一次交互。

乱码问题应该如何分析

好了，介绍了各个步骤中涉及到的各种字符集，大家估计也看的眼花缭乱了，下边总结一下我们遇到乱码的时候应该如何分析，而不是胡子眉毛一把抓，随便百度一篇文章，然后修改某个参数，运气好修改了之后改对了，运气不好改了一天也改不好。知其然也要知其所以然，在学习了本篇文章后，大家一定要有节奏的去分析乱码问题：

声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号