MySQL字符集和比较规则
字符集和比较规则简介
字符集简介
计算机中实际存储的是二进制数据，将字符映射成二进制数据的过程叫编码，将二进制数据映射到字符的过程叫解码。人们抽象出一个字符集的概念来描述某个字符范围的编码规则。
比如我们自定义了一个名字叫做char-demo的字符集，它包含的字符范围和编码规则额如下：
包含字符：'a'、'b'、'c'、'd'。编码规则为一个字符编码一个字符的形式。字符和字节的映射关系如下：
'a'：00000001（十六进制0x01）
'b'：00000010（十六进制0x02）
'c'：00000011（十六进制0x03）
'd'：00000100（十六进制0x04）
下面是一些字符串用char-demo字符编码后的二进制表示：
- ‘ab’：0000000100000010（十六进制0x0102）
- ‘cd’：0000001100000100（十六进制0x0304）
- ‘ef’：无法表示，因为我们自定义的字符集char-demo不包含’e’和’f’
比较规则简介
确定字符集表示的字符范围和编码规则后，该如何比较两个字符的大小呢？最简单的比较规则就是二进制比较规则，比如’a’的编码为00000001，‘b’为00000010，所以’a’小于’b’。二进制规则尽管很简单，但有时候不符合现实需求，比如在一些场合下，英文字符都是不区分大小写的，也就是说’a’和’A’是相等的。对于某种字符集来说，可以制定用来比较字符大小的多种规则，也就是说同一种字符集可以有多种的比较规则。
一些重要的字符集
- ASCII字符集：收录了128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符，由于ASCII总共才128个字符，所以可以用一个字节来进行编码。例如：‘L’=01001100（十六进制0x4C，十进制76）
- ISO 8859-1字符集：收录了256个字符，它在ASCII基础上又扩充了128个西欧常用字符，ISO 8859-1字符集也可以用一个字节来进行编码（这个字符集也有一个别名Latin1）。
- GB2312字符集：收录了汉字及拉丁字母、希腊字母、日文平假名及假名字母、俄语西里尔字母，收录汉字6763个，收录其他文字符号682个。这种字符集同时又兼容ASCII字符集，所以在编码方式上显的有些奇怪：如果该字符在ASCII字符集中，则采用一子节编码，否则采用两字节编码。
- GBK字符集：GBK字符集在GB2312字符集的基础上进行了扩充，编码方式兼容GB2312字符集。
- UTF-8字符集：几乎收录了当今世界各国各地区的字符，并在不断扩充，这种字符集兼容ASCII字符集，采用变长编码。UTF-8字符集是Unicode字符集的一种编码方案，Unicode字符集还有UTF-16、UTF-32等编码方案。
这种使用不同字节数来表示一个字符的编码方式成为变长编码方式。计算机在读取一个字节序列时，怎么区别某个字节代表的是一个单独的字符还是某个字符的一部分呢？以UTF-8为例，UTF-8使用如下规范编码：
| 编码范围 | 字节数 | 编码 |
|---|---|---|
| U < 128 | 一字节 | 0xxxxxxx |
| 128 <= U < 2048 | 二字节 | 110xxxxx 10xxxxxx |
| 2048 <= U < 65536 | 三字节 | 1110xxxx 10xxxxxx 10xxxxxx |
| 65536 <= U < 2097152 | 四字节 | 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx |
| 以字节高位作为标识，0表示本字节代表一个单独的字符，110表示本字节加下一个字节才是一个单独的字符，依此类推。 |
MySQL中支持的字符集和比较规则
MySQL中的utf8和utf8mb4
UTF-8是用1～4个字节表示一个字符，但是我们常用的一些字符使用1～3个字节就可以表示了，为了优化系统的性能和存储，MySQL将UTF-8分为了2种：
| 字符集 | 描述 |
|---|---|
| utf8mb3 | 别名utf8，是阉割版UTF-8字符集，只用1～3个字节表示字符 |
| utf8mb4 | 正宗的UTF-8字符集，使用1～4个字节表示字符。如果要存emoji表情，请使用utf8mb4 |
| PS：MySQL8.0中，对utf8mb4进行了优化，很大程序上提升了性能，并且将utf8mb4设置为了默认的字符集 |
字符集的查看
SHOW [CHARACTER SET|CHARSET] [LIKE 匹配模式]
例子：
SHOW CHARACTER SET LIKE 'utf8';
SHOW CHARSET LIKE 'utf8';
结果：
+---------+---------------+-------------------+--------+
| Charset | Description | Defalut collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
PS：Defalut collation表示该字符集的默认比较规则，Maxlen表示这种字符集最多需要几个字节表示一个字符。
比较规则的查看
SHOW COLLATION [LIKE 匹配模式]
例子：
SHOW COLLATION LIKE 'utf8_general_ci';
结果：
+-----------------+---------+----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------+---------+----+---------+----------+---------+---------------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | PAD SPACE |
+-----------------+---------+----+---------+----------+---------+---------------+
- 比较规则的名称以与其关联的字符集的名称开头。比如在上面的查询结果中，比较规则名称是以utf8开头的。
- 后面紧跟着该比较规则所应用的语言。比如utf_polish_ci表示波兰语的比较规则，而utf8_general_ci是一种通用的比较规则。
- 每种字符集对应若干比较规则，且每种字符集都有一种默认的比较规则，Default列值为Yes就是该字符集默认的比较规则，比如utf8字符集默认的比较规则就是utf8_general_ci。
- 名称后缀意味着该比较规则是否区分语言中的重音、大小写等，可用值参照下表：
| 后缀 | 英文释义 | 描述 |
|---|---|---|
| _ai | accent insensitive | 不区分中重音 |
| _as | accent sensitive | 区分重音 |
| _ci | case insensitive | 不区分大小写 |
| _cs | case sensitive | 区分大小写 |
| _bin | binary | 以二进制方式比较 |
字符集和比较规则的应用
各级别的字符集和比较规则
MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别、列级别。
服务器级别
服务器级别的字符集
SHOW VARIABLES LIKE 'character_set_server';
服务器级别的比较规则
SHOW VARIABLES LIKE 'collation_server';
在启动服务器程序时，可以通过修改配置文件来修改这两个变量的值：
[server]
character_set_server=gb2312
collation_server=gb2312_chinses_ci
数据库级别
数据库级别的字符集
SHOW VARIABLES LIKE 'character_set_database';
数据库级别的比较规则
SHOW VARIABLES LIKE 'collation_database';
在创建和修改数据库时，可以修改这两个变量的值：
创建数据库
CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
修改数据库
ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
- 其中DEFAULT可以省略，如果没指定字符集和比较规则，则变量与服务器级别下对应的系统变量名的值相等。
- 修改这两个变量，不会改变已经存在的表和列的字符集和比较规则，只会影响之后创建的表和列的默认字符集和比较规则。
表级别
创建表
CREATE TABLE 表名 (列信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];
修改表
ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];
- 其中DEFAULT可以省略，如果没指定字符集和比较规则，则变量与数据库级别下对应的系统变量名的值相等。
- 修改这两个变量，不会改变已经存在的列的字符集和比较规则，只会影响之后创建的列的默认字符集和比较规则。
列级别
创建列
CREATE TABLE 表名 (
列名 类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列 ...
);
修改列
ALTER TABLE 表名 MODIFY 列名 类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
- 其中DEFAULT可以省略，如果没指定字符集和比较规则，则变量与表级别下对应的系统变量名的值相等。
- 在修改列的字符集时，如果列中存储的数据不能用修改后的字符集进行表示，就会报错。比如列最初使用utf8字符集，数据中存了中文，现在把它修改为ascii的话就会出错，因为acsii字符集不能表示中文字符。
仅修改字符集或仅修改比较规则
由于字符集和比较规则之间互相关联，因此如果只修改字符集，比较规则也会跟着变化。如果只修改比较规则，字符集也会跟着变化，具体规则如下：
- 只修改字符集，则比较规则变为修改后的字符集的默认比较规则。
- 只修改比较规则，则字符集变为修改后的比较规则所对应的字符集。
客户端和服务器通信过程中使用的字符集
编码和解码使用的字符集不一致
如果程序A将字符串"我"用UTF-8字符集编码后的序列是0xE68891，如果程序A将这个字符系列发给程序B，程序B用GBK字符集解码后成l了"鎴"。所以编码和解码使用的字符集不一样，会产生所谓的乱码。
字符集转换的概念
将字节序列按照正确的字符集解码后，再用其他的字符集进行编码，这个过程叫做字符集的转换。
MySQL中字符集的转换过程
客户端发送的请求和服务器返回的响应本质上就是一个字节序列，这个过程中经历了多次的字符集转换。
MySQL客户端发送给服务器的请求以及服务器返回给客户端的响应，都遵从了一定的格式(这个格式指明了请求和相应的每一个字节分别代表什么意思)，我们把MySQL客户端和服务器进行通信的过程中事先规定好的数据格式称为MySQL通信协议。
客户端发送请求
一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。
当使用UNIX操作系统时
LC_ALL、LC_CTYPE、LANG 这三个环境变量的值决定了操作系统当前使用的是哪种字符集。其中优先级顺序为LC_ALL > LC_CTYPE > LANG。如果LC_ALL设置了字符集，则无论LC_CTYPE、LANG设置了什么，都以LC_ALL设置的字符集为准。
shell> echo $LC_ALL
shell> echo $LC_CTYPE
shell> echo $LANG
zh_CN.UTF-8
很显然，只有LANG设置了字符集，则我的macOS操作系统当前使用的字符集是UTF-8。假设这3个环境变量都没有设置，默认字符集位US-ASCII。
当使用Windows操作系统时
在Windows中，字符集成为代码页(code page)，一个代码页与一个唯一的数字相关联。比如936代表GBK字符集，65001代表UTF-8字符集。我们可以在Windows命令行窗口的标题栏右键打开"属性"子菜单，从打开的弹窗中选择"选项"选项卡。可以看到当前代码页是936，也就表示当前命令行窗口使用的事GBK字符集。如下图所示：
或者直接输入命令可查：
> chcp
活动代码页: 936
在Windows操作系统中，如果在启动MySQL客户端程序时携带了default-character-set启动选项。那么MySQL客户端将以启动选项指定的字符集对请求的字符串进行编码(这一点不适用于类UNIX操作系统)
mysql --default-character-set=utf-8
服务器接收请求
从本质上来说，服务器接收到的请求就是一个字节序列。服务器将这个字节序列看作是使用系统变量character_set_client代表的字符集进行编码的字节序列(每个客户端与服务器建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量，这个变量是SESSION级别的)。可以通过如下命令进行修改变量：
set character_set_client=latin1;
服务器处理请求
服务器会将请求的字符序列当作采用character_set_client对应的字符集进行编码的字符序列，不过在真正处理请求时又将其转换为使用SESSION级别的系统变量character_set_connection对应的字符集进行编码的字节序列。看到这句话的小伙伴是不是有这个疑问，直接将客户端的字符序列解码后转换成表列对应的字符集不就可以了吗，为什么还要弄一层character_set_connection，这不是多此一举吗？手册里有一句话：转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的校对规则优先级。
以下这两个例子的语句不涉及表列查询，需要用到character_set_connection指定的字符集和collation_connection指定的比较规则进行计算比较
> SELECT LENGTH('中国人');
或者
> SELECT 'a' = 'A';
服务器生成响应
服务器会将字符根据SESSION级别的系统变量character_set_result的值，按照配置的字符集编码成字符序列，再发送给客户端。如有特殊需要，
set character_set_results=latin1;
下图是提及到的三个变量的描述(都是SESSION级别的作用范围)：
| 系统变量 | 描述 |
|---|---|
| character_set_client | 服务器认为请求是按照该系统变量指定的字符集进行编码的 |
| character_set_connection | 服务器在处理请求时，会把请求字节序列从character_set_client转化为character_set_connection |
| character_set_results | 服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码 |
客户端接收到相应
客户端收到字节序列后，如果没有特殊设置的话，一般用操作系统当前使用的字符集来解释这个字节序列。
比较规则的应用
比较规则通常用来比较字符的大小以及对某些字符串进行排序，所以有时候也被称为排序规则。
学习更多MySQL知识，请移步MySQL系列
请移步 MySQL系列 一定会带给你想要的MySQL知识
721
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
