湫枫染墨

字符集和比较规则

7/17/2022 Mysql

摘要

Mysql Version：5.7.36

# 一：简介

字符集

计算机中只能存储二进制数据，那该如何存储字符串呢？建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚，要把哪些字符映射成二进制数据？也就是界定清楚字符范围。

怎么映射？

将一个字符映射成一个二进制数据的过程也叫做编码； 将一个二进制数据映射到一个字符的过程叫做解码。

人们抽象出一个字符集的概念来描述某个字符范围的编码规则。比如自定义一套新字符集叫newCharacter，假设字符'a'、'b'、'A'、'B'，编码规则采用1个字节编码一个字符的形式，字符和字节的映射关系如下：

'a' -> 00000001 (十六进制：0x01)
'b' -> 00000010 (十六进制：0x02)
'A' -> 00000011 (十六进制：0x03)
'B' -> 00000100 (十六进制：0x04)
1
2
3
4

现在就可以用二进制形式表示一些字符串了

'bA' -> 0000001000000011（十六进制：0x0203）
'baB' -> 000000100000000100000100（十六进制：0x020104）
'cd' -> 无法表示，字符集newCharacter不包含字符'c'和'd'
1
2
3

比较规则

在确定了newCharacter字符集字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小，比方说字符 'a' 的编码为0x01，字符 'b' 的编码为0x02，所以'a' 小于'b'，这种简单的比较规则也可以被称为二进制比较规则，英文名为binary collation。

二进制比较规则是简单，但有时候并不符合现实需求，比如在很多场合对于英文字符我们都是不区分大小写的，也就是说 'a' 和 'A' 是相等的，在这种场合下就不能简单粗暴的使用二进制比较规则了，这时候可以 将两个大小写不同的字符全都转为大写或者小写，再比较这两个字符对应的二进制数据 来进行比较。

这是一种稍微复杂一点点的比较规则，但是实际生活中的字符不止英文字符一种，比如我们的汉字有几万之多，对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则。

# 二：常见字符集

# 2.1 ASCII 字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编，例如：

'L' -> 01001100（十六进制：0x4C，十进制：76）
'M' -> 01001101（十六进制：0x4D，十进制：77）
1
2

# 2.2 ISO-8859-1 字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符（包括德法两国的字母），也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。

# 2.3 GB2312 字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII 字符集，所以在编码方式上显得有些奇怪：

  • 如果该字符在ASCII 字符集中，则采用1字节编码；
  • 否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。比方说字符串'爱u'，其中 '爱' 需要用2个字节进行编码，编码后的十六进制表示为0xCED2，'u' 需要用1个字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是0xCED275 。

如何区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？

ASCII字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

# 2.4 GBK 字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

# 2.5 utf8 字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，如下：

'L' -> 01001100（十六进制：0x4C）
'啊' -> 111001011001010110001010（十六进制：0xE5958A）
1
2

准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。

对于同一个字符，不同字符集也可能有不同的编码方式。比如对于汉字'我'来说，ASCII 字符集中根本没有收录这个字符，utf8和gb2312字符集编码方式如下：

utf8编码：111001101000100010010001（3个字节，十六进制表示是：0xE68891）
gb2312编码：1100111011010010（2个字节，十六进制表示是：0xCED2）
1
2

# 三：MySQL支持的字符集和排序规则

# 3.1 MySQL中的utf8和utf8mb4

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以MySQL设计者定义了两个概念：

  • utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
  • utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果需要4字节编码一个字符的情况，比如存储一些emoji表情啥的，那就使用utf8mb4。

# 3.2 字符集查看

MySQL支持多种字符集，查看当前MySQL中支持的字符集可以用下边这个语句：

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
1

可以看到，我使用的这个MySQL版本一共支持41种字符集，其中的 Default collation 列，表示这种字符集中一种默认的比较规则。注意返回结果中的最后一列 Maxlen，它代表该种字符集表示一个字符最多需要几个字节。下面几个常用到的字符集的Maxlen务必记住：

字符集名称 Maxlen
ascii 1
latin1 1
gb2312 2
gbk 2
utf8 3
utf8mb4 4

# 3.3 比较规则查看

查看MySQL中支持的比较规则的命令如下：

SHOW COLLATION [LIKE 匹配的模式];
1

前边说过一种字符集可能对应着若干种比较规则，MySQL支持的字符集就已经非常多了，所以支持的比较规则更多，下面查看utf8字符集下的比较规则：

这些比较规则的命名还挺有规律的，具体规律如下：

  1. 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8 开头的。后边紧跟着该比较规则主要作用于哪种语言，比如：
  • utf8_polish_ci：表示以波兰语的规则比较
  • utf8_spanish_ci：是以西班牙语的规则比较
  • utf8_general_ci：是一种通用的比较规则。
  1. 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的，具体可以用的值如下：
后缀 英文释义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION 的返回结果中的 Default 列的值为 YES 的就是该字符集的默认比较规则，比方说 utf8 字符集默认的比较规则就是 utf8_general_ci。

# 四：字符集和比较规则的应用

MySQL有4个级别的字符集和比较规则，分别是：

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

接下来看一下怎么设置和查看这几个级别的字符集和比较规则。

# 4.1 服务器级别

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则：

系统变量 描述
character_set_server 服务器级别的字符集
collation_server 服务器级别的比较规则

查看这两个系统变量的值：

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
1
2

可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET语句 修改这两个变量的值。比如可以在配置文件中这样写：

[server]
character_set_server=gbk
collation_server=gbk_chinese_ci
1
2
3

# 4.2 数据库级别

在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
1
2
3
4
5
6
7

例如：

CREATE DATABASE charset_demo_db
CHARACTER SET gb2312
COLLATE gb2312_chinese_ci;
1
2
3

如果想查看当前数据库使用的字符集和比较规则，使用USE语句选择当前默认数据库，再用上面服务器级的那两个变量查看，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值，例如：

可以看到这个 charset_demo_db 数据库的字符集和比较规则就是创建语句中指定的。需要注意的一点是：character_set_database 和 collation_database 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

# 4.3 表级别

在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]
1
2
3
4
5
6
7

例如：

CREATE TABLE t(
	col VARCHAR(10)
) CHARACTER SET utf8 COLLATE utf8_general_ci;
1
2
3

# 4.4 列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
1
2
3
4
5
6

例如：

ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
1

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

仅修改字符集或仅修改比较规则，由于字符集和比较规则是互相有联系的，如果只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则；
  • 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

# 五：客户端和服务器通信中的字符集

对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果，看上去就像是产生了乱码一样。

例如，utf8字符集编码下的 '我' 为 0xE68891 占3个字节，如果客户端用gbk字符集解码，'我' 这个汉字仅是占2个字节，具体解码过程如下：

  1. 首先看第一个字节0xE6，它的值大于0x7F(十进制：127)，说明是两字节编码，继续读一字节后是0xE688，然后从gbk 编码表中查找发现是字符'鎴'；
  2. 继续读一个字节0x91，它的值也大于0x7F，再往后读一个字节发现木有了，所以这是半个字符；
  3. 最终0xE68891 被gbk 字符集解释成一个字符'鎴' 和半个字符。

# 5.1 MySQL中字符集的转换

从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量，如下：

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

过程图示：

SHOW VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'character_set_results';
1
2
3

可以使用SET修改系统变量，如：

SET character_set_connection = gbk;
1

如果使用navicat这类可视化工具，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集。

通常把character_set_client、character_set_connection、character_set_results这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便MySQL提供了一条非常简便的语句：

SET NAMES 字符集名;
1

等同于同时设置三个变量。如果想在启动系统时就指定变量的值成一样。可以指定一个叫 default-character-set 的启动选项。

# 六：参考文献

  • 《MySQL 是怎样运行的 - 小孩子4919》
最后更新: 10/22/2022, 10:41:05 AM

Mysql 连接与请求过程