FaceBook专家：10分钟彻底解决MySQL乱码问题?

卢钧轶 2016-01-08 09:01:44

本文由【DBA+社群】原创专家卢钧轶为大家较为详细的介绍字符集，字符编码, MySQL乱码的成因和具体的解决方案。

Part 1 字符集和字符编码

· 什么是字符集

· 什么是字符编码

· UTF-8和Unicode的关系

· UTF-8编码简介

· 为什么会出现乱码

· 如何识别乱码的本来想要表达的文字

· 常见问题处理之Emoji

Part 2 MySQL乱码

· MySQL出现乱码的原因

· 如何避免乱码

Part 1

字符集和字符编码

这部分将简述字符集，字符编码的概念。以及在遭遇乱码时的一些常用诊断技巧。

字符集和编码无疑是IT菜鸟甚至是各种大神的头痛问题。当遇到纷繁复杂的字符集，各种火星文和乱码时，问题的定位往往变得非常困难。我们将会从原理方面对字符集和编码做个简单的科普介绍，同时也会介绍一些通用的乱码故障定位的方法以方便读者以后能够更从容的定位相关问题。在正式介绍之前，先做个小申明：如果你希望非常精确的理解各个名词的解释，那么可以查阅wikipedia。本文是作者通过自己理解消化后并转化成易懂浅显的表述后的介绍。

什么是字符集

在介绍字符集之前，我们先了解下为什么要有字符集。我们在计算机屏幕上看到的是实体化的文字，而在计算机存储介质中存放的实际是二进制的比特流。那么在这两者之间的转换规则就需要一个统一的标准，否则把我们的U盘插到老板的电脑上，文档就乱码了；小伙伴QQ上传过来的文件，在我们本地打开又乱码了。于是为了实现转换标准，各种字符集标准就出现了。简单的说字符集就规定了某个文字对应的二进制数字存放方式（编码）和某串二进制数值代表了哪个文字（解码）的转换关系。

那么为什么会有那么多字符集标准呢？这个问题实际非常容易回答。问问自己为什么我们的插头拿到英国就不能用了呢？为什么显示器同时有DVI，VGA，HDMI，DP这么多接口呢？很多规范和标准在最初制定时并不会意识到这将会是以后全球普适的准则，或者处于组织本身利益就想从本质上区别于现有标准。于是，就产生了那么多具有相同效果但又不相互兼容的标准了。

说了那么多我们来看一个实际例子，下面就是屌这个字在各种编码下的十六进制和二进制编码结果，怎么样有没有一种很屌的感觉？

字符集	16进制编码	对应的二进制数据
UTF-8	0xE5B18C	1110 0101 1011 0001 1000 1100
UTF-16	0x5C4C	1011 1000 1001 1000
GBK	0x8CC5	1000 1100 1100 0101

什么是字符编码

字符集只是一个规则集合的名字，对应到真实生活中，字符集就是对某种语言的称呼。例如：英语，汉语，日语。对于一个字符集来说要正确编码转码一个字符需要三个关键元素：字库表（character repertoire）、编码字符集（coded character set）、字符编码（character encoding form）。其中字库表是一个相当于所有可读或者可显示字符的数据库，字库表决定了整个字符集能够展现表示的所有字符的范围。编码字符集，即用一个编码值code point来表示一个字符在字库中的位置。字符编码，将编码字符集和实际存储数值之间的转换关系。一般来说都会直接将code point的值作为编码后的值直接存储。例如在ASCII中A在表中排第65位，而编码后A的数值是0100 0001也即十进制的65的二进制转换结果。

看到这里，可能很多读者都会有和我当初一样的疑问：字库表和编码字符集看来是必不可少的，那既然字库表中的每一个字符都有一个自己的序号，直接把序号作为存储内容就好了。为什么还要多此一举通过字符编码把序号转换成另外一种存储格式呢？其实原因也比较容易理解：统一字库表的目的是为了能够涵盖世界上所有的字符，但实际使用过程中会发现真正用的上的字符相对整个字库表来说比例非常低。例如中文地区的程序几乎不会需要日语字符，而一些英语国家甚至简单的ASCII字库表就能满足基本需求。而如果把每个字符都用字库表中的序号来存储的话，每个字符就需要3个字节（这里以Unicode字库为例），这样对于原本用仅占一个字符的ASCII编码的英语地区国家显然是一个额外成本（存储体积是原来的三倍）。算的直接一些，同样一块硬盘，用ASCII可以存1500篇文章，而用3字节Unicode序号存储只能存500篇。于是就出现了UTF-8这样的变长编码。在UTF-8编码中原本只需要一个字节的ASCII字符，仍然只占一个字节。而像中文及日语这样的复杂字符就需要2个到3个字节来存储。

UTF-8和Unicode的关系

看完上面两个概念解释，那么解释UTF-8和Unicode的关系就比较简单了。Unicode就是上文中提到的编码字符集，而UTF-8就是字符编码，即Unicode规则字库的一种实现形式。随着互联网的发展，对同一字库集的要求越来越迫切，Unicode标准也就自然而然的出现。它几乎涵盖了各个国家语言可能出现的符号和文字，并将为他们编号。详见https://en.wikipedia.org/wiki/Unicode。Unicode的编号从0000开始一直到10FFFF共分为16个Plane，每个Plane中有65536个字符。而UTF-8则只实现了第一个Plane，可见UTF-8虽然是一个当今接受度最广的字符集编码，但是它并没有涵盖整个Unicode的字库，这也造成了它在某些场景下对于特殊字符的处理困难（下文会有提到）。

UTF-8编码简介

为了更好的理解后面的实际应用，我们这里简单的介绍下UTF-8的编码实现方法。即UTF-8的物理存储和Unicode序号的转换关系。

UTF-8编码为变长编码。最小编码单位（code unit）为一个字节。一个字节的前1-3个bit为描述性部分，后面为实际序号部分。

如果一个字节的第一位为0，那么代表当前字符为单字节字符，占用一个字节的空间。0之后的所有部分（7个bit）代表在Unicode中的序号。
如果一个字节以110开头，那么代表当前字符为双字节字符，占用2个字节的空间。110之后的所有部分（5个bit）加上后一个字节的除10外的部分（6个bit）代表在Unicode中的序号。且第二个字节以10开头
如果一个字节以1110开头，那么代表当前字符为三字节字符，占用2个字节的空间。110之后的所有部分（5个bit）加上后两个字节的除10外的部分（12个bit）代表在Unicode中的序号。且第二、第三个字节以10开头
如果一个字节以10开头，那么代表当前字节为多字节字符的第二个字节。10之后的所有部分（6个bit）和之前的部分一同组成在Unicode中的序号。

具体每个字节的特征可见下表，其中x代表序号部分，把各个字节中的所有x部分拼接在一起就组成了在Unicode字库中的序号

我们分别看三个从一个字节到三个字节的UTF-8编码例子：

细心的读者不难从以上的简单介绍中得出以下规律：

· 3个字节的UTF-8十六进制编码一定是以E开头的

· 2个字节的UTF-8十六进制编码一定是以C或D开头的

· 1个字节的UTF-8十六进制编码一定是以比8小的数字开头的

为什么会出现乱码

先科普下乱码的英文native说法是mojibake。

简单的说乱码的出现是因为：编码和解码时用了不同或者不兼容的字符集。对应到真实生活中，就好比是一个英国人为了表示祝福在纸上写了bless（编码过程）。而一个法国人拿到了这张纸，由于在法语中bless表示受伤的意思，所以认为他想表达的是受伤（解码过程）。这个就是一个现实生活中的乱码情况。在计算机科学中一样，一个用UTF-8编码后的字符，用GBK去解码。由于两个字符集的字库表不一样，同一个汉字在两个字符表的位置也不同，最终就会出现乱码。

我们来看一个例子：假设我们用UTF-8编码存储很屌两个字，会有如下转换：

于是我们得到了E5BE88E5B18C这么一串数值。而显示时我们用GBK解码进行展示，通过查表我们获得以下信息：

解码后我们就得到了寰堝睂这么一个错误的结果，更要命的是连字符个数都变了。

如何识别乱码的本来想要表达的文字

要从乱码字符中反解出原来的正确文字需要对各个字符集编码规则有较为深刻的掌握。但是原理很简单，这里用最常见的UTF-8被错误用GBK展示时的乱码为例，来说明具体反解和识别过程。

第1步编码

假设我们在页面上看到寰堝睂这样的乱码，而又得知我们的浏览器当前使用GBK编码。那么第一步我们就能先通过GBK把乱码编码成二进制表达式。当然查表编码效率很低，我们也可以用以下SQL语句直接通过MySQL客户端来做编码工作：

第2步识别

现在我们得到了解码后的二进制字符串E5BE88E5B18C。然后我们将它按字节拆开。

然后套用之前UTF-8编码介绍章节中总结出的规律，就不难发现这6个字节的数据符合UTF-8编码规则。如果整个数据流都符合这个规则的话，我们就能大胆假设乱码之前的编码字符集是UTF-8

第3步解码

然后我们就能拿着E5BE88E5B18C用UTF-8解码，查看乱码前的文字了。当然我们可以不查表直接通过SQL获得结果：

常见问题处理之Emoji

所谓Emoji就是一种在Unicode位于\u1F601-\u1F64F区段的字符。这个显然超过了目前常用的UTF-8字符集的编码范围\u0000-\uFFFF。Emoji表情随着IOS的普及和微信的支持越来越常见。下面就是几个常见的Emoji:

那么Emoji字符表情会对我们平时的开发运维带来什么影响呢？最常见的问题就在于将他存入MySQL数据库的时候。一般来说MySQL数据库的默认字符集都会配置成UTF-8（三字节），而utf8mb4在5.5以后才被支持，也很少会有DBA主动将系统默认字符集改成utf8mb4。那么问题就来了，当我们把一个需要4字节UTF-8编码才能表示的字符存入数据库的时候就会报错：ERROR 1366: Incorrect string value: '\xF0\x9D\x8C\x86' for column 。如果认真阅读了上面的解释，那么这个报错也就不难看懂了。我们试图将一串Bytes插入到一列中，而这串Bytes的第一个字节是\xF0意味着这是一个四字节的UTF-8编码。但是当MySQL表和列字符集配置为UTF-8的时候是无法存储这样的字符的，所以报了错。

那么遇到这种情况我们如何解决呢？有两种方式：升级MySQL到5.6或更高版本，并且将表字符集切换至utf8mb4。第二种方法就是在把内容存入到数据库之前做一次过滤，将Emoji字符替换成一段特殊的文字编码，然后再存入数据库中。之后从数据库获取或者前端展示时再将这段特殊文字编码转换成Emoji显示。第二种方法我们假设用-*-1F601-*-来替代4字节的Emoji，那么具体实现python代码可以参见Stackoverflow上的回答（http://stackoverflow.com/questions/3220031/how-to-filter-or-replace-unicode-characters-that-would-take-more-than-3-bytes）

Part 2

MySQL乱码

本部分将详细介绍MySQL乱码的成因和具体的解决方案

MySQL出现乱码的原因

要了解为什么会出现乱码，我们就先要理解：从客户端发起请求，到MySQL存储数据，再到下次从表取回客户端的过程中，哪些环节会有编码/解码的行为。为了更好的解释这个过程，博主制作了两张流程图，分别对应存入和取出两个阶段。

存入MySQL经历的编码转换过程

上图中有3次编码/解码的过程（红色箭头）。三个红色箭头分别对应：客户端编码，MySQL Server解码，Client编码向表编码的转换。其中Terminal可以是一个Bash，一个web页面又或者是一个APP。本文中我们假定Bash是我们的Terminal，即用户端的输入和展示界面。图中每一个框格对应的行为如下：

在terminal中使用输入法输入
terminal根据字符编码转换成二进制流
二进制流通过MySQL客户端传输到MySQL Server
Server通过character-set-client解码
判断character-set-client和目标表的charset是否一致
如果不一致则进行一次从client-charset到table-charset的一次字符编码转换
将转换后的字符编码二进制流存入文件中

从MySQL表中取出数据经历的编码转换过程

上图有3次编码/解码的过程（红色箭头）。上图中三个红色箭头分别对应：客户端解码展示，MySQL Server根据character-set-client编码，表编码向character-set-client编码的转换。

从文件读出二进制数据流
用表字符集编码进行解码
将数据转换为character-set-client的编码
使用character-set-client编码为二进制流
Server通过网络传输到远端client
client通过bash配置的字符编码展示查询结果

造成MySQL乱码的原因

1. 存入和取出时对应环节的编码不一致

这个会造成乱码是显而易见的。我们把存入阶段的三次编解码使用的字符集编号为C1,C2,C3（图一从左到右）；取出时的三个字符集依次编号为C1’,C2’,C3’（从左到右）。那么存入的时候bash C1用的是UTF-8编码，取出的时候,C1'我们却使用了windows终端（默认是GBK编码），那么结果几乎一定是乱码。又或者存入MySQL的时候set names utf8(C2)，而取出的时候却使用了set names gbk(C2')，那么结果也必然是乱码。

2. 单个流程中三步的编码不一致

即上面任意一幅图中的同方向的三步中，只要两步或者两部以上的编码有不一致就有可能出现编解码错误。如果差异的两个字符集之间无法进行无损编码转换（下文会详细介绍），那么就一定会出现乱码。例如：我们的shell是UTF8编码，MySQL的character-set-client配置成了GBK，而表结构却又是charset=utf8，那么毫无疑问的一定会出现乱码。

这里我们就简单演示下这种情况

关于MySQL的编/解码

既然系统之间是按照二进制流进行传输的，那直接把这串二进制流直接存入表文件就好啦。为什么在存储之前还要进行两次编解码的操作呢？

Client to Server的编解码的原因是MySQL需要对传来的二进制流做语法和词法解析。如果不做编码解析和校验，我们甚至没法知道传来的一串二进制流是insert还是update。
File to Engine的编解码是为知道二进制流内的分词情况。举个简单的例子：我们想要从表里取出某个字段的前两个字符，执行了一句形如select left(col,2) from table的语句，存储引擎从文件读入该column的值是E4B8ADE69687。那么这个时候如果我们按照GBK把这个值分割成E4B8,ADE6,9687三个字，并那么返回客户端的值就应该是E4B8ADE6；如果按照UTF8分割成E4B8AD,E69687，那么就应该返回E4B8ADE69687两个字。可见，如果在从数据文件读入数据后，不进行编解码的话在存储引擎内部是无法进行字符级别的操作的。

关于错进错出

在MySQL中最常见的乱码问题的起因就是把错进错出神话。所谓的错进错出就是，客户端(web或shell)的字符编码和最终表的字符编码格式不同，但是只要保证存和取两次的字符集编码一致就仍然能够获得没有乱码的输出的这种现象。但是，错进错出并不是对于任意两种字符集编码的组合都是有效的。我们假设客户端的编码是C，MySQL表的字符集编码是S。那么为了能够错进错出，需要满足以下两个条件

MySQL接收请求时，从C编码后的二进制流在被S解码时能够无损。
MySQL返回数据是，从S编码后的二进制流在被C解码时能够无损。

编码无损转换

那么什么是有损转换，什么是无损转换呢？假设我们要把用编码A表示的字符X，转化为编码B的表示形式，而编码B的字形集中并没有X这个字符，那么此时我们就称这个转换是有损的。那么，为什么会出现两个编码所能表示字符集合的差异呢？如果大家看过博主之前的那篇十分钟搞清字符集和字符编码，或者对字符编码有基础理解的话，就应该知道每个字符集所支持的字符数量是有限的，并且各个字符集涵盖的文字之间存在差异。UTF8和GBK所能表示的字符数量范围如下

GBK单个字符编码后的取值范围是：8140 - FEFE 其中不包括**7E，总共字符数在27000左右
UTF8单个字符编码后，按照字节数的不同，取值范围如下表：

由于UTF-8编码能表示的字符数量远超GBK。那么我们很容易就能找到一个从UTF8到GBK的有损编码转换。我们用字符映射器（见下图）找出了一个明显就不在GBK编码表中的字符，尝试存入到GBK编码的表中。并再次取出查看有损转换的行为。

字符信息具体是：ਅ GURMUKHI LETTER A Unicode: U+0A05, UTF-8: E0 A8 85

在MySQL中存储的具体情况如下：

出错的部分是在编解码的第3步时发生的。具体见下图

可见MySQL内部如果无法找到一个UTF8字符所对应的GBK字符时，就会转换成一个错误mark（这里是问号）。而每个字符集在程序实现的时候内部都约定了当出现这种情况时的行为和转换规则。例如：UTF8中无法找到对应字符时，如果不抛错那么就将该字符替换成 (U+FFFD)

那么是不是任何两种字符集编码之间的转换都是有损的呢？并非这样，转换是否有损取决于以下几点：

被转换的字符是否同时在两个字符集中
目标字符集是否能够对不支持字符，保留其原有表达形式

关于第一点，刚才已经通过实验来解释过了。这里来解释下造成有损转换的第二个因素。从刚才的例子我们可以看到由于GBK在处理自己无法表示的字符时的行为是：用错误标识替代，即0x3F。而有些字符集（例如latin1）在遇到自己无法表示的字符时，会保留原字符集的编码数据，并跳过忽略该字符进而处理后面的数据。如果目标字符集具有这样的特性，那么就能够实现这节最开始提到的错进错出的效果。

我们来看下面这个例子

具体流程图如下。可见在被MySQL Server接收到以后实际上已经发生了编码不一致的情况。但是由于Latin1字符集对于自己表述范围外的字符不会做任何处理，而是保留原值。这样的行为也使得错进错出成为了可能。

如何避免乱码

理解了上面的内容，要避免乱码就显得很容易了。只要做到“三位一体”，即客户端，MySQL character-set-client，table charset三个字符集完全一致就可以保证一定不会有乱码出现了。而对于已经出现乱码，或者已经遭受有损转码的数据，如何修复相对来说就会有些困难。下一节我们详细介绍具体方法。

如何修复已经编码损坏的数据

在介绍正确方法前，我们先科普一下那些网上流传的所谓的“正确方法”可能会造成的严重后果。

错误方法一

无论从语法还是字面意思来看：ALTER TABLE ... CHARSET=xxx 无疑是最像包治乱码的良药了！而事实上，他对于你已经损坏的数据一点帮助也没有，甚至连已经该表已经创建列的默认字符集都无法改变。我们看下面这个例子

可见该语法紧紧修改了表的默认字符集，即只对以后创建的列的默认字符集产生影响，而对已经存在的列和数据没有变化。

错误方法二

ALTER TABLE … CONVERT TO CHARACTER SET … 的相较于方法一来说杀伤力更大，因为从官方文档的解释他的作用就是用于对一个表的数据进行编码转换。下面是文档的一小段摘录：

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

而实际上，这句语法只适用于当前并没有乱码，并且不是通过错进错出的方法保存的表。。而对于已经因为错进错出而产生编码错误的表，则会带来更糟的结果。我们用一个实际例子来解释下，这句SQL实际做了什么和他会造成的结果。假设我们有一张编码是latin1的表，且之前通过错进错出存入了UTF-8的数据，但是因为通过terminal仍然能够正常显示。即上文错进错出章节中举例的情况。一段时间使用后我们发现了这个错误，并打算把表的字符集编码改成UTF-8并且不影响原有数据的正常显示。这种情况下使用alter table convert to character set会有这样的后果：

从这个例子我们可以看出，对于已经错进错出的数据表，这个命令不但没有起到“拨乱反正”的效果，还会彻底将数据糟蹋，连数据的二进制编码都改变了。

正确的方法一 Dump & Reload

这个方法比较笨，但也比较好操作和理解。简单的说分为以下三步：

通过错进错出的方法，导出到文件
用正确的字符集修改新表
将之前导出的文件导回到新表中

还是用上面那个例子举例，我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

正确的方法二 Convert to Binary & Convert Back

这种方法比较取巧，用的是将二进制数据作为中间数据的做法来实现的。由于，MySQL再将有编码意义的数据流，转换为无编码意义的二进制数据的时候并不做实际的数据转换。而从二进制数据准换为带编码的数据时，又会用目标编码做一次编码转换校验。通过这两个特性就相当于在MySQL内部模拟了一次“错出”，将乱码“拨乱反正”了。

还是用上面那个例子举例，我们用UTF-8将数据“错进”到latin1编码的表中。现在需要将表编码修改为UTF-8可以使用以下命令

reference

如何配置Python默认字符集（http://cenalulu.github.io/python/python-encoding/）
字符编码笔记：ASCII，Unicode和UTF-8（http://www.ruanyifeng.com/blog/2007/10/ascii_unicode_and_utf-8.html）
Unicode中文编码表（http://www.chi2ko.com/tool/CJK.htm）
Emoji Unicode Table（http://apps.timwhitlock.info/emoji/tables/unicode）
Every Developer Should Know About The Encoding（http://www.joelonsoftware.com/articles/Unicode.html）
http://www.psce.com/blog/2015/03/03/mysql-character-encoding-part-2/
http://www.qqxiuzi.cn/zh/hanzi-gbk-bianma.php
http://zh.wikipedia.org/wiki/%E6%B1%89%E5%AD%97%E5%86%85%E7%A0%81%E6%89%A9%E5%B1%95%E8%A7%84%E8%8C%83
http://www.qqxiuzi.cn/zh/hanzi-gbk-bianma.php
http://blog.csdn.net/ws84643557/article/details/6905167

作者介绍：卢钧轶（cenalulu）

DBA+社群原创专家。
目前就职于Facebook MySQL Infra Team，主要负责大规模MySQL数据库运维。在Failover，备份，监控，优化，数据库私有云等相关领域有一定经验和个人理解。
之前先后就职于BesTV和大众点评网。曾在阿里嘉年华和中华数据库大会上有过相关分享。

小编精心为大家挑选了近日最受欢迎的几篇热文:

回复001，看杨志洪《【职场心路】一个老DBA的自白》；

回复002，看丁俊的《【重磅干货】看了此文，Oracle SQL优化文章不必再看！》；

回复003，看胡怡文《PG，一道横跨oltp到olap的梦想之桥》；

回复004，看陈科《memcached&redis等分布式缓存的实现原理》；

回复005，看宋日杰《Oracle后台专家解决library cache锁争用的终极武器》；

回复006，看郑晓辉《存储和数据库不得不说的故事》；

回复007，看袁伟翔《揭秘Oracle数据库truncate原理》；

回复008，看杨建荣《立等可取:工具定制让Oracle优化变得更简单快捷》；

回复009，看丁启良《LINUX类主机JAVA应用程序占用CPU、内存过高分析手段》；

回复010，看徐桂林《以应用为中心的企业混合云管理》。

关于DBA+社群

DBA+社群是中国最大的涵盖各种架构师、数据库、中间件的微信社群！线上分享2次/周、线下沙龙1次/月，顶级峰会6次/年，直接受众10000+，间接影响50万+ITer。DBA+社群致力于搭建一个学习交流、专业人脉、跨界合作的公益平台，更多精彩请持续关注dbaplus微信订阅号！

扫码关注

DBAplus社群

超越DBA圈子，连接的不仅仅是DBA