乱码问题让人头疼，彻底解决 MySQL 中的乱码问题

字符集转换概述

我们有必要说明一下，字符其实是面向人类的一个概念，计算机可并不关心字符是什么，它只关心这个字符对应的字节编码是什么。对于一个字节序列，计算机怎么知道它是使用什么字符集编码的呢？计算机不知道，所以其实在计算机中表示一个字符串时，都需要附带上它对应的字符集是什么，就像这样（以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的一个可执行文件），如图所示：

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

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

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

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

客户端发送请求

• 对于UNIX操作系统来说
  
  在我们使用某个输入法软件向黑框框中输入字符时，该字符采用的编码字符集其实是操作系统当前使用的字符集。比方说当前
  LC_ALL
  环境变量的值为
  zh_CN.UTF-8
  ，那么意味着黑框框中的字符其实是使用utf8字符集进行编码。稍后MySQL客户端程序将调用操作系统提供的read函数从黑框框中读取数据（其实就是所谓的从标准输入流中读取数据），所读取的数据其实就是采用utf8字符集进行编码的字节序列，稍后将该字节序列作为请求内容发送到服务器。
  
  这样其实会产生一个问题，如果客户端的默认字符集和操作系统当前正在使用的字符集不同，那么将产生比较尴尬的结果。比方说我们在启动客户端是携带了
  --default-character-set=gbk
  的启动参数，那么客户端的默认字符集将会被设置成gbk，而如果操作系统此时采用的字符集是utf8。比方说我们的语句中包含汉字
  '我'
  ，那么客户端调用
  read
  函数读到的字节序列其实是
  0xE68891
  ，从而将
  0xE68891
  发送到服务器，而服务器认为客户端发送过来的请求都是采用gbk进行编码的，这样就会产生问题（当然，这仅仅是发生乱码问题的前奏，并不意味着产生乱码，乱码只有在最后一步，也就是客户端应用程序将服务器返回的数据写到黑框框里时才会发生）。
  
• 对于Windows操作系统来说
  
  在Windows操作系统中，从黑框框中读取数据调用的是Windows提供的
  ReadConsoleW
  函数。在该函数执行后，MySQL客户端会得到一个宽字符数组（其实就是一组16位的UNICODE），然后客户端需要把该宽字符数组再次转换成客户端使用的默认字符集编码的字节序列，然后才将该字节序列作为请求的内容发送到服务器。
  
  这样在UNIX操作系统中可能产生的问题，在Windows系统中却可以避免。比方说我们在启动客户端是携带了
  --default-character-set=gbk
  的启动参数，那么客户端的默认字符集将会被设置成gbk，假如此时操作系统采用的字符集是utf8。比方说我们的语句中包含汉字
  '我'
  ，那么客户端调用
  ReadConsoleW
  函数先读到一个代表着
  我
  字的宽字符数组，之后又将其转换为客户端的默认字符集，也就是gbk字符集编码的数据
  0xCED2
  ，然后将
  0xCED2
  发送到服务器。此时服务器也认为客户端发送过来的请求就是采用gbk进行编码的，这样就完全正确了～
  

服务器接收请求

  
  
  

   
   
   SET character_set_client=gbk;

  
  
  

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 WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1300
Message: Invalid ascii character string: '\xE6\x88\x91'
1 row in set (0.00 sec)

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

服务器处理请求

  
  
  

   
   
   mysql> SELECT 'a' = 'A';

  
  
  

• 考虑一：这些字符串是采用什么字符集进行编码的呢？
  
• 考虑二：在我们确定了编码这些字符串的字符集之后，也就意味着每个字符串都会映射到一个字节序列，那么我们怎么比较这些字节序列呢，是直接比较它们二进制的大小，还是有别的什么比较方式？比方说
  'a'
  和
  'A'
  在utf8字符集下的编码分别为
  0x61
  和
  0x41
  ，那么
  'a' = 'A'
  是应该直接比较
  0x61
  和
  0x41
  的大小呢，还是将
  0x61
  减去32之后再比较大小呢？其实这两种比较方式都可以，每一种比较方式我们都称作一种
  比较规则
  （英文名：
  collation
  ）。
  

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)

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)

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)

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)

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

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)

  
  
  

   
   
   SET NAMES 'charset_name'

  
  
  

  
  
  

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

客户端接收到响应

• 对于UNIX操作系统来说，MySQL客户端向黑框框中写入数据使用的是操作系统提供的
  fputs
  、
  putc
  或者
  fwrite
  函数，这些函数基本上相当于直接就把接收到的字节序列写到了黑框框中（请注意我们用词：
  '基本上相当于'
  ，其实内部还会做一些工作，但是我们这里就不想再关注这些细节了）。此时如果该字节序列实际的字符集和黑框框展示字符所使用的字符集不一致的话，就会发生所谓的乱码（大家注意，这个时候和操作系统当前使用的字符集没啥关系）。
  
  比方说我们在启动MySQL客户端的时候使用了
  --default-character-set=gbk
  的启动参数，那么服务器的
  character_set_result
  变量就是gbk。然后再执行
  SELECT * FROM t
  语句，那么服务器就会将字符
  '我'
  的gbk编码，也就是
  0xCDE2
  发送到客户端，客户端直接把这个字节序列写到黑框框中，如果黑框框此时采用utf8字符集展示字符，那自然就会发生乱码。
  
• 对于Windows操作系统来说，MySQL客户端向黑框框中写入数据使用的是操作系统提供的
  WriteConsoleW
  函数，该函数接收一个宽字符数组，所以MySQL客户端调用它的时候需要显式地将它从服务器收到的字节序列按照客户端默认的字符集转换成一个宽字符数组。正因为这一步骤的存在，所以可以避免上边提到的一个问题。
  
  比方说我们在启动MySQL客户端的时候使用了
  --default-character-set=gbk
  的启动参数，那么服务器的
  character_set_result
  变量就是gbk。然后再执行
  SELECT * FROM t
  语句，那么服务器就会将字符
  '我'
  的gbk编码，也就是
  0xCDE2
  发送到客户端，客户端将这个字节序列先从客户端默认字符集，也就是gbk的编码转换成一个宽字符数组，然后再调用
  WriteConsoleW
  函数写到黑框框，黑框框自然可以把它显示出来。
  

乱码问题应该如何分析

• 我使用的是什么操作系统
  
• 对于UNIX系统用户来说，要搞清楚我使用的黑框框到底是使用什么字符集展示字符，就像是
  iTerm2
  中的
  character encoding
  属性：
  
  
  
  
  
  同样还要搞清楚操作系统当前使用什么字符集，运行
  locale
  命令查看：
  

       
       
       
  
        
        
        王大爷喊你输入呢，跟这儿>locale
  LANG=""
  LC_COLLATE="zh_CN.UTF-8"
  LC_CTYPE="zh_CN.UTF-8"
  LC_MESSAGES="zh_CN.UTF-8"
  LC_MONETARY="zh_CN.UTF-8"
  LC_NUMERIC="zh_CN.UTF-8"
  LC_TIME="zh_CN.UTF-8"
  LC_ALL="zh_CN.UTF-8"
  王大爷喊你输入呢，跟这儿>
       
       
       

  没有什么特别极端的特殊需求的话，一定要保证上述两个字符集是相同的，否则可能连汉字都输入不进去！
  
• 对于Windows用户来说
  
  搞清楚自己使用的黑框框的代码页是什么，也就是操作系统当前使用的字符集是什么。
  
• 搞清楚客户端的默认字符集是什么
  
  启动MySQL客户端的时候有没有携带
  --default-character-set
  参数，如果携带了，那么客户端默认字符集就以该参数指定的值为准。否则分析自己操作系统当前使用的字符集是什么。
  
• 搞清楚客户端发送请求时是以什么字符集编码请求的
  
• 对于UNIX系统来说，我们可以认为请求就是采用操作系统当前使用的字符集进行编码的。
  
• 对于Windows系统来说，我们可以认为请求就是采用客户端默认字符集进行编码的。
  
• 通过执行
  SHOW VARIABLES LIKE 'character%'
  命令搞清楚：
  
• character_set_client
  ：服务器是怎样认为客户端发送过来的请求是采用何种字符集编码的
  
• character_set_connection
  ：服务器在运行过程中会采用何种字符集编码请求中的字符
  
• character_set_result
  ：服务器会将响应使用何种字符集编码后再发送给客户端的
  
• 客户端收到响应之后：
  
  对于服务器发送过来的字节序列来说：
  
• 在UNIX操作系统上，可以认为会把该字节序列直接写到黑框框里。此时应该搞清楚我们的黑框框到底是采用何种字符集展示数据。
  
• 在Windows操作系统上，该字节序列会被认为是由客户端字符集编码的数据，然后再转换成宽字符数组写入到黑框框中。