字符集和排序规则

本文介绍了 TiDB 中支持的字符集和排序规则。

字符集和排序规则的概念

字符集 (character set) 是符号与编码的集合。TiDB 中的默认字符集是 utf8mb4,与 MySQL 8.0 及更高版本中的默认字符集匹配。

排序规则 (collation) 是在字符集中比较字符以及字符排序顺序的规则。例如,在二进制排序规则中,比较 Aa 的结果是不一样的:

  1. SET NAMES utf8mb4 COLLATE utf8mb4_bin;
  2. SELECT 'A' = 'a';
  3. SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
  4. SELECT 'A' = 'a';
  1. SELECT 'A' = 'a';
  1. +-----------+
  2. | 'A' = 'a' |
  3. +-----------+
  4. | 0 |
  5. +-----------+
  6. 1 row in set (0.00 sec)
  1. SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
  1. Query OK, 0 rows affected (0.00 sec)
  1. SELECT 'A' = 'a';
  1. +-----------+
  2. | 'A' = 'a' |
  3. +-----------+
  4. | 1 |
  5. +-----------+
  6. 1 row in set (0.00 sec)

支持的字符集和排序规则

目前 TiDB 支持以下字符集:

  1. SHOW CHARACTER SET;
  1. +---------+-------------------------------------+-------------------+--------+
  2. | Charset | Description | Default collation | Maxlen |
  3. +---------+-------------------------------------+-------------------+--------+
  4. | ascii | US ASCII | ascii_bin | 1 |
  5. | binary | binary | binary | 1 |
  6. | gbk | Chinese Internal Code Specification | gbk_bin | 2 |
  7. | latin1 | Latin1 | latin1_bin | 1 |
  8. | utf8 | UTF-8 Unicode | utf8_bin | 3 |
  9. | utf8mb4 | UTF-8 Unicode | utf8mb4_bin | 4 |
  10. +---------+-------------------------------------+-------------------+--------+
  11. 6 rows in set (0.00 sec)

TiDB 支持以下排序规则:

  1. SHOW COLLATION;
  1. +--------------------+---------+------+---------+----------+---------+
  2. | Collation | Charset | Id | Default | Compiled | Sortlen |
  3. +--------------------+---------+------+---------+----------+---------+
  4. | ascii_bin | ascii | 65 | Yes | Yes | 1 |
  5. | binary | binary | 63 | Yes | Yes | 1 |
  6. | gbk_bin | gbk | 87 | | Yes | 1 |
  7. | gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
  8. | latin1_bin | latin1 | 47 | Yes | Yes | 1 |
  9. | utf8_bin | utf8 | 83 | Yes | Yes | 1 |
  10. | utf8_general_ci | utf8 | 33 | | Yes | 1 |
  11. | utf8_unicode_ci | utf8 | 192 | | Yes | 1 |
  12. | utf8mb4_0900_ai_ci | utf8mb4 | 255 | | Yes | 1 |
  13. | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 |
  14. | utf8mb4_bin | utf8mb4 | 46 | Yes | Yes | 1 |
  15. | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 |
  16. | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 |
  17. +--------------------+---------+------+---------+----------+---------+
  18. 13 rows in set (0.00 sec)

概述 - 图1

警告

TiDB 会错误地将 latin1 视为 utf8 的子集。当用户存储不同于 latin1utf8 编码的字符时,可能会导致意外情况出现。因此强烈建议使用 utf8mb4 字符集。详情参阅 TiDB #18955

在 TiDB v7.5.0 中,如果查询条件中包含对字符串前缀的 LIKE 过滤, 比如 LIKE 'prefix%',并且该列被设置为非二进制排序规则(即排序规则的后缀不是以 _bin 结尾),那么 TiDB 优化器暂时无法把这个过滤条件转化为范围扫描 (Range Scan),而是用全量扫描代替。因此这类 SQL 有可能造成超出预期的资源消耗。从 v7.5.1 开始,TiDB 优化器移除该限制。

概述 - 图2

注意

TiDB 中的默认排序规则(后缀为 _bin 的二进制排序规则)与 MySQL 中的默认排序规则不同,后者通常是一般排序规则,后缀为 _general_ci_ai_ci。当用户指定了显式字符集,但依赖于待选的隐式默认排序规则时,这个差异可能导致兼容性问题。 在 TiDB 中,默认的排序规则也受到客户端的连接排序规则设置的影响。例如,MySQL 8.x 客户端默认使用 utf8mb4_0900_ai_ci 作为 utf8mb4 字符集的连接排序规则。

  • 在 TiDB v7.4.0 之前,如果客户端使用 utf8mb4_0900_ai_ci 作为连接排序规则,因为 TiDB 不支持 utf8mb4_0900_ai_ci 排序规则,TiDB 将回退到使用 TiDB 服务器默认的排序规则 utf8mb4_bin
  • 从 v7.4.0 开始,如果客户端使用 utf8mb4_0900_ai_ci 作为连接排序规则,TiDB 将遵循客户端的配置,使用 utf8mb4_0900_ai_ci 作为默认排序规则。

利用以下的语句可以查看字符集对应的排序规则(以下是新的排序规则框架)下的结果:

  1. SHOW COLLATION WHERE Charset = 'utf8mb4';
  1. +--------------------+---------+------+---------+----------+---------+
  2. | Collation | Charset | Id | Default | Compiled | Sortlen |
  3. +--------------------+---------+------+---------+----------+---------+
  4. | utf8mb4_0900_ai_ci | utf8mb4 | 255 | | Yes | 1 |
  5. | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 |
  6. | utf8mb4_bin | utf8mb4 | 46 | Yes | Yes | 1 |
  7. | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 |
  8. | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 1 |
  9. +--------------------+---------+------+---------+----------+---------+
  10. 5 rows in set (0.00 sec)

TiDB 对 GBK 字符集的支持详情见 GBK

TiDB 中的 utf8utf8mb4

MySQL 限制字符集 utf8 为最多 3 个字节。这足以存储在基本多语言平面 (BMP) 中的字符,但不足以存储表情符号 (emoji) 等字符。因此,建议改用字符集utf8mb4

默认情况下,TiDB 同样限制字符集 utf8 为最多 3 个字节,以确保 TiDB 中创建的数据可以在 MySQL 中顺利恢复。你可以禁用此功能,方法是将系统变量 tidb_check_mb4_value_in_utf8 的值更改为 OFF

以下示例演示了在表中插入 4 字节的表情符号字符(emoji 字符)时的默认行为。utf8 字符集下 INSERT 语句不能执行,utf8mb4 字符集下可以执行 INSERT 语句:

  1. CREATE TABLE utf8_test (
  2. c char(1) NOT NULL
  3. ) CHARACTER SET utf8;
  1. Query OK, 0 rows affected (0.09 sec)
  1. CREATE TABLE utf8m4_test (
  2. c char(1) NOT NULL
  3. ) CHARACTER SET utf8mb4;
  1. Query OK, 0 rows affected (0.09 sec)
  1. INSERT INTO utf8_test VALUES ('😉');
  1. ERROR 1366 (HY000): incorrect utf8 value f09f9889(😉) for column c
  1. INSERT INTO utf8m4_test VALUES ('😉');
  1. Query OK, 1 row affected (0.02 sec)
  1. SELECT char_length(c), length(c), c FROM utf8_test;
  1. Empty set (0.01 sec)
  1. SELECT char_length(c), length(c), c FROM utf8m4_test;
  1. +----------------+-----------+------+
  2. | char_length(c) | length(c) | c |
  3. +----------------+-----------+------+
  4. | 1 | 4 | 😉 |
  5. +----------------+-----------+------+
  6. 1 row in set (0.00 sec)

不同范围的字符集和排序规则

字符集和排序规则可以在设置在不同的层次。

数据库的字符集和排序规则

每个数据库都有相应的字符集和排序规则。数据库的字符集和排序规则可以通过以下语句来设置:

  1. CREATE DATABASE db_name
  2. [[DEFAULT] CHARACTER SET charset_name]
  3. [[DEFAULT] COLLATE collation_name]
  4. ALTER DATABASE db_name
  5. [[DEFAULT] CHARACTER SET charset_name]
  6. [[DEFAULT] COLLATE collation_name]

在这里 DATABASE 可以跟 SCHEMA 互换使用。

不同的数据库之间可以使用不一样的字符集和排序规则。

通过系统变量 character_set_databasecollation_database 可以查看到当前数据库的字符集以及排序规则:

  1. CREATE SCHEMA test1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  1. Query OK, 0 rows affected (0.09 sec)
  1. USE test1;
  1. Database changed
  1. SELECT @@character_set_database, @@collation_database;
  1. +--------------------------|----------------------+
  2. | @@character_set_database | @@collation_database |
  3. +--------------------------|----------------------+
  4. | utf8mb4 | utf8mb4_general_ci |
  5. +--------------------------|----------------------+
  6. 1 row in set (0.00 sec)
  1. CREATE SCHEMA test2 CHARACTER SET latin1 COLLATE latin1_bin;
  1. Query OK, 0 rows affected (0.09 sec)
  1. USE test2;
  1. Database changed
  1. SELECT @@character_set_database, @@collation_database;
  1. +--------------------------|----------------------+
  2. | @@character_set_database | @@collation_database |
  3. +--------------------------|----------------------+
  4. | latin1 | latin1_bin |
  5. +--------------------------|----------------------+
  6. 1 row in set (0.00 sec)

在 INFORMATION_SCHEMA 中也可以查看到这两个值:

  1. SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  2. FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

表的字符集和排序规则

表的字符集和排序规则可以通过以下语句来设置:

  1. CREATE TABLE tbl_name (column_list)
  2. [[DEFAULT] CHARACTER SET charset_name]
  3. [COLLATE collation_name]]
  4. ALTER TABLE tbl_name
  5. [[DEFAULT] CHARACTER SET charset_name]
  6. [COLLATE collation_name]

例如:

  1. CREATE TABLE t1(a int) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
  1. Query OK, 0 rows affected (0.08 sec)

如果表的字符集和排序规则没有设置,那么数据库的字符集和排序规则就作为其默认值。在仅指定字符集为 utf8mb4,但未设置排序规则时,排序规则为变量 default_collation_for_utf8mb4 指定的值。

列的字符集和排序规则

列的字符集和排序规则的语法如下:

  1. col_name {CHAR | VARCHAR | TEXT} (col_length)
  2. [CHARACTER SET charset_name]
  3. [COLLATE collation_name]
  4. col_name {ENUM | SET} (val_list)
  5. [CHARACTER SET charset_name]
  6. [COLLATE collation_name]

如果列的字符集和排序规则没有设置,那么表的字符集和排序规则就作为其默认值。在仅指定字符集为 utf8mb4,但未设置排序规则时,排序规则为变量 default_collation_for_utf8mb4 指定的值。

字符串的字符集和排序规则

每一个字符串都对应一个字符集和一个排序规则,在使用字符串时指此选项可选,如下:

  1. [_charset_name]'string' [COLLATE collation_name]

示例如下:

  1. SELECT 'string';
  2. SELECT _utf8mb4'string';
  3. SELECT _utf8mb4'string' COLLATE utf8mb4_general_ci;

规则如下:

  • 规则 1:如果指定了 CHARACTER SET charset_nameCOLLATE collation_name,则直接使用 charset_name 字符集和 collation_name 排序规则。
  • 规则 2:如果指定了 CHARACTER SET charset_name 且未指定 COLLATE collation_name,则使用 charset_name 字符集和 charset_name 对应的默认排序规则。
  • 规则 3:如果 CHARACTER SET charset_nameCOLLATE collation_name 都未指定,则使用 character_set_connectioncollation_connection 系统变量给出的字符集和排序规则。

客户端连接的字符集和排序规则

  • 服务器的字符集和排序规则可以通过系统变量 character_set_servercollation_server 获取。
  • 数据库的字符集和排序规则可以通过环境变量 character_set_databasecollation_database 获取。

对于每一个客户端的连接,也有相应的变量表示字符集和排序规则:character_set_connectioncollation_connection

character_set_client 代表客户端的字符集。在返回结果前,服务端会把结果根据 character_set_results 转换成对应的字符集,包括结果的元信息等。

可以用以下的语句来影响这些跟客户端相关的字符集变量:

  • SET NAMES 'charset_name' [COLLATE 'collation_name']

    SET NAMES 用来设定客户端会在之后的请求中使用的字符集。SET NAMES utf8mb4 表示客户端会在接下来的请求中,都使用 utf8mb4 字符集。服务端也会在之后返回结果的时候使用 utf8mb4 字符集。SET NAMES 'charset_name' 语句其实等于下面语句的组合:

    1. SET character_set_client = charset_name;
    2. SET character_set_results = charset_name;
    3. SET character_set_connection = charset_name;

    COLLATE 是可选的,如果没有提供,将会用 charset_name 对应的默认排序规则设置 collation_connection

  • SET CHARACTER SET 'charset_name'

    SET NAMES 类似,等价于下面语句的组合:

    1. SET character_set_client = charset_name;
    2. SET character_set_results = charset_name;
    3. SET charset_connection = @@charset_database;
    4. SET collation_connection = @@collation_database;

服务器、数据库、表、列、字符串的字符集和排序规则的优先级

优先级从高到低排列顺序为:

字符串 > 列 > 表 > 数据库 > 服务器

字符集和排序规则的通用选择规则

  • 规则 1:如果指定了 CHARACTER SET charset_nameCOLLATE collation_name,则直接使用 charset_name 字符集和 collation_name 排序规则。
  • 规则 2:如果指定了 CHARACTER SET charset_name 且未指定 COLLATE collation_name,则使用 charset_name 字符集和 charset_name 对应的默认排序规则。
  • 规则 3:如果 CHARACTER SET charset_nameCOLLATE collation_name 都未指定,则使用更高优先级的字符集和排序规则。

字符合法性检查

当指定的字符集为 utf8 或 utf8mb4 时,TiDB 仅支持合法的 utf8 字符。对于不合法的字符,会报错:incorrect utf8 value。该字符合法性检查与 MySQL 8.0 兼容,与 MySQL 5.7 及以下版本不兼容。

如果不希望报错,可以通过 set @@tidb_skip_utf8_check=1; 跳过字符检查。

概述 - 图3

注意

跳过字符检查可能会使 TiDB 检测不到应用写入的非法 UTF-8 字符,进一步导致执行 ANALYZE 时解码错误,以及引入其他未知的编码问题。如果应用不能保证写入字符串的合法性,不建议跳过该检查。

排序规则支持

排序规则的语法支持和语义支持受到配置项 new_collations_enabled_on_first_bootstrap 的影响。这里语法支持和语义支持有所区别。语法支持是指 TiDB 能够解析和设置排序规则;而语义支持是指 TiDB 能够在比较字符串时正确地使用排序规则。

在 4.0 版本之前,TiDB 只提供了旧的排序规则框架,能够在语法上支持的绝大部分 MySQL 排序规则,但语义上所有的排序规则都当成二进制排序规则。

4.0 版本中,TiDB 增加了新的排序规则框架用于在语义上支持不同的排序规则,保证字符串比较时严格遵循对应的排序规则,详情请见下文。

旧框架下的排序规则支持

在 4.0 版本之前,TiDB 中可以指定大部分 MySQL 中的排序规则,并把这些排序规则按照默认排序规则处理,即以编码字节序为字符定序。和 MySQL 不同的是,TiDB 不会处理字符末尾的空格,因此会造成以下的行为区别:

  1. CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);
  1. Query OK, 0 rows affected
  1. INSERT INTO t VALUES ('A');
  1. Query OK, 1 row affected
  1. INSERT INTO t VALUES ('a');
  1. Query OK, 1 row affected

以上语句,在 TiDB 会执行成功,而在 MySQL 中,由于 utf8mb4_general_ci 大小写不敏感,报错 Duplicate entry 'a'

  1. INSERT INTO t VALUES ('a ');
  1. Query OK, 1 row affected

以上语句,在 TiDB 会执行成功,而在 MySQL 中,由于补齐空格比较,报错 Duplicate entry 'a '

新框架下的排序规则支持

TiDB 4.0 新增了完整的排序规则支持框架,从语义上支持了排序规则,并新增了配置开关 new_collations_enabled_on_first_bootstrap,在集群初次初始化时决定是否启用新排序规则框架。如需启用新排序规则框架,可将 new_collations_enabled_on_first_bootstrap 的值设为 true,详情参见 new_collations_enabled_on_first_bootstrap

对于一个已经初始化完成的 TiDB 集群,可以通过 mysql.tidb 表中的 new_collation_enabled 变量确认是否启用了新排序规则框架。

概述 - 图4

注意

mysql.tidb 表查询结果和 new_collations_enabled_on_first_bootstrap 的值不同时,以 mysql.tidb 表的结果为准。

  1. SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME='new_collation_enabled';
  1. +----------------+
  2. | VARIABLE_VALUE |
  3. +----------------+
  4. | True |
  5. +----------------+
  6. 1 row in set (0.00 sec)

在新的排序规则框架下,TiDB 能够支持 utf8_general_ciutf8mb4_general_ciutf8_unicode_ciutf8mb4_unicode_ciutf8mb4_0900_binutf8mb4_0900_ai_cigbk_chinese_cigbk_bin 这几种排序规则,与 MySQL 兼容。

使用 utf8_general_ciutf8mb4_general_ciutf8_unicode_ciutf8mb4_unicode_ciutf8mb4_0900_ai_cigbk_chinese_ci 中任一种时,字符串之间的比较是大小写不敏感 (case-insensitive) 和口音不敏感 (accent-insensitive) 的。同时,TiDB 还修正了排序规则的 PADDING 行为:

  1. CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);
  1. Query OK, 0 rows affected (0.00 sec)
  1. INSERT INTO t VALUES ('A');
  1. Query OK, 1 row affected (0.00 sec)
  1. INSERT INTO t VALUES ('a');
  1. ERROR 1062 (23000): Duplicate entry 'a' for key 't.PRIMARY'

TiDB 兼容了 MySQL 的 case insensitive collation。

  1. INSERT INTO t VALUES ('a ');
  1. ERROR 1062 (23000): Duplicate entry 'a ' for key 't.PRIMARY'

TiDB 修正了 PADDING 行为,与 MySQL 兼容。

概述 - 图5

注意

TiDB 中 padding 的实现方式与 MySQL 的不同。在 MySQL 中,padding 是通过补齐空格实现的。而在 TiDB 中 padding 是通过裁剪掉末尾的空格来实现的。两种做法在绝大多数情况下是一致的,唯一的例外是字符串尾部包含小于空格 (0x20) 的字符时,例如 'a' < 'a\t' 在 TiDB 中的结果为 1,而在 MySQL 中,其等价于 'a ' < 'a\t',结果为 0

表达式中排序规则的 Coercibility 值

如果一个表达式涉及多个不同排序规则的子表达式时,需要对计算时用的排序规则进行推断,规则如下:

  • 显式 COLLATE 子句的 coercibility 值为 0
  • 如果两个字符串的排序规则不兼容,这两个字符串 concat 结果的 coercibility 值为 1
  • 列或者 CAST()CONVERT()BINARY() 的排序规则的 coercibility 值为 2
  • 系统常量(USER() 或者 VERSION() 返回的字符串)的 coercibility 值为 3
  • 常量的 coercibility 值为 4
  • 数字或者中间变量的 coercibility 值为 5
  • NULL 或者由 NULL 派生出的表达式的 coercibility 值为 6

在推断排序规则时,TiDB 优先使用 coercibility 值较低的表达式的排序规则。如果 coercibility 值相同,则按以下优先级确定排序规则:

binary > utf8mb4_bin > (utf8mb4_general_ci = utf8mb4_unicode_ci) > utf8_bin > (utf8_general_ci = utf8_unicode_ci) > latin1_bin > ascii_bin

以下情况 TiDB 无法推断排序规则并报错:

  • 如果两个子表达式的排序规则不相同,而且表达式的 coercibility 值都为 0
  • 如果两个子表达式的排序规则不兼容,而且表达式的返回类型为 String 类。

COLLATE 子句

TiDB 支持使用 COLLATE 子句来指定一个表达式的排序规则,该表达式的 coercibility 值为 0,具有最高的优先级。示例如下:

  1. SELECT 'a' = _utf8mb4 'A' collate utf8mb4_general_ci;
  1. +-----------------------------------------------+
  2. | 'a' = _utf8mb4 'A' collate utf8mb4_general_ci |
  3. +-----------------------------------------------+
  4. | 1 |
  5. +-----------------------------------------------+
  6. 1 row in set (0.00 sec)

更多细节,参考 Connection Character Sets and Collations