数据库系列 1
—MySQL数据库协议之字符集与排序
开篇碎碎念
因为日常工作与数据库打交道,先准备写点简单的自己关于数据库的认识。今天刚好在看有关字符集协商的问题,简单的开篇讲一下MySQL中的字符集设定。
MySQL数据库的设置与其协议都是简洁且自由度高的,这两种特性综合起来就导致了在使用MySQL数据库中会遇到一些由于灵活的配置导致的坑。
可能很多人都遇到过一个问题,就是在使用MySQL数据库时,会发先一些中文字符或者表情产生了乱码。或者使用了较新的客户端去连接服务器的时候,产生了类似于「ERROR 1115 (42000): Unknown character set: ‘utf8mb4’」这样连接失败的错误。
MySQL中的字符集
character sets
MySQL中的字符集由两部分构成,一是字符的编码方式(character sets),二是编码的排序方式(collation)。看字面意思,编码方式就是单个字符的编码,排序方式就是比较字符之间顺序的规则。网上搜索相关的内容大多重复讲了这一点。
要搞清楚字符集,首先要弄明白字符集在什么时候需要。字符集是我们可见的字符在计算机中存储的一种方式,每个字符根据规则按照一定长度存储为十六进制(二进制)的值,而这个值如何解释则决定了它在打印时产生的字符。
首先要明确一个分类,字符集在数据库中有两大类的使用(我个人的理解,如有纰漏请指正)。我感觉网上现有的解释都混淆了这两类使用,导致不容易理解。
- 存储使用
- 传输使用
存储使用,就是在数据库服务端,对数据进行存储时使用的编码格式。在MySQL数据库中的表现为server, database, table, column四个层次的字符集设置,可以很详细地设定字符集。这个一般是在设计数据库时定义的,和我们要存储的数据的类型有关,通常也要同时确认其排序规则,关于排序规则后问再讲。
传输使用,发生在客户端(官方客户端或者我们自己的程序或者代理软件等等)和数据库服务器进行交互时。客户端和服务器交互,必然要进行通信,不论上层的封装是基于tcp/ip,还是pipe转发,本地套接字,其应用层的数据库协议都是相同的。服务端从库中取出数据,按照存储的字符集设定解释,然后再按照某种编码方式传输给客户端,客户端按照这种编码方式解释数据流;反之亦然。
如果我们自己来设计,服务器、客户端、传输协议中的编码方式最好是统一的,这样能够用最少的解释和编码过程。MySQL数据库的做法是让客户端声明一种编码方式,然后服务器与客户端的通信都使用同一种编码方式。在MySQL协议的握手过程中,服务端先发送第一个包,客户端在返回包时声明了自己要用的字符集以及排序方式。(字符集对排序方式是一对多的关系,因此也可以理解为只需要传输特定的排序方式)这个排序方式以一个字节的十六进制值表示。服务端收到这个字符集/排序方式后,查看自己是否支持,如果支持,则采用该方式进行编码解码,进行通行。如果不支持,则产生回退过程,服务器将自己支持的字符集发送给客户端,进行协商,客户端选择一种进行支持,或者双方无法达成一致,断开连接。可以说,在通信过程中,字符集的选择主要取决于客户端的声明。这一个字符集最后的表现形式为三项设定:character_set_client, character_set_results和character_set_connection。可以使用以下语句进行查询。
1 | SHOW SESSION VARIABLES LIKE 'character\_set\_%'; |
可以看到这三项的值是统一的。据我现在的调研,各种客户端基本都不支持设置默认字符集,不同的客户端版本自己会声明一种字符集,5.5之前多数为latin1,5.7以后基本上都是utf8mb4,所以坑多为5.5.-5.7之间,可能存在声明utf8的字符集。一些比较奇怪的声明是客户端将这一标志位声明为0xff即255,但并没有对应的字符集,我猜测这可能是声称自己自持现有的所有字符集。可能是看到的文档版本不太对,最新的8.0.16文档中0xff(255)即是utf8mb4的标志。
客户端的配置项不提供指定字符集的方式,我们可以直接修改服务器中的三项设定
1 | SET character_set_client = charset_name; |
一般来说使用匹配版本的客户端和服务器,能够避免大量问题,如果要自己指定通信时的字符集,推荐5.1之前都使用latin1(5.5之前不止此utf8mb4编码),5.5以后都可以使用utf8mb4编码。注意:ucs2,utf16,utf32是不被支持的。
而关于之前容易产生的两种错误,第一个乱码的情况,一般是因为字符集不统一导致解码错误。在MySQL中,多数表现为按照utf8格式进行编码时产生的。因为历史遗留问题,MySQL中的一个坑是其utf8编码并不是我们通常认知中的utf8编码,这是一种最大长度为3字节的编码,因此当字符的长度是4字节时,解析就会出现错误,导致乱码的出现。MySQL官方也终于在MySQL5.5版本开始支持了4字节uft8,即utf8mb4字符集。第二个错误是使用了较高的(默认版本为5.7+)客户端连接较老的服务器(5.1),导致后续客户端申请更换字符集时遭到了拒绝。
1 | SET NAMES 'utf8mb4'; |
collations
排序方式是比较单个字符顺序的规则定义,比如忽视大小写,则A与a的排序是等同的;而b则应该排在a之后。每个字符编码方式都对应一种默认的排序方式。
尾记
第一次写文章,思路不够清晰,可能存在问题的地方也比较多,希望接下来能够清晰地做一个数据库专题,尤其是关于数据库协议相关的内容。
引用
https://dev.mysql.com/doc/refman/5.5/en/charset-connection.html