MySQL字符编码指南--基础篇

1. 总论

要了解MySQL的字符编码，解决乱码问题，必须先了解字符编码。我们知道所谓信息，在计算机中不过是一串的位(bit：0 or 1)，每8个bit组成了一个字节，而这些字节到底表示什么，取决于读到这些对象的上下文，1个字节序列，可以表示整数，字符串或者机器指令，当然也可以表示中文，日文，甚至上古文字。

即：

信息=位＋上下文

而为了确定字符与二进制位的对应关系，就必须制定编码。

因为计算机是西方发明的，所以最早的也是我们最熟悉的编码ASCII主要包含的也就是26个基本拉丁字母（大小写）、阿拉伯数目字和英式标点符号等。但地球其它地方的人们也需要现代化，也要使用计算机，靠ASCII仅仅7个bit，128个字符位是不可能表示所有国家、地区的字符的。特别是像汉字这种非拼音字符，常用的也有几千个，1个字节都放下不。

def hex_to_text(hex_str):    
    byte_str = bytes.fromhex(hex_str)      
    print(hex_str+':');
    
    # 对于Latin1编码    
    print(byte_str.decode('latin1'))
    
    # 对于GBK编码    
    print(byte_str.decode('gbk'))
    
    # 对于UTF8编码    
    print(byte_str.decode('utf8'))
    print('------------------');
    
    return 0
    
hex_str = '41'hex_to_text(hex_str)
hex_str = 'e682a8e5a5bd'hex_to_text(hex_str)
hex_str = 'C1AACDA8'hex_to_text(hex_str)

上面是一个python程序，将16进制数字转为3种不同的编码字符，让我们看下执行结果：

41:
A
A
A
------------------
e682a8e5a5bd:
您好
鎮ㄥソ
您好
------------------
C1AACDA8:
ÁªÍ¨
联通
---------------------------------------------------------------
UnicodeDecodeError Traceback (most recent call last)
Cell In[5], line 26
     23 hex_to_text(hex_str)
     25 hex_str = 'C1AACDA8'
---> 26 hex_to_text(hex_str)

Cell In[5], line 13, in hex_to_text(hex_str)
     10 print(byte_str.decode('gbk'))
     12 # 对于UTF8编码
---> 13 print(byte_str.decode('utf8'))
     15 print('------------------');
     17 return 0

UnicodeDecodeError: 'utf-8' codec can't decode byte 
0xc1 in position 0: invalid start byte

可以看到对于'41'，因为ASCII字符是GBK和UTF8编码的子集，所以在3种字符集中都是字符'A'。

而对于'e682a8e5a5bd'，一共6个字节，对应latin1字符集的6个字符：'您好'，对应gbk字符集的3个汉字'鎮ㄥソ'，对应utf8字符集的2个字符'您好'。3个字符集都能解析出字符，那么到底显示哪个，完全取决于上下文了。'鎮ㄥソ'也可以视为一种乱码，因为这个组合没有意义，但这种乱码是可以恢复的，只要指定正确的字符集即可。

对于'C1AACDA8'，需要注意的是在gbk中表示'联通'，但在utf8中找不到对应的字符，报错''utf-8' codec can't decode byte'。对于找不到对应字符的情况，有些程序会直接转为' '或者?号，这样的情况下，乱码已经无法恢复。

2. 2大体系

经过很多年的发展，现在的字符编码主要有两大体系，ANSI和UNICODE。

ANSI是由一个母体（ASCII）出发，产生了多个不兼容平行分支（LATIN1、GBK、JIS）。而UNICODE体系则将所有的字符统一于一种编码规范之下，所谓“天下大同，唯此一码”，可以包含地球上所有的语言文字符号。

ANSI编码占用的空间较少，如汉字是双字节，但只能支持一种非ASCII语言，适用于个性化的个人PC操作系统；UNICODE支持所有语言，但是是多字节编码，占用空间较大，如汉字是3字节，一般适用于数据传输和web页面。

下面具体来说说这2大体系：

3. ANSI体系

ANSI是指美国国家标准学会，成立于1918年，制定了很多工业标准，ASCII编码是由ANSI最初制定；而UNICODE由ISO制定，ISO是国际标准化组织，成立于1947年，ANSI是ISO的重要成员。

ANSI体系：

又称为ISO-646，ASCII只对基本控制符号、英文、数字、标点进行了编码，为了在计算机上存储各个国家的语言，各个国家根据自己的语言特点，制定了完全兼容ASCII的编码，例如西欧语言的latin-1（ISO-8859-1）、中文的GBK、日语的JIS等，需要注意的是这些编码之间互不兼容。当信息在国际间交流时，无法将属于两种语言的文字，存储在同一段 ANSI 编码的文本中。在windows系统中，ANSI编码具体采用哪种表现形式，由操作系统的语言内码决定，简体中文的采用GBK表达，日文用JIS表达等。所以，可以认为，在简体中文windows系统中，ANSI=GBK，在日文系统中，ANSI=JIS。

图片

ASCII:

美国信息交换标准代码，等同于国际标准ISO/IEC 646，ASCII第一次以规范标准的型态发表是在1967年，最后一次更新则是在1986年，至今为止共定义了128个字符；33个字符无法显示（这是以现今操作系统为依归，但在DOS模式下可显示出一些诸如笑脸、扑克牌花式等8-bit符号），且这33个字符多数都已是陈旧的控制字符。控制字符的用途主要是用来操控已经处理过的文字。在33个字符之外的是95个可显示的字符，包含26个基本拉丁字母、阿拉伯数目字和英式标点符号等。ASCII的局限在于只能显示26个基本拉丁字母、阿拉伯数目字和英式标点符号，因此只能用于显示现代美国英语（而且在处理英语当中的外来词如naïve、café、élite等等时，所有重音符号都不得不去掉，即使这样做会违反拼写规则）。

ASCII的33个控制字符：

注意，输入控制字符需要用ctrl+脱出字符，比如telnet下面的Escape character is ‘^]’ ，实际输入要用ctrl+]，而不是^+]

ASCII的95个可显示字符：

3.1 latin1

扩展的ASCII包含ASCII中已有的128个字符（数字0–32显示在下图中），又增加了128个字符，总共是256个。

Latin1是ISO-8859-1的别名,也等同于Windows cp1252 ，有些环境下写作Latin-1。

ISO-8859-1编码是单字节编码，向下兼容ASCII，其编码范围是0×00-0xFF，0×00-0×7F之间完全和ASCII一致，0×80-0×9F之间是控制字符，0xA0-0xFF之间是文字符号。

ISO-8859-1收录的字符除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。欧元符号出现的比较晚，没有被收录在ISO-8859-1当中。

因为ISO-8859-1编码范围使用了单字节内的所有空间，在支持ISO-8859-1的系统中传输和存储其他任何编码的字节流都不会被抛弃。换言之，把其他任何编码的字节流当作ISO-8859-1编码看待都没有问题。

MySQL数据库默认编码Latin1可以存放汉字就是利用这个原理，实际的编码其实是GBK或者UTF8。

ASCII编码是一个7位的容器，ISO-8859-1编码是一个8位的容器。

标准latin1编码表：

在上表中，0×20是空格、0xA0是不换行空格、0xAD是选择性连接号。0×00-0×1F、0×7F、0×80-0×9F在此字符集中未有定义。（控制字符是由ISO/IEC 6429定义）。

注意MySQL中的latin1和标准latin1是有区别的，我们说过0×80-0×9F之间是未定义的，MySQL把这部分编码拿出来，自己指定了字符，比如欧元符号！如下表所示：

测试如下： set names latin1;select hex('€');

为什么是'E282AC'而不是'80'？因为即使你设置了MySQL的连接字符集为latin1，MySQL在执行HEX()函数时依然会使用Unicode编码将字符串转换为16进制表示。即使字符"€"在latin1中的编码是0x80，但在Unicode（以及UTF-8）中，它的编码是U+20AC，对应的UTF-8编码的16进制表示是E282AC。

3.2 GB2312->GBK->GB18030

GB2312:

GB 2312 或 GB 2312-80 是中华人民共和国国家标准简体中文字符集，全称《信息交换用汉字编码字符集·基本集》，又称GB0，由中国国家标准总局发布，1981年5月1日实施。GB2312编码通行于中国大陆；新加坡等地也采用此编码。中国大陆几乎所有的中文系统和国际化的软件都支持GB 2312。

GB 2312标准共收录6763个汉字，其中一级汉字3755个，二级汉字3008个；同时收录了包括拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母在内的682个字符。

GB 2312中对所收汉字进行了“分区”处理，每区含有94个汉字／符号。这种表示方式也称为区位码。

01-09区为特殊符号。

16-55区为一级汉字，按拼音排序。

56-87区为二级汉字，按部首／笔画排序。

10-15区及88-94区则未有编码。

每个汉字及符号以两个字节来表示。第一个字节称为“高位字节”，第二个字节称为“低位字节”。“高位字节”使用了0xA1-0xF7（把01-87区的区号加上0xA0），“低位字节”使用了0xA1-0xFE（把01-94加上0xA0）。

GBK:

因为GB 2312 不足表示所有的汉字，此编码标准只收录了6763个常用汉字，而GB字库以外大量汉字，如中国前总理朱镕基的“镕”字，只能通过补字软件拼字或其它造字程序补字。尽管补出的汉字在字形上满足需要，但在字体风格、大小、结构方面难以协调统一，并且无法检索。

1993年，Unicode 1.1版本推出，收录中国大陆、台湾、日本及韩国通用字符集的汉字，总共有20,902个。

中国大陆订定了等同于Unicode 1.1版本的“GB 13000.1-93”。

微软利用GB 2312-80未使用的编码空间，收录GB 13000.1-93全部字符制定了GBK编码。它实际上是CP936字码表 (Code Page 936)的扩展（之前CP936和GB 2312-80一模一样），最早实现于Windows 95简体中文版。虽然GBK收录GB 13000.1-93的全部字符，但编码方式并不相同。

GBK自身并非国家标准，只是曾由国家技术监督局标准化司、电子工业部科技与质量监督司公布为“技术规范指导性文件”。

原始GB13000一直未被业界采用，后续国家标准GB18030技术上兼容GBK而非GB13000。

编码范围如下表：

GBK共收入21886个汉字和图形符号

* GB2312中的全部汉字、非汉字符号。

* BIG5中的全部汉字。

* 与ISO 10646相应的国家标准GB13000中的其它CJK汉字，以上合计20902个汉字。

* 其它汉字、部首、符号，共计984个。

GB18030:

中华人民共和国国家质量技术监督局于2000年3月17日推出了GB 18030-2000标准，以取代GBK。GB 18030-2000除保留全部GBK编码汉字，在第二字节把能使用范围再度进行扩展，增加了大约一百个汉字及四位元组编码空间，但是将GBK作为子集全部保留。

GB18030 编码是一二四字节变长编码。一字节部分从 0×0~0×7F 与 ASCII 编码兼容。二字节部分, 首字节从 0×81~0xFE, 尾字节从 0×40~0×7E 以及 0×80~0xFE, 与 GBK标准基本兼容。

四字节部分, 第一字节从 0×81~0xFE, 第二字节从 0×30~0×39, 第三和第四字节的范围和前两个字节分别相同。四字节部分覆盖了从 0×0080 开始, 除去二字节部分已经覆盖的所有 Unicode 3.1 码位。也就是说, GB18030 编码在码位空间上做到了与 Unicode 标准一一对应,这一点与 UTF-8 编码类似。

4. UNICODE体系

下面来看看UNICODE体系：

UNICODE即ISO-10646，最终由ISO组织规范。

UNICODE定义了字符编码的序列关系。而真正进行编码的时候，有两种方式，UCS-2和UCS-4。UCS-2为两字节编码，范围在0×0000-0xffff之间，定义2^16=65536个码位；UCS-4为四字节编码，范围在0×00000000-0xffffffff之间，定义2^32=2147483648个码位。

由于UNICODE是多字节编码，在传输过程中需在字节序、容错性方面进行定义。基本可以认为 UTF16=UCS2，UTF-32=UCS4，这两种都是定长编码，即每个字符的编码都是固定长度的。通常意义上所说的UNICODE是指UTF-16。

而UTF8是一种变长编码，每个字符的编码长度由1-3位不等；这样在单字节字符为主的情况下，UTF-8在存储效率、传输效率和容错性上有显著的性能优势，成为互联网数据传输和网页展示的主流编码方式。

在 Unix 下使用 UCS-2 (或 UCS-4) 会导致非常严重的问题. 用这些编码的字符串会包含一些特殊的字符, 比如 ‘/0′ 或 ‘/’, 它们在 文件名和其他 C 库函数参数里都有特别的含义. 另外, 大多数使用 ASCII 文件的 UNIX 下的工具, 如果不进行重大修改是无法读取16位的字符的. 基于这些原因, 在文件名, 文本文件, 环境变量等地方, UCS-2 不适合作为 Unicode 的外部编码。

外部编码指的是用于将Unicode字符存储到文件或通过网络发送的编码方式。UTF-8、UTF-16和UTF-32等都是Unicode的外部编码。

UCS-2（Universal Character Set 2）是一种固定长度（2字节）的Unicode字符编码方式，但它只能表示Unicode字符集中的前65,536个字符，无法表示更高的代码点，这就使得UCS-2不能表示所有的Unicode字符。比如，UCS-2无法表示那些在基本多语言平面（Basic Multilingual Plane, BMP）之外的字符。

另一方面，UCS-2在编码字符时使用了两个字节，与ASCII编码不兼容，这可能在处理一些只能处理ASCII字符的系统或软件时导致问题。

因此，尽管UCS-2在一些情况下可能有用，但它通常不被推荐作为Unicode的外部编码，特别是在需要处理大量数据或需要兼容ASCII的场景中。在大多数情况下，UTF-8是一个更好的选择，因为它既可以表示所有的Unicode字符，又与ASCII编码兼容，而且对于大部分文本数据，UTF-8的存储效率也更高。

UCS和UTF的转换关系如下：

UTF-8编码字符理论上可以最多到6个字节长，然而16位BMP（Basic Multilingual Plane）字符最多只用到3字节长。

UCS                                  UTF8

U-00000000–U-0000007F:0xxxxxxx

U-00000080–U-000007FF:110xxxxx 10xxxxxx

U-00000800–U-0000FFFF:1110xxxx 10xxxxxx 10xxxxxx

U-00010000–U-001FFFFF:11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

U-00200000–U-03FFFFFF:111110xx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx

U-04000000–U-7FFFFFFF:1111110x 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx 10xxxxxx

转换关系如下图所示：

5. 编码转换

ASCII、LATIN-1、UNICODE之间是如何转换？

如图所示，以10进制计。ASCII和LATIN1是单字节编码，8BIT的LATIN1编码当最高位为0时，与ASCII一致。

16BIT的UTF16当高8位为0时，低八位表示的编码与LATIN1一致。

而ANSI和UNICODE通过编码对照表，一一进行对应和转换，每种ANSI体系的编码，都存在一个转换UNICODE的对照表。例如GBK<=>UTF16对照表、JIS<=>UTF16对照表。

6. 编码识别

对于一个文本文件，计算机如何知道是采用何种编码并进行正确的显示呢？

字节顺序标记（英语：byte-order mark，BOM）是位于码点U+FEFF的统一码字符的名称。当以UTF-16或UTF-32来将UCS/统一码字符所组成的字符串编码时，这个字符被用来标示其字节序。它常被用来当做标示文件是以UTF-8、UTF-16或UTF-32编码的记号并且可以表示是大端序还是小端序。具体如下表所示：

不同编码的字节顺序标记的表示：

如果没有BOM标志，采用顺序检测：如果所有单字节字符都在0×00-0×7f之间，则为ASCII；否则，根据编码特征确定是哪种编码，如果错误的编码导致编码特征冲突，则会出现乱码。

比如当txt文档中一切字符都在 C0≤AA（第一个字节）≤DF ，80≤BB（第二个字节）≤BF 这个范围时，notepad无法确认文档格式，没有自动依照GB2312格式来”Display”。比如”联通”就是C1 AA CD A8，刚好在上面范围内，所以不能正常显现。因为程序认为它更像一个UTF-8编码文本。这是因为“联通”两个字的GB-2312编码看起来更像UTF-8编码导致的。

那么MySQL又是如何存储和展示不同字符集的字符呢？什么情况下会产生乱码？乱码是否能恢复？且听下回分解。

公众号"数据库之巅"分享这十几年来我在数据库特别是互联网金融数据库运维走过的路和踩过的坑，欢迎大家关注。