《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第三章
一、字符集和比较规则简介
1.1 字符集简介
由于计算机存储的是二进制数据，因此如果需要存储字符串，需要建立字符和二进制数据的映射关系。这个过程，需要搞清楚：
要把哪些字符映射成二进制数据？ => 界定字符范围
怎么映射？ =>
编码：字符映射成二进制数据的过程；解码：二进制数据映射到字符的过程
1.2 比较规则简介
编码后，如何比较两个字符的大小呢？
最简单的，二进制比较规则：直接比较这两个字符对应的二进制编码的大小。但是这种方法有时不符合现实需求。
对于某一种字符集来说，可以制定用来比较字符大小的多种规则，也就是行锁同一种字符集可以有多种比较规则。
1.3 一些重要的字符集
ASCII字符集：收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。
ISO
8859-1字符集：在ASCII码的基础上扩充了128个字符。该字符集也可以使用一个字节来编码（即Latin1）
GB2312字符集：兼容ASCII字符集
GBK字符集：兼容GB2312字符集
UTF-8字符集：几乎收录了当今世界各个国家/地区使用的字符，而且还在不断扩充。兼容ASCII字符集，采用变长编码方式，编码一个字符时需要1~4个字节。
二、MySQL中支持的字符集和比较规则
2.1 MySQL中的utf8和utf8mb4
UTF-8字符集在表示一个字符时需要1~4个字节，但是我们常用的一些字符使用1~3字节就可以表示了。
在MySQL中，字符集表示一个字符所用的最大字节长度在某些方面会影响系统的存储和性能。
MySQL中的UTF-8:
utf8mb3:
阉割过的UTF-8字符集，只使用1~3个字节表示字符，MySQL中的utf-8
utf8mb4:
正宗的UTF-8字符集，使用1~4个字节表示字符，可以存储emoji表情
MySQL8中，已经将utf8mb4作为默认的字符集了。
2.2 字符集的查看
命令：
SHOW (CHARACTER SET | CHARSET) [LIKE 匹配的模式];
结果：
mysql> SHOW CHARSET; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | binary | Binary pseudo charset | binary | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | ... | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | ... | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.01 sec)
最后一列 Maxlen，表示该字符集最多需要几个字节来表示一个字符。
常见的字符集的Maxlen列（记忆）：
字符集名称
Maxlen
ascii
1
latin1
1
gb2312
2
gbk
2
utf8
3
utf8mb4
4
2.3 比较规则的查看
命令：
SHOW COLLATION [LIKE 匹配的模式];
前面提到过，一种字符集可能对应多种比较规则。
查看utf8字符集下的比较规则：
mysql> SHOW COLLATION LIKE 'utf8_%' ; +-----------------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +-----------------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb3_bin | utf8mb3 | 83 | | Yes | 1 | PAD SPACE | ... | utf8mb3_general_ci | utf8mb3 | 33 | Yes | Yes | 1 | PAD SPACE | | utf8mb3_general_mysql500_ci | utf8mb3 | 223 | | Yes | 1 | PAD SPACE | | utf8mb3_german2_ci | utf8mb3 | 212 | | Yes | 8 | PAD SPACE | | utf8mb3_hungarian_ci | utf8mb3 | 210 | | Yes | 8 | PAD SPACE | | utf8mb3_icelandic_ci | utf8mb3 | 193 | | Yes | 8 | PAD SPACE | | utf8mb3_latvian_ci | utf8mb3 | 194 | | Yes | 8 | PAD SPACE | | utf8mb3_lithuanian_ci | utf8mb3 | 204 | | Yes | 8 | PAD SPACE | | utf8mb3_persian_ci | utf8mb3 | 208 | | Yes | 8 | PAD SPACE | | utf8mb3_polish_ci | utf8mb3 | 197 | | Yes | 8 | PAD SPACE | | utf8mb3_romanian_ci | utf8mb3 | 195 | | Yes | 8 | PAD SPACE | | utf8mb3_roman_ci | utf8mb3 | 207 | | Yes | 8 | PAD SPACE | | utf8mb3_sinhala_ci | utf8mb3 | 211 | | Yes | 8 | PAD SPACE | | utf8mb3_slovak_ci | utf8mb3 | 205 | | Yes | 8 | PAD SPACE | | utf8mb3_slovenian_ci | utf8mb3 | 196 | | Yes | 8 | PAD SPACE | | utf8mb3_spanish2_ci | utf8mb3 | 206 | | Yes | 8 | PAD SPACE | | utf8mb3_spanish_ci | utf8mb3 | 199 | | Yes | 8 | PAD SPACE | | utf8mb3_swedish_ci | utf8mb3 | 200 | | Yes | 8 | PAD SPACE | | utf8mb3_tolower_ci | utf8mb3 | 76 | | Yes | 1 | PAD SPACE | | utf8mb3_turkish_ci | utf8mb3 | 201 | | Yes | 8 | PAD SPACE | | utf8mb3_unicode_520_ci | utf8mb3 | 214 | | Yes | 8 | PAD SPACE | | utf8mb3_unicode_ci | utf8mb3 | 192 | | Yes | 8 | PAD SPACE | | utf8mb3_vietnamese_ci | utf8mb3 | 215 | | Yes | 8 | PAD SPACE | | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | | utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD | | utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD | | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD | ... | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE | ... | utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4 | 322 | | Yes | 0 | NO PAD | | utf8mb4_mn_cyrl_0900_as_cs | utf8mb4 | 323 | | Yes | 0 | NO PAD | +-----------------------------+---------+-----+---------+----------+---------+---------------+ 117 rows in set (0.01 sec)
比较规则的命名是很有规律的：
比较规则的名称以与其关联的字符集的名称开头
后面紧跟着该比较规则所应用的语言。utf8mb4_general_ci是一种通用的比较规则
名称后缀意味着该比较规则是否区分语言中的重音、大小写等。
后缀
英文释义
描述
_ai
accent insensitive
不区分重音
_as
accent sensitive
区分重音
_ci
case insensitive
不区分大小写
_cs
case sensitive
区分大小写
_bin
binary
以二进制方式比较
每种字符集对应若干种比较规则，并且每种字符集都有一种默认的比较规则。执行结果中的Defult列的值为YES的比较规则就是该字符集的默认比较规则。
三、字符集和比较规则的应用
3.1 各级别的字符集和比较规则
MySQL有4个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别、列级别。
3.1.1 服务器级别
两个系统变量：
系统变量
描述
character_set_server
服务器级别的字符集
collation_server
服务器级别的比较规则
查看：
mysql> SHOW VARIABLES LIKE 'character_set_server' ; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_server | utf8mb4 | +----------------------+---------+ 1 row in set (0.01 sec) mysql> SHOW VARIABLES LIKE 'collation_server' ; +------------------+--------------------+ | Variable_name | Value | +------------------+--------------------+ | collation_server | utf8mb4_0900_ai_ci | +------------------+--------------------+ 1 row in set (0.00 sec)
同理，在启动服务器程序时，可以通过启动选项或者在服务器程序运行过程中使用SET语句来修改这两个变量的值。
[server] character_set_server =asciicollation_server =ascii_general_ci
3.1.2 数据库级别
在创建或者修改数据库的时候指定该数据库的字符集和比较规则：
CREATE DATABASE 数据库名 [[DEFAULT ] CHARACTER SET 字符集名称] [[DEFAULT ] COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT ] CHARACTER SET 字符集名称] [[DEFAULT ] COLLATE 比较规则名称];
DEFAULT可以省略
查看当前数据库使用的字符集和比较规则，涉及到两个系统变量：
系统变量
描述
character_set_database
当前数据库的字符集
collation_database
当前数据库的比较规则
前提是使用了USE语句选择当前的数据库。如果没有指定数据库，该变量与服务器级别下相应的系统变量的值具有相同的值。
查看：
mysql> USE db_learning; Database changed mysql> SHOW VARIABLES LIKE 'character_set_database' ; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | character_set_database | utf8mb4 | +------------------------+---------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'collation_database' ; +--------------------+--------------------+ | Variable_name | Value | +--------------------+--------------------+ | collation_database | utf8mb4_0900_ai_ci | +--------------------+--------------------+ 1 row in set (0.00 sec)
📢注意：
character_set_database和collation_database这两个系统变量只是用来告诉用户当前数据库的字符集和比较规则是什么，但是不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则。
如果在数据库创建语句中不指定字符集和比较规则，这将会使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。
3.1.3 表级别
在创建或者修改表的时候指定该表的字符集和比较规则：
CREATE TABLE 表名 (列信息) [[DEFAULT ] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]; ALTER DATABASE 数据库名 [[DEFAULT ] CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
DEFAULT可以省略
如果在创建表的语句中没有指明字符集和比较规则，则使用该表所在的数据库的字符集和比较规则作为该表的字符集和比较规则。
3.1.4 列级别
对于存储字符串的列 ，同一个表中不同的列也可以有不同的字符集和比较规则。我们可以在创建和修改该列的时候可以指定该列的字符集和比较规则：
CREATE TABLE 表名 ( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... ); ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
对于某个列来说，如果在创建和修改表的语句中没有指明字符集和比较规则，则使用该列所在表的字符集和比较规则作为其字符集和比较规则。
📢注意：在修改列的字符集时需要注意，如果列中存储的数据不能用修改后的字符集进行表示，则会发生错误。
3.1.5
仅修改字符集或者仅修改比较规则
由于字符集和比较规则之间相互关联，二者会随对方相互改变：
只修改字符集，则比较规则将变为修改后的字符集默认的比较规则
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集
这两条规则对于每个级别的字符集和比较规则都生效。
3.1.6
各级别字符集和比较规则小结
如果没有显式指定字符集和比较规则，则会按照以下顺序查找对应的字符集和比较规则：列
-> 表 -> 数据库 -> 服务器。
对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定每个列存储的实际数据所占用的存储空间大小。
3.2
客户端和服务器通信过程中使用的字符集
3.2.1
编码和解码使用的字符集不一致的后果
字符串在计算机上的体现就是一个字节串，如果对于同一个字符串的编码和解码使用的字符集不一样，会产生意想不到的效果，我们看上去就像是乱码一样。
3.2.2 字符集转换的概念
如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换，也就是字符串'我'从utf8字符集转换为gbk字符集。
3.2.3 MySQL中的字符集转换过程
从用户的角度来看，客户端发送的请求以及服务器返回的响应都是一个字符串。
从机器的角度来看，客户端发送的请求和服务器返回的响应本质上就是一个字节序列。
在这个过程中经历了多次的字符集转换，涉及三个系统变量：
系统变量
描述
character_set_client服务器解码请求时使用的字符集
character_set_connection服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results服务器向客户端返回数据时使用的字符集
这几个系统变量在不同的操作系统上的默认值可能不同。
mysql> SHOW VARIABLES LIKE 'character_set_client' ; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | character_set_client | utf8mb4 | +----------------------+---------+ 1 row in set (0.01 sec) mysql> SHOW VARIABLES LIKE 'character_set_connection' ; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | character_set_connection | utf8mb4 | +--------------------------+---------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'character_set_results' ; +-----------------------+---------+ | Variable_name | Value | +-----------------------+---------+ | character_set_results | utf8mb4 | +-----------------------+---------+ 1 row in set (0.00 sec)
这三个系统变量在服务器中的作用范围都是SESSION级别。每个客户端在于服务器建立连接后，服务器都会为这个连接维护者三个变量。
客户端发送请求所使用的字符集：一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样。
服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是character_set_client，然后把这串字节转换为character_set_connection字符集编码的字符
服务器将响应使用character_set_results代表的字符集编码，然后发送给客户端。
📢注意：
服务器认为客户端发送过来的请求是用character_set_client编码的。
服务器将把得到的结果集使用character_set_results编码后发送给客户端。
character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符 ，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。
MySQL设置字符集的命令：
SET NAMES 字符集名;SET character_set_client = 字符集名;SET character_set_connection = 字符集名;SET character_set_results = 字符集名;
另外，如果想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，可以在启动客户端的时候指定一个叫default-character-set的启动选项：
[client] default-character-set =utf8
3.3 比较规则的应用
比较规则 的作用通常体现在比较字符串大小的表达式以及对某个字符串进行排序中，所有有时候也称为排序规则 。
【栗子🌰】
表t的列col使用的字符集是gbk，使用的比较规则是gbk_chinese_ci。
现在插入几条记录：
mysql> INSERT INTO t(col) VALUES('a' ), ('b' ), ('A' ), ('B' ); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0
查询的时候按照t列排序一下：
mysql> SELECT * FROM t ORDER BY col; +------+ | col | +------+ | a | | A | | b | | B | | 我 | +------+ 5 rows in set (0.00 sec)
可以发现在默认的比较规则gbk_chinese_ci中是不区分大小写的，我们现在把列col的比较规则修改为gbk_bin：
mysql> ALTER TABLE t MODIFY col VARCHAR(10) COLLATE gbk_bin; Query OK, 5 rows affected (0.02 sec) Records: 5 Duplicates: 0 Warnings: 0
由于gbk_bin是直接比较字符的编码，所以是区分大小写的，接着来查询排序下：
mysql> SELECT * FROM t ORDER BY s; +------+ | s | +------+ | A | | B | | a | | b | | 我 | +------+ 5 rows in set (0.00 sec)
=>如果以后在对字符串做比较或者对某个字符串列做排序操作时，没有得到想象中的结果，需要思考一下是不是比较规则 的问题。
