Session 3 字符集和比较规则

ASCII字符集——共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符

ISO 8859-1字符集——共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符

GB2312字符集——收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母

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

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

1
2
'L' ->  01001100（十六进制：0x4C）
'啊' ->  111001011001010110001010（十六进制：0xE5958A）

查看数据库支持的字符集：

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

常见字符集及其最大长度：

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

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。MySQL定义了两个概念：

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

MySQL中utf8是utf8mb3的别名。

字符'我'在utf8字符集编码下的字节串长这样：0xE68891。但如果用其他字符集去解码，便可能得到“乱码”（被gbk字符集解释成一个字符'鎴'和半个字符）

比较规则是针对某个字符集中的字符比较大小的一种规则。每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则。

查看数据库支持的比较规则：

1
2
3
4
5
6
7
8
9
10
11
SHOW COLLATION [LIKE 匹配的模式];

SHOW COLLATION LIKE 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
...
后缀 英文释义 描述
_ai accent insensitive 不区分重音
_as accent sensitive 区分重音
_ci case insensitive 不区分大小写
_cs case sensitive 区分大小写
_bin binary 以二进制方式比较

比如utf8_general_ci，其名称以utf8开头表示与其关联的字符集为utf8，以ci结尾的说明不区分大小写。其Default字段值为Yes，表示它是utf8字符集的默认比较规则。

中间部分表示比较规则主要作用于哪种语言，比如utf8_icelandic_ci 主要用于冰岛语。

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

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

每一级都会默认沿用上一级的字符集和比较规则。

（一）服务器级别

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

系统变量 描述
character_set_server 服务器级别的字符集（默认utf8）
collation_server 服务器级别的比较规则（默认utf8_general_ci）

可通过启动选项、配置文件或SET命令修改。

（二）数据库级别

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，直接见例子：

1
2
3
4
5
6
mysql> CREATE DATABASE charset_demo_db
    -> CHARACTER SET gb2312
    -> COLLATE gb2312_chinese_ci;
mysql> ALTER DATABASE charset_demo_db
    -> CHARACTER SET gb2312
    -> COLLATE gb2312_chinese_ci;

可以通过两个系统变量查看当前USE的数据库使用的字符集和比较规则：

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

这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

（三）表级别

1
2
3
4
mysql> CREATE TABLE t(
    ->     col VARCHAR(10)
    -> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
/* ALTER 同理 */

（四）列级别

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

mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;

由于字符集和比较规则是互相有联系的，仅修改一方会引起另一方的同样变化

字符集的转换：如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串。从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

其默认值都是utf8（但在我的wsl上都是utf8mb4）。整个过程如图所示。

image-20230719170655160

服务器认为客户端发送过来的请求是用character_set_client编码的。假设你的客户端采用的字符集和character_set_client 不一样的话，会出现意想不到的情况。

服务器将把得到的结果集使用character_set_results编码后发送给客户端。假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况。

character_set_connection 字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用。

我们通常都把这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。

1
2
3
4
5
SET NAMES 字符集名;
/* 效果等同于 */
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;