MySQL字符集与比较规则

MySQL数据库支持非常多的字符集，可以通过以下命令进行查看

SHOW CHARSET;

执行查询，查看结果

Charset	Description	Default collation	Maxlen
armscii8	ARMSCII-8 Armenian	armscii8_general_ci	1
ascii	US ASCII	ascii_general_ci	1
...	...	...	...
utf8	UTF-8 Unicode	utf8_general_ci	3
utf8mb4	UTF-8 Unicode	utf8mb4_0900_ai_ci	4
...	..	...	...

UTF8与UTF8MB4

在现实开发过程中，最常用的字符集应该就是UTF8了。UTF8字符集兼容ascii字符集，使用1~4个字节来表示一个文字，是一种变长的编码格式。

MySQL的UTF8字符集实际上是UTF8MB3，其最多使用3个字节来描述一个文字（见上方表格Maxlen列）。这也就意味着，部分4字节的文本不能使用UTF8字符集来存储。这一部分文本的代表是emoji表情。而要存储这些文本则需要使用UTF8MB4字符集。

Collation

每一个字符集都对应有一个默认的排序规则，见表格中Default collation列。

实际上，每个字符集都有对应的一组排序规则可供使用，以UTF8字符集为例，可以使用以下命令进行查看

SHOW COLLATION LIKE 'UTF8%';

执行命令，查看结果

Collation	Charset	id	Default	Compiled	Sorten	Pad_attribute
utf8mb4_0900_ai_ci	utf8mb4	255	Yes	Yes	0	NO PAD
utf8mb4_0900_as_ci	utf8mb4	305		Yes	0	NO PAD
utf8mb4_0900_as_cs	utf8mb4	278		Yes	0	NO PAD
...	...	...	...	...	...	...
utf8mb4_general_ci	utf8mb4	45		Yes	1	PAD SPACE
...	...	...	...	...	...	...

排序集的命名非常有规律，根据其后缀名：

• _ai: 不区分重音；
• _as: 区分重音；
• _ci: 不区分大小写；
• _cs: 区分大小写；
• _bin: 以二进制的方式进行比较；

字符集和排序规则的设置

MySQL可以在4个地方指定字符集和排序规则，也是对应了4个级别。

服务器级别

可以在MySQL配置文件上进行设置：

[server]
character_set_server=UTF8MB4
collation_server=UTF8MB4_GENERAL_CI

数据库级别

可以在创建数据库的时候进行指定：

CREATE DATABASE IF NOT EXISTS MY_DATABASE CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

表级别

可以在创建表的时候进行指定：

CREATE TABLE IF NOT EXISTS STUDENT(
  ID BIGINT PRIMARY KEY AUTO_INCREMENT,
  ...
) CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI;

字段级别

表中的不同字段可以指定不同的字符集和排序规则，甚至完全不同于表级别的：

CREATE TABLE IF NOT EXISTS TEACHER(
  ID BIGINT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20) CHARSET UTF8MB4 COLLATE UTF8MB4_GENERAL_CI NOT NULL,
  CODE VARCHAR(20) CHARSET ASCII COLLATE ASCII_GENERAL_CI NOT NULL UNIQUE
) CHARSET GBK COLLATE GBK_BIN;

MySQL中，字符集合排序规则的级别越小，其优先级越大。就是说，从优先级的角度来看：

字段级别 > 表级别 > 数据库级别 > 服务器级别

此外，MySQL也支持单独设置字符集或是单独设置排序规则。

当单独设置字符集时，会隐式的将排序规则修改为指定字符集的默认排序排序规则，如：

# 通过SQL设置服务器级别字符集
SET character_set_server = UTF8MB4; 
# 实际上也隐式的设置了 collation_server = UTF8MB4_0900_AI_CI

单独设置排序规则时，也会隐式的将字符集设置为指定排序规则所对应的字符集，如：

# 通过SQL设置服务器级别排序规则
SET collation_server = UTF8MB4_GENERAL_CI;
# 实际上也隐式的设置了 character_set_server = UTF8MB4

通信字符集

MySQL提供了3个主要的参数用来设置客户端与服务端通信过程中的字符集：

• character_set_client: 表示客户端传输数据所使用的字符集;
• character_set_connection: 服务器端处理本次连接所使用的字符集；
• character_set_results: 服务端向客户端响应数据时所使用的字符集；

MySQL服务器接收到SQL请求时，会假定客户端所传输的数据都是以character_set_client参数所指定的字符集进行编码的。在将请求的二进制字节接收完毕之后，会以character_set_client所指定的字符集对二进制字节数据进行解码，并使用character_set_connection参数所指定的字符集对解码后的数据再次进行编码，用于执行后续的查询或插入等操作。其中若character_set_connection和SQL语句所操作的列的字符集不一致时，会再次执行解码和编码的操作。当SQL语句执行完毕后，会将结果以character_set_results所指定的字符集进行编码并响应回客户端。

为了避免这个过程中出现的大量编码解码操作，一般会通过以下语句将这3个参数都设置为一个统一的值：

SET NAMES [字符集名称]
# 如：SET NAMES UTF8MB4

JDBC连接参数

在Java工程中使用JDBC来连接MySQL时，往往会使用characterEncoding参数来指定字符集：

dataSource.url=jdbc:mysql://127.0.0.1:3306/MY_DATABASE?characterEncoding=UTF8

根据上文可知，MySQL实际上是根据character_set_client属性的值来作为客户端传输数据的字符集。那么JDBC连接参数characterEncoding和character_set_client有什么关系呢？

可以参考官方文档

characterEncoding: Instructs the server to set session system variables 'character_set_client' and 'character_set_connection' to the default character set for the specified Java encoding and set 'collation_connection' to the default collation for this character set.

大意指characterEncoding参数会影响到本次会话中character_set_client和character_set_connection参数的值。

不过这里有一个重点需要指出，characterEncoding参数要求设置的是Java encoding，而在Java中是没有UTF8MB4字符集的。因此当我们设置characterEncoding=UTF8MB4时，就会出现一个SQLException，指明Unsupported character encoding 'utf8mb4' 。

我们可以把characterEncoding设置为UTF8，并通过以下代码进行试验，判断MySQL会将其识别为UTF8还是UTF8MB4：

Class.forName("com.mysql.cj.jdbc.Driver"); // 注册数据库驱动

Connection connection = DriverManager.getConnection(
    "jdbc:mysql://127.0.0.1:3306/PLANTATION?characterEncoding=UTF8", "root", "root"); // 建立数据库连接

PreparedStatement stat = connection.prepareStatement("show variables like 'character_set_client'");

ResultSet rs = stat.executeQuery();

while (rs.next()) {
  System.out.println(rs.getString(2));
}

执行代码，可以在控制台上看到输出结果为UTF8MB4。

另外，根据官方文档的介绍，Connector/J在8.0.26或更高版本中，将默认使用UTF8MB4：

For Connector/J 8.0.26 and later, the driver will use "utf8mb4".

因此在使用MySQL8的时候，也可以不去手动指定characterEncoding参数了。