MySQL是怎样运行的（三）

第3章　乱码的前世今生——字符集和比较规则

3.1字符集和比较规则

3.1.1字符集简介

计算机只能存储二进制数据，存储字符串要建立二进制数据映射关系：

• 界定字符范围
• 将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码

抽象出一个字符集的概念来描述某个字符范围的编码规则

3.2.2 比较规则简介

同一种字符集可以有多种比较规则

3.2.3 一些重要的字符集

• ASCII字符集
  共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符，使用一个字节来进行编码
• ISO 8859-1字符集
  收录了256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符，也可以用一个字符来编码，又叫latin1
• GB2312字符集
  收录了汉字以及拉丁字母、希腊字母、日本平假名及片假名字母、俄语西里尔字母。共收录汉字6763个，其他文字符号682个。同时兼容ASCII字符集，编码方式：如果该字符在ASCII字符集中，采用1字节编码，否则再用2字节编码
  这种表示一个字符需要字节数可能不同的编码方式成为变长编码方式
• GBK字符集
  只是在收录字符范围上对GB2312字符集进行了扩充编码方式兼容CB2312
• utf8字符集
  收录所有字符，而且还在不断扩充兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1~4个字节
  准确来说utf8知识Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32

对于同一字符，不同字符集也可能有不同的编码方式

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

3.2.1 MySQL中的utf8和utf8mb4

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

注意：在MySQL中的utf8是utf8mb3的别名。utf8使用1~3个字节来表示一个字符，如存储一些emoji表情类的，使用utf8mb4

3.2.2 字符集的查看

show (character set|charset) [like 匹配模式]

 3.2.3 比较规则的查看

show collation [like 匹配模式]

命名规律

• 比较规则名称以与其关联的字符集名称开头
• 后边紧跟着该比较规则主要作用于哪种语言
• 名称后缀意味着该比较规则是否区分语言中的重音、大小写等
  

 每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，show collation的返回结果中的default列的值为yes就是该字符集的默认比较规则

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

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

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

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

服务器级别

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

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

 数据库级别

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

 其中default可省略

查看当前数据库使用的字符集和比较规则：

 注意，这两个变量是只读的，不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则

数据库的创建语句也可以不指定字符集和比较规则

这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则

表级别

 创建和修改表时可以指定该表的字符集和比较规则

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

列级别

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

  如果创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则

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

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

无论是哪个级别这两条规则都适用

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

编码和解码使用的字符集不一致的后果：产生乱码

字符集转换的概念：字符串从utf8字符集转换为gbk字符集的过程

MySQL中的字符集转换：从发送请求到返回结果过程中伴随多次字符集的转换，在这个过程中会用到3个系统变量

select * from t where s='我';

set character_set_connection=gbk

•  客户端发送请求所使用的字符集

  一般情况下客户端所使用的字符集和当前操作系统一致

  类Unix系统使用的是utf8  我=>0xE68891

  windows使用的是gbk

• 服务器接收到客户端发送过来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是character_set_client，然后把这串字节转换成character_set_connection字符集编码的字符
  0xE68891 => 0xCED2

• 因为表t的列采用gbk字符集，与character_set_connection一致，所以直接到列中找到字节值为0xCED2的记录（如果这个列使用的字符集和character_set_connection不一致，还需要进行一次字符集转换）

• 上一步骤找到的记录中列其实是一个字节串0xCED2，采用的gbk进行编码，所以先将这个字节串使用gbk进行解码得到字符串'我'，然后再把这个字符串使用character_set_result代表的字符集，也就是utf8进行编码，得到新的字节串0xE68891，然后发给客户端

• 由于客户端使用的字符集是utf8，所以顺利将0xE68891解释为字符我。

注意：character_set_connection必须包含的字符范围一定涵盖请求中的字符，通常三个变量设置成一样：

在启动客户端时候指定启动选项，将3个值设置成utf8，配置文件 ：

 3.3.3 比较规则的应用

比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中。

3.4 总结

1. 字符集是指某个字符范围的编码规则

2. 比较规则是针对某个字符集中的字符比较大小的一种规则

3. 在MySQL中，一个字符集可以有若干种比较规则，其中一个默认的比较规则，一个比较规则必须对应一个字符集

4. 查看MySQL支持的字符集和比较规则

5. MySQL有四个级别的字符集和比较规则

• 服务器级别

  character_set_server表示服务器级别的字符集，collation_server表示服务器级别的比较规则

  数据库级别

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

  

   character_set_database表示当前数据库的字符集，collation_database表示当前默认数据库的比较规则，这两个系统变量是只读的，不能修改。如果没有指定当前默认数据库，则变量与相应的服务器级别系统变量相等

• 表级别

   创建和修改表时可以指定字符集和比较规则：

   

• 列级别

   创建和修改列定义时可以指定字符集和比较规则：

  

6. 从发送请求到接收结果过程中发生的字符集转换

• 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串
• 服务器将客户端发送过来的字节串采用character_set_client代表的字符集进行解码，将解码的字符串再按照character_set_connection代表的字符集进行编码
• 如果character_set_connection代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则还需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使用的字符集之后进行操作
• 将从某列获取到的字节串从该列使用的字符集转换为character_set_result代表的字符集后发送到客户端
• 客户端使用操作系统的字符集解析接收到的结果集字节串

7. 比较规则的作用通常体现比较字符串大小的表达式以及字符串列进行排序中