小刘
2022-12-05
目录

字符集和比较规则

# 字符集和比较规则

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

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

# 1. 一些重要的字符集

  • ASCII：共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。使用1个字节来进行编码。

  • ISO 8859-1：共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，使用1个字节来进行编码。别名latin1。

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

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

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

  • utf8：收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。

# 2. MySQL中支持的字符集和排序规则

# 2.1. MySQL中的utf8和utf8mb4

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

  • utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

# 2.2. 字符集的查看

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

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

其中CHARACTER SET和CHARSET是同义词，用任意一个都可以。我们查询一下（支持的字符集太多了，我们省略了一些）：

mysql> SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
...
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
...
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
...
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
...
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
...
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
...
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
...
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
...
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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

常用到的字符集的Maxlen：

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

# 2.3. 比较规则的查看

查看MySQL中支持的比较规则的命令如下：

SHOW COLLATION [LIKE 匹配的模式];
1

查看一下utf8字符集下的比较规则：

mysql> 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 |
| utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
| utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
| utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
| utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
| utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

这些比较规则的命名还挺有规律的，具体规律如下：

  • 比较规则名称：以与其关联的字符集的名称开头。后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_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的就是该字符集的默认比较规则。

# 3. 字符集和比较规则的应用

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

# 3.1. 服务器级别

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

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

我们看一下这两个系统变量的值：

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

可以看到在我的计算机中服务器级别默认的字符集是utf8，默认的比较规则是utf8_general_ci。

通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值：

[server]
character_set_server=gbk
collation_server=gbk_chinese_ci
1
2
3

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

# 3.2. 数据库级别

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

-- 创建数据库
CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
-- 修改数据库
ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
1
2
3
4
5
6
7
8

其中的DEFAULT可以省略，并不影响语句的语义。

比方说我们新创建一个名叫charset_demo_db的数据库，在创建的时候指定它使用的字符集为gb2312，比较规则为gb2312_chinese_ci：

mysql> CREATE DATABASE charset_demo_db
    -> CHARACTER SET gb2312
    -> COLLATE gb2312_chinese_ci;
Query OK, 1 row affected (0.01 sec)
1
2
3
4

如果想查看当前数据库使用的字符集和比较规则，可以查看下面两个系统变量的值（前提是使用USE语句选择当前默认数据库，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值）：

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

我们来查看一下刚刚创建的charset_demo_db数据库的字符集和比较规则：

mysql> USE charset_demo_db;
Database changed
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | gb2312 |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | gb2312_chinese_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

可以看到这个charset_demo_db数据库的字符集和比较规则就是我们在创建语句中指定的。需要注意的一点是： character_set_database 和 collation_database 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

数据库的创建语句中也可以不指定字符集和比较规则，比如这样：

CREATE DATABASE 数据库名;
1

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

# 3.3. 表级别

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

--创建表
CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]
--修改表
ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]
1
2
3
4
5
6
7
8

比方说我们在刚刚创建的charset_demo_db数据库中创建一个名为t的表，并指定这个表的字符集和比较规则：

CREATE TABLE t(
    col VARCHAR(10)
) CHARACTER SET utf8 COLLATE utf8_general_ci;
1
2
3

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

# 3.4. 列级别

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

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

修改一下表t中列col的字符集和比较规则可以这么写：

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

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。

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

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

不论哪个级别的字符集和比较规则，这两条规则都适用，我们以服务器级别的字符集和比较规则为例来看一下详细过程：

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

    mysql> SET character_set_server = gb2312;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | gb2312 |
+----------------------+--------+
1 row in set (0.00 sec)
mysql>  SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | gb2312_chinese_ci |
+------------------+-------------------+
1 row in set (0.00 sec)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16

    我们只修改了character_set_server的值为gb2312，collation_server的值自动变为了gb2312_chinese_ci。

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

    mysql> SET collation_server = utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-----------------+
| Variable_name    | Value           |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16

    我们只修改了collation_server的值为utf8_general_ci，character_set_server的值自动变为了utf8。

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

# 4.1. 字符集转换的概念

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

# 4.2. MySQL中字符集的转换

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

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

这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

mysql> SHOW VARIABLES LIKE 'character_set_client';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_client | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_connection';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| character_set_connection | utf8  |
+--------------------------+-------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'character_set_results';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| character_set_results | utf8  |
+-----------------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

大家可以看到这几个系统变量的值都是utf8，为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

set character_set_connection = gbk;
1

所以现在系统变量character_set_client和character_set_results的值还是utf8，而character_set_connection的值为gbk。现在假设我们客户端发送的请求是下边这个字符串：

SELECT * FROM t WHERE s = '我';
1

为了方便大家理解这个过程，我们只分析字符'我'在这个过程中字符集的转换。

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

  1. 客户端发送请求所使用的字符集：类Unix系统使用的是utf8，Windows使用的是gbk。

  2. 服务器认为这串字节采用的字符集是chacharacter_set_client，然后把这串字节转换为character_set_connection字符集编码的字节。

  3. 因为表t的列col采用的是gbk字符集，与character_set_connection一致，所以直接到列中找字节值为0xCED2的记录，最后找到了一条。

  4. 服务器将这个字符串从character_set_connection字符集转换成character_set_results字符集，然后把转换后的字节串返回到客户端。

  5. 由于客户端是用的字符集是utf8，所以可以顺利的将0xE68891解释成字符我，从而显示到我们的显示器上。

通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：

SET NAMES 字符集名;
# 等同于
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;
1
2
3
4
5

比方说我的客户端使用的是utf8字符集，所以需要把这几个系统变量的值都设置为utf8，如果你使用的是Windows系统，那应该设置成gbk：

SET NAMES utf8;
1

启动客户端的时候指定一个叫default-character-set的启动选项：

[client]
default-character-set=utf8
1
2

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

# 4.3. 比较规则的应用

结束了字符集的漫游，我们把视角再次聚焦到比较规则，比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为排序规则。比方说表t的列col使用的字符集是gbk，使用的比较规则是gbk_chinese_ci，我们向里边插入几条记录：

INSERT INTO t(col) VALUES('a'), ('b'), ('A'), ('B');
1

我们查询的时候按照t列排序一下：

mysql> SELECT * FROM t ORDER BY col;
+------+
| col  |
+------+
| a    |
| A    |
| b    |
| B    |
| 我   |
+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

可以看到在默认的比较规则gbk_chinese_ci中是不区分大小写的，我们现在把列col的比较规则修改为gbk_bin：

ALTER TABLE t MODIFY s VARCHAR(10) COLLATE gbk_bin;
1

由于gbk_bin是不区分大小写而直接比较字符的编码，我们再看一下排序后的查询结果：

mysql> SELECT * FROM t ORDER BY s;
+------+
| s    |
+------+
| A    |
| B    |
| a    |
| b    |
| 我   |
+------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

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

上次更新: 2024/05/11, 03:55:33

← InnoDB记录存储结构-行→

最近更新
01
kubernetes控制器-Service
08-18
02
kubernetes控制器-Deployment
08-08
03
kubernetes调度基础
07-27
更多文章>
Theme by Vdoing | Copyright © 2023-2024 本站支持IPv6访问 本站支持SSL安全访问