前言
关于编码问题前面一共整理4篇博客，这是终篇。我使用MySQL时经常会遇到乱码问题，尤其是涉及到中文和emoji表情符号时，然而当我查询资料时发现大多数资料几乎雷同，寥寥几句仅贴了几个参数的定义，并没有案例来详细说明，因此我利用几个周末时间整理出这个编码系列博客，希望能对和我同样深受编码困扰的人提供些帮助，当然能力有限，里面很多观点是我根据各种资料的推测，并没有在相关文档中找到确切的描述佐证，可能有理解偏颇之处。
定义
下面一段描述我摘自网上，也是被粘贴复制最多的一部分内容，这里先贴出来，执行如下语句查询数据库编码相关参数：
代码语言：javascript复制show variables like 'character%'查询结果如下：
这三个参数我们可以不用关心，因为他们几乎不会影响到我们（注意是几乎）：
character_set_filesystem：文件系统上的存储格式，默认为binary（二进制），即不做转换；
character_set_system ：系统的存储格式，默认为utf8；
character_sets_dir：可以使用的字符集的文件路径；
剩下的5个就是影响读写乱码的罪魁祸首：
character_set_client：客户端请求数据的字符集；
character_set_connection：从客户端接收到数据，然后传输的字符集；
character_set_database：默认数据库的字符集；如果没有默认数据库，使用character_set_server字段；
character_set_results：结果集的字符集，一般与业务代码的编码相同，否则会导致乱码；
character_set_server：数据库服务器的默认字符集；
参数解读
下面是我画的一条SQL从请求到返回的过程（没装office，手绘的有些粗糙）：
1、客户端请求数据库数据，发送的数据使用character_set_client字符集，客户端包括但不限于bash、jdbc等；
2、MySQL实例收到客户端请求后，将其转换为character_set_connection字符集；
3、MySQL进行内部操作时，将数据字符集转换为内部操作字符集（使用每个数据字段的character set设定值；若没设定则使用对应数据表的default character set设定值；若没设定则使用对应数据库的default character set设定值；若没设定则使用character_set_server设定值）；
4、将操作结果集从内部操作字符集转换为character_set_result；
读以上流程，我们会禁不住产生疑问：返回结果时是内部操作字符集转换为character_set_result，那为什么请求时要先把character_set_client转换为character_set_connection，再把character_set_connection转换为内部操作字符集呢，中间这个转换为character_set_connection的过程是不是有些多余？
我们先看官方的《MySQL中文手册》是怎么写的：服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的校对规则优先级。
只看这段话有些难以理解，我用实验来对比一下，在这之前先同步一点：一个字符集（character set）对应了一个默认的字符排序码规则（collation）,当改变了一个等级的默认编码集时，与它同等级的默认字符排序规则也会变成该字符集对应的字符排序规则。 换种说法：字符串的比较大小是基于字符集的，比如在ASCII字符集中a的编码是97，b的编码是98，因此a<b，假如在某一字符集中a的编码大于b的编码，则a>b;
1）首先新建一个数据库，字符集为utf8，数据库有一张字符集为utf8的表，表结构只有两列：id（int类型）和value（varchar类型），该表仅有一条记录，id=1；value=你好；因为select value from test where id=1的结果就是“你好”；所以理论上select (select value from test where id=1)>’b’等效于select ‘你好’ > ‘b’;
2）将character_set_connection设置为utf8，两条语句得到相同的结果1：
3）将character_set_connection设置为ascii，语句1的的结果为0，语句2的结果为1：
分析：按照《MySQL中文手册》中描述，由于语句2实际上是列value的值与b比较，因此步骤2和步骤3并没有使用character_set_connection所对应的字符排序码规则，而是使用优先级更高的列字符集（建表时没有指定列的字符集，因此这里就会使用表的字符集utf8）所对应的字符排序码规则，两次均使用utf8比对，结果为1符合预期。而语句1是比较文字字符串“你好”和“b”，因此使用的是character_set_connection所对应的字符排序码规则，当设置为ascii时，“你好”的utf8（character_set_client）的16进制编码是0x4F60、0x597D，转为ascii时就变成了4F（O）、60（`）、59(Y)、7D(})，而b的ascii的16进制编码62，根据比较规则取第一个字符比较4F<62，则结果为0，也符合预期。
以上实验仅仅是证实了character_set_connection的生效的场景：1）这个字符集在比较字符串时生效；2）在列值比较时它并没有效果。然而依然没有回答为什么要多这一个过程，我找遍资料也没有找到确切的结论，按照我个人理解：尽管我们大多数情况下执行的SQL语句都是对数据表做操作，但依然有情况我们执行的语句和数据表无关，例如select ‘a’<’b’，在这种情况是使用图一中的任何一个编码均既不符合逻辑，也不符合我们的直觉，因次增加了character_set_connection来处理与操作库表无关的字符串计算。
到这里依然有两个变量没有介绍：
character_set_filesystem：
文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem， 默认binary是不做任何转换的，我们可以做个实验：当character_set_filesystem=utf8、ascii时分别执行select * from test into outfile '/Users/jeanwu/Downloads/你好.txt';结果如下：
注意：该参数仅影响操作系统内创建文件时指定的文件名，而“你好.txt”文件的内容使用都是character_set_results指定的编码。
character_set_system：
默认就是utf8，它是元数据的编码，比如数据库的字段名、数据库名等。是个只读数据不能更改，而且也不要去更改它，因为类似于用户名密码可能会使用各种奇怪的字符，只有utf8能够容纳它们。
小结：
1、character_set_system、character_set_dababase、character_set_server都只表示在数据库内部的保存格式，而不是返回到客户端的编码格式，返回到客户端的结果都会转化为character_set_results指定的字符集之后再返回；
2、character_set_client、 character_set_connection、character_set_database、character_set_results是客户端每次连进来设置的，和服务端没有关系。
乱码
明白了以上流程，我们就可以知道数据库产生乱码的原因可以归结为如下两种：
存取环节的编码不一致
举例说明：
1）插入时使用MySQL默认设置，character_set_client、character_set_connection、character_set_results均为latin1；插入操作的数据将经过latin1–>latin1–>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；
2）查询时的结果将经过utf8–>utf8的字符集转换过程，将保存的6个字节原封不动返回，而产生乱码；
单流程中编码不一致且字符集之间是有损编码转换
先介绍一下有损转换和无损转换的概念：假设字符X是用用编码A表示的，当转换为编码B的时候发现B编码中并没有字符X，那么我们称为这种转换是有损的，因此无损转换的前提是B字符集包含A字符集。
举例说明：
比如客户端(web或shell)是UTF8编码，character_set_client设置为GBK，表结构又是charset=utf8，由于UTF8和GBK不可以无损切换（GBK字符集中汉字个数多于UTF8中的汉字个数），那么毫无疑问的会出现乱码；但是当客户端的字符编码和最终表的字符编码格式不同，但是存和取两次的字符集编码一致，且可以进行无损编码转换时不会产生乱码，这也就是所谓的错进错出：客户端(web或shell)的字符编码和最终表的字符编码格式不同，但是只要保证存和取两次的字符集编码一致就仍然能够获得没有乱码的输出。但是错进错出并不是对于任意两种字符集编码的组合都是有效的，我们假设客户端的编码是X，MySQL表的字符集编码是Y，那么为了能够错进错出，需要满足以下两个条件：MySQL接收请求时从X编码后的二进制流在被Y解码时能够无损；MySQL返回数据时从Y编码后的二进制流在被X解码时能够无损。
错进错出一句话解释：存入的时候将字符串x错误的存储为y，读取时又将y错误的读取为x，负负得正。这种情况下尽管并不影响业务代码，但是数据库存储的数据是错的（尽管我们并不感知）！
出现了乱码怎么办
错误的方法
错误一：ALTER TABLE … CHARSET=XXX
当XXX设置为utf8mb4时看起来是包治乱码的良药，然而这种方法对于已经损坏的数据并没起到丝毫修复作用，当数据经历有损转换后，我们并不能通过修改编码来反推出原来的编码是什么。
错误二：ALTER TABLE … CONVERT TO CHARACTER SET …
官方文档对该命令的解释：用于对一个表的数据进行编码转换，该命令只适用于当前并没有乱码，并且并不能将错进错出纠正为对进对出。
举例说明：假设我们有一张通过错进错出（set names latin1）存入了UTF-8的数据、编码是latin1的表，并打算把表的字符集编码改成UTF-8（同时set names utf8）并且不影响原有数据的正常显示，执行alter table test_latin1 convert to character set utf8后发现依然可以错进错出（set names latin1），然而对进对出就会显示乱码（这是由于错进错出本质上数据存储的内容就是错误的内容，我们通过正确的步骤编码、读取，得到的结果一定是错的）。
正确的方法：
正确一：导出导入法
这个方法比较原始但却有效，操作简单且易于理解，步骤如下：
1）将数据通过错进错出的方法导出到文件； 2）用正确的字符集创建新表； 3）将之前导出的文件重新导入到新表中。
注意：一定要确认导出的文件用文本编辑器在UTF-8编码下查看没有乱码
正确二： Convert to Binary & Convert Back
这种方法是将二进制数据作为中间数据的方法来实现修改编码的，因为MySQL在将有编码意义的数据流转换为无编码意义的二进制数据的时候并不做实际的数据转换，而从二进制数据准换为带编码的数据时又会用目标编码做一次编码转换校验，利用这两个特性就可以实现在MySQL内部模拟了一次“错出”将乱码修正了。
总结：
1、如果想保证任何情况下都不出现乱码，那么我们应该保证数据库编码和set names XXX是一致的；
2、假如由于某种原因不能做到第一条，那么一定要保证character_set_client、character_set_connection、character_set_results是一致的且可以和数据库编码无损转换。
