MySQL 字符集和比较规则

我们常用的字符集 UTF-8，采用变长编码方式，在使用时需要用 1~4 个字节，但是使用的绝大多数字符只需要 1~3 个字节，而 MySQL 为了最大程度的提升系统的存储和性能，设计了两套 UTF-8 字符集：

  • utf8mb3：只使用 1~3 个字节表示字符。
  • utf8mb4：正统 UTF-8，采用 1~4 个字节表示字符。

MySQL 中 utf8 是 utf8mb3 的别名，如果有使用 4 字节的情况，例如一些 emoji 表情，手动指定 utf8mb4。而在 MySQL 8.0 中，对 utf8mb4 最大程度的进行了优化，提升其存储和性能，并且将 utf8mb4 设置为默认字符集。

1
2
# 查看字符集
SHOW CHARSET LIKE '匹配的模式';

不同的字符集采用的字节长度不同，以及囊括的字符也不同，也可能存在同一个字符采用了不同的编码方式，比如对于汉字“我”来说， ASCII 字符集，只有 128 个字符，根本没有收录这个字符，而 UTF-8 和 GB2312 字符对汉字“我”的编码方式如下：

  • UTF-8：111001101000100010010001（3 字节，十六进制 0xE68891）
  • GB2312：1100111011010010（2 字节，十六进制 0xCED2）
1
2
# 查看比较规则
SHOW COLLATION LIKE 匹配的模式

字符集和比较规则的应用

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

服务器级别

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

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

通过以下 SQL 可以查看这两个系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+

SHOW VARIABLES LIKE 'collation_server';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| collation_server | utf8mb4_unicode_ci |
+------------------+--------------------+

这里我用的是 MySQL 8，默认的字符集是 utf8mb4，默认的比较规则是 utf8mb4_unicode_ci。

在启动服务器程序时，可以通过启动选项或在服务器程序运行时使用 SET 语句来修改这两个变量值。

比如在配置文件中配置：

1
2
3
[server]
character_set_server=gb2312
collation_server=gb2312_chinese_ci

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

数据库级别

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

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

当创建完数据库或者修改完数据库的字符集和比较规则后，通过查看以下两个系统变量的值是否修改成功（前提是需要使用 USE 语句选择对应数据库，反正则变量与服务器级别下对应的系统变量具有相同的值）。

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

通过以下 SQL 语句可以查询这两个系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
SHOW VARIABLES LIKE 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+

SHOW VARIABLES LIKE 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_unicode_ci |
+--------------------+--------------------+

但我们创建数据库时，大部分场景下不指定字符集和比较规则，此时数据库的字符集和比较规则将使用服务器级别的字符集和比较规则。

表级别

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

1
2
3
4
5
6
7
CREATE TABLE 表名(
	列信息
) [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

ALTER TABLE 表名(
	列信息
) [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

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

列级别

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

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

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

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

在修改列的字符集时需要注意，如果列中存储的数据不能用修改后的字符集进行表示，则会发生错误。比如，列最初使用的字符集是 utf8，列中存储了一些汉字，现在把列的字符集转换为 ASCII 的话就会出错，因为 ASCII 字符集并不能表示汉字字符。

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

由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化；如果只修改比较规则，字符集也会跟着变化。具体规则如下：

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

各级别字符集和比较规则小结

  • 如果创建或修改列时没有显式指定字符集和比较规则，则该列默认使用表的字符集和比较规则；
  • 如果创建表时没有显式指定字符集和比较规则，则该表默认使用数据库的字符集和比较规则；
  • 如果创建数据库时没有显式指定字符集和比较规则，则该数据库默认使用服务器的字符集和比较规则。

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

从发送请求到接收响应的过程中发生的字符集转换如下所示：

  • 客户端发迭的请求字节序列是采用哪种字符线进行编码的？
    这一步骤主要取决于操作系统当前使用的字符集，对于Windows操作系统来说，还与客户端启动时设置的default-character-set启动选项有关。
  • 服务器接收到请求字节序列后会认为它是采用哪种字符？
    这一步骤取决于系统变量character_set_ client的值。
  • 服务器在运行过程中会把请求的字节序列转换为以哪种字符集编码的字节序列？
    这一步骤取决于系统变盘character_set_ connection的值。
  • 服务器在向客户端返回字节序列时，是采用哪种字符集进行编码的？
    这一步骤取决于系统变量character_ set_results的值。
  • 客户端在收到响应字节序列后，是怎么把它们写到控制台中的？
    这一步骤主要取决于操作系统当前使用的字符集，对于Windows操作系统来说，还与客户端启动时设置的default-character-set启动选项有关。

在这个过程中，各个系统变量的含义如下表所示：

系统变量描述
character_set_client服务器认为请求是按照该系统变量指定的字符集进行编码的
character_set_connection服务器在处理请求时，会把请求字节序列从 character_set_client 转换为 character_set_connection
character_set_results服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码