第3章 字符集和比较规则

3.1 字符集和比较规则简介

3.1.1 字符集简介

计算机存储字符是建立字符与二进制数据的映射关系

将字符映射成二进制数据的过程叫做编码

将二进制数据映射到字符的过程叫做解码

3.1.2 比较规则简介

如何比较两个字符的大小呢?

例如：直接比较这两个字符对应的二进制编码的大小

或将两个大小写不同的字符全部都转为大写或者小写，再比较这两个字符对应的二进制数据

3.1.3 一些重要的字符集

ASCII、ISO8895-1、GB2312、GBK、UTF-8

使用不同字节数来表示一个字符的编码方式称为变长编码方式。

UTF-8编码一个字符时需要1~4个字节

3.2 MySQL中支持的字符集和比较规则

3.2.1 MySQL中utf8和utf8mb4

在MySQL中，字符集表示一个字符所用的最大字节长度在某些方面会影响系统的存储和性能。

• utfmb3："阉割过的"UTF-8字符集，只使用1~3个字节表示字符
• utfmb4：正宗的UTF-8字符集，使用1~4个字节表示字符

注意：在MySQL中，utf8是utf8mb3的别名，即使用1~3字节来表示一个字符。

在MySQL8.0中，默认字符集为utfmb4。

3.2.2 字符集的查看

查看当前MySQL中支持的字符集：

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

• Default collation列表示这种字符集中一种默认的比较规则
• Maxlen代表这种字符集最多需要几个字节来表示一个字符

几个常用字符集的Maxlen列

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

3.2.3 比较规则的查看

查看MySQL中支持的比较规则

SHOW COLLATION [LIKE 匹配的模式];

每种字符集对应若干种比较规则，且每种字符集都有一种默认的比较规则。在执行SHOW COLLATION语句后返回的结果中，Default列的值为YES的比较规则，就是该字符集的默认比较规则，比如utf8字符集默认的比较规则就是utf8-general-ci。

3.3 字符集和比较规则的应用

3.3.1 各级别的字符集和比较规则

MySQL有4个级别的字符集和比较规则：服务器级别、数据库级别、表级别、列级别。

1. 服务器级别

服务器级别的字符集和比较规则对应的系统变量及其描述：

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

查看这两个系统变量

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

修改这两个系统变量

SET character_set_server = 字符集名称;
SET collation_server = 比较规则名称;

2. 数据库级别

在创建数据和修改数据库时可以指定该数据库的字符集和比较规则

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

数据库级别的字符集和比较规则对应的系统变量及其描述：

系统变量	描述
character_set_database	当前数据库的字符集
collation_database	当前数据库的比较规则

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

前提是使用USE语句选择当前的默认数据库。如果没有默认数据库，则变量与服务器级别下相应的系统变量具有相同的值。

USE 数据库名;
SHOW VARIABLES LIKE 'character_set_database';  
SHOW VARIABLES LIKE 'collation_server';

注意：character_set_database和 collation_server这两个系统变量只是告诉用户当前数据库的字符集和比较规则是什么。我们不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则。

在数据库的创建语句中也可以不指定字符集和比较规则，比如：CREATE DATABASE 数据库名;这将使用服务器级别的字符集和比较规则作为数据的字符集和比较规则。

3. 表级别

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

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

如果创建表的语句没有指明字符集和比较规则，则使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

4. 列级别

对于存储字符串的列，同一个表中不同的列也可以有不同的字符集和比较规则。

在创建和修改列的时候指定表的字符集和比较规则：

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

对于某个列来说，如果在创建和修改表的语句中没有指明字符集和比较规则，则使用该列所在表的字符集和比较规则作为其字符集和比较规则。

5. 仅修改字符集或仅修改比较规则

由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化；如果只修改比较规则，字符集也会跟着变化。

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

6. 各级别字符集和比较规则小结

• 如果创建或修改列时没有显示指定字符集和比较规则，则该列默认使用表的字符集和比较规则
• 如果创建表时没有显示指定字符集和比较规则，则该表默认使用数据库的字符集和比较规则
• 如果创建数据库时没有显示指定字符集和比较规则，则该数据库默认使用服务器的字符集和比较规则

3.3.2 客户端和服务器通信过程中使用的字符集

1. 编码和解码使用的字符集不一致

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

2. 字符集转换的概念

一字符从从字符集A编码转换成从字符集B编码，称为字符集转换

例如："我"通过字符集UTF-8编码后成为0xE68891，然后通过UTF-8解码成为"我"，最后再通过GBK编码，则我们称字符串"我"从UTF-8字符集转换成为GBK字符集。

3. MySQL中的字符集转换过程

客户端发送请求

一般情况下，客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。

在Windows中，字符集成为代码页。936代表GBK字符集，65001代表UTF-8字符集。

查看代码页的两种方法

• Win+R —> cmd —> chcp

  • 

• Windows命令行窗口 —> 属性—> 选项

  • 

服务器接受请求

• 从本质上来说，服务器接收到的请求就是一个字节序列。服务器将这个字节序列看作是使用系统变量character_set_client代表的字符集进行编码的字节序列(每个客户端与服务器，建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量，这个变量是Session级别的)。

• 客户端在编码请求字符串时实际使用的字符集，与服务器在收到一个字节序列后认为该字节序列所采用的编码字符集，是两个独立的字符集。一般情况下，我们应该尽量保证这两个字符集一致。

• 如果character_set_client对应的字符集不能解释请求的字节序列，那么服务器就会发出警告。

服务器处理请求

• 服务器会将请求的字节序列当作采用character_set_client对应的字符集进行编码的字节序列，不过在真正处理请求时又会将其转换为使用SESSION级别的系统变量character_set_connection对应的字符集进行编码的字节序列。

服务器生成响应

服务器在向客户端返回字节序列时，是采用哪种字符集进行编码的。取决于系统变量character_set_results的值。

系统变量	描述
character_set_client	服务器认为请求是按照该系统变量指定的字符集进行编码的
character_set_connection	服务器在处理请求时，会把请求字节序列从character_set_client转换为character_set_connection
character_set_results	服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码

这3个系统变量在服务器中的作用都是SESSION级别。每个客户端在与服务器建立连接后，服务器都会为这个连接维护这3个变量。

查看这几个系统变量的值:
SHOW VARIABLES LIKE '%character_set%';
修改这几个系统变量的值:
SET character_set_client charset_name;
SET character_set_connection charset_name;
SET character_set_results charset_name;

• 如果在启动MySQL客户端时设置了default-character-set启动选项，那么客户端会忽视操作系统当前使用的字符集，直接将default-character-set启动选项中指定的值作为客户端的默认字符集。在连接服务器时，客户端将默认的字符集信息与用户名，密码等信息一起发送给服务器，服务器在收到后会将character_set_client、character_set_connection、character_set_results这3个系统变量的值初始化为客户端的默认字符集。

客户端接收到响应

对于Windows操作系统来说，客户端会使用客户端的默认字符集来解释这个字节序列。

3.3.3 比较规则的应用

在对字符串进行比较，或者对某个字符串列执行排序操作时，如果没有得到想象中的结果，需要思考一下是不是比较规则的问题。