字符集和比较规则简介

字符集简介
计算机中只能存储二进制数据。
那该怎么存储字符串呢？答案是：建立字符与二进制数据的映射关系，
建立映射关系最起码要搞清楚两件事：

要把哪些字符映射成二进制数据？也就是界定字符范围
怎么映射，即映射规则

将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。
人们抽象出一个字符集的概念来描述某个字符范围的编码规则。

比较规则简介
在确定了字符集表示字符的范围以及编码规则后，如何比较两个字符的大小呢？
最容易想到的就是：直接比较这两个字符对应的二进制编码的大小，这种简单的比较规也被称为二进制比较规则，英文名为binary collation。
二进制比较规则是简单，但有时候并不符合现实需求，比如在很多场合对于英文字符不区分大小写，也就是说：’a’ 和 ‘A’ 是相等的，在这种场合下可以这样指定比较规则：

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

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

一些重要的字符集

不同的字符集表示的字符范围，和用到的编码规则可能都不一样。

**ASCII** 字符集
共收录 128 个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。
由于总共才 128 个字符，所以可以使用 1 个字节来进行编码，我们看一些字符的编码方式：

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

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

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

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

这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。

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

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

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

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

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

字符集的查看

MySQL 支持很多字符集，查看当前 MySQL 支持的字符集可以用如下语句：
show (character set / charset) [like 匹配的模式];
图片.png

其中的 Default collation 列表示这种字符集中一种默认的比较规则。
最后一列Maxlen 代表该种字符集表示一个字符最多需要的字节数。

几个常到的字符集的 Maxlen 列：

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

比较规则的查看

查看 MySQL 中支持的比较规则的命令如下：
show collation [like 匹配的模式];
一种字符集可能对应着若干种比较规则，MySQL 支持的字符集就已经非常多了，所以支持的比较规则更多，utf8 字符集下的比较规则如下：
图片.png
这些比较规则的命名比较有规律，具体规律如下：

比较规则名称以与其关联的字符集的名称开头。如上 utf8 的比较规则名称都是以 utf8 开头
后边紧跟着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较，utf8_general_ci 是一种通用的比较规则。
名称后缀意味着该比较规则是否区分语言中的重音、大小写什么的，具体可以用的值如下：比如utf8_general_ci 这个比较规则是以 ci 结尾的，说明不区分大小写。 | 后缀 | 英文释义 | 描述 | | —- | —- | —- | | _ai | accent insensitive | 不区分重音 | | _as | accent sensitive | 区分重音 | | _ci | case insensitive | 不区分大小写 | | _cs | case sensitive | 区分大小写 | | _bin | binary | 以二进制方式比较 |

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

utf8 字符集默认的比较规则就是 utf8_general_ci

字符集和比较规则的应用

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

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

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

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

create database 数据库名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];
alter database 数据库名
    [[default] character set 字符集名称]
    [[default] collate 比较规则名称];

其中的 default 可以省略，并不影响语句的语义。
如果想查看当前数据库使用的字符集和比较规则，可以查看下面两个系统变量的值（前提是使用 use 语句选择当前默认数据库，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值）

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

character_set_database 和 collation_database 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。
数据库的创建语句中不指定的话，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别
我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

create table 表名 (列的信息)
    [[default] character set 字符集名称]
        [collate 比较规则名称];
alter table 表名
    [[default] character set 字符集名称]
    [collate 比较规则名称];

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

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

create table 表名(
    列名 字符串类型 [character set 字符集名称] [collate 比较规则名称],
    其他列...
);
alter table 表名 modify 列名 字符串类型 [character set 字符集名称] [collate 比较规则名称];

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

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

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

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

不论哪个级别的字符集和比较规则，这两条规则都适用。

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

如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果，作为人类的我们看上去就像是产生了乱码一样。
如果接收 0xE68891 这个字节串的程序按照 utf8 字符集进行解码，然后又把它按照 gbk 字符集进行编码，最后编码后的字节串就是 0xCED2，我们把这个过程称为字符集的转换，也就是字符串 ‘我’ 从 utf8 字符集转换为 gbk 字符集。

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

系统变量	描述
character_set_client	服务器解码请求时使用的字符集
character_set_connection	服务器运行过程中使用的字符集
character_set_results	服务器向客户端返回数据时使用的字符集

现在假设我们客户端发送的请求是下边这个字符串：
SELECT * FROM t WHERE s = '我';
为了方便理解这个过程，只分析字符 ‘我’ 在这个过程中字符集的转换。

character_set_client = utf8 character_set_connection = gbk character_set_results = utf8

现在看一下在请求从发送到结果返回过程中字符集的变化：

一般情况下客户端所使用的字符集和当前操作系统一致，不同的操作系统使用的字符集可能不一样，客户端使用的是 utf8 字符集。所以字符 ‘我’ 在发送给服务器的请求中的字节形式就是：0xE68891
- 类 Unix 系统使用的是 utf8
- Windows 使用的是 gbk
服务器接收到客户端发送来的请求其实是一串字节，它会认为这串字节采用的字符集是chacharacter_set_client，然后把这串字节转换为 character_set_connection 字符集编码的字节。所以字符串 ‘我’ 将从 utf8 字符集转换为 gbk 字符集，所以最后得到字节其实是 0xCED2。
因为表 t 的列 col 采用的是 gbk 字符集，与 character_set_connection 一致，所以直接到列中找字节值为 0xCED2 的记录，最后找到了一条。
服务器会将查找到的结果集包装成一个字符串，需要将这个字符串从 character_set_connection 字符集转换成 character_set_results 字符集，然后把转换后的字节串返回到客户端。

· 服务器生成的结果集中含有字符 ‘我’，由于设置的 character_set_connection 的值是 gbk，所以
结果集中的 ‘我’ 对应的字节串是 0xCED2，而我的计算机 character_set_results 的值是 utf8，所以需
将 ‘我’ 从 gbk 字符集转换到 utf8 字符集，也就是转换后的字节串是 0xE68891。

由于客户端是用的字符集是 utf8，所以可以顺利的将 0xE68891 解释成字符 ‘我’，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

图片.png
从这个分析中我们可以得出几点需要注意的地方：

服务器认为客户端发送过来的请求是用 character_set_client 编码的。

假设客户端采用的字符集和 character_set_client 不一样的话，这就会出现意想不到的情况。

服务器将把得到的结果集使用 character_set_results 编码后发送给客户端。

假设你的客户端采用的字符集和 character_set_results 不一样的话，这就会出现客户端无法解码
结果集的情况，结果就是在屏幕上出现乱码。

character_set_connection 只是服务器在处理请求时使用的字符集，要注意的是：该字符集包含的字符范围一定涵盖请求以及结果集中的字符，要不然会出现无法将请求中的字符编码成 character_set_connection 字符集或者无法编码结果集中的字符的情况。

为了方便我们设置，MySQL 提供了一条非常简便的语句：
set names 字符集名;
这一条语句产生的效果和我们执行这3条的效果是一样的：

set character_set_client = 字符集名;
set character_set_connection = 字符集名;
set character_set_results = 字符集名;

比方说：我的客户端使用的是 utf8 字符集，所以需要把这几个系统变量的值都设置为 utf8。
如果你想在启动客户端的时候就把 character_set_client、character_set_connection、character_set_results 这三个系统变量的值设置成一样的，那可以在启动客户端的时候指定一个叫default-character-set 的启动选项，它起到的效果和执行一遍 set names utf8; 是一样的。

比较规则的应用

比较规则的作用通常体现在：比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为排序规则。
使用不同的比较规则进行排序，排序结果可能不同。
所以如果以后在对字符串做比较或者对某个字符串列做排序操作时没有得到想象中的结果，需要思考一下是不是比较规则的问题。

字符集和比较规则