字符集和排序规则

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

    排序规则 (collation) 是在字符集中比较字符以及字符排序顺序的规则。例如，在二进制排序规则中，比较“A”和“a”的结果是不一样的：

    1. mysql> SELECT 'A' = 'a';
    2. +-----------+
    3. | 'A' = 'a' |
    4. +-----------+
    5. |         0 |
    6. +-----------+
    7. 1 row in set (0.00 sec)
    9. mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
    10. Query OK, 0 rows affected (0.00 sec)
    12. mysql> SELECT 'A' = 'a';
    13. +-----------+
    14. | 'A' = 'a' |
    15. +-----------+
    16. |         1 |
    17. +-----------+
    18. 1 row in set (0.00 sec)

    TiDB 默认使用二进制排序规则。这一点与 MySQL 不同，MySQL 默认使用不区分大小写的排序规则。

    支持的字符集和排序规则

    目前 TiDB 支持以下字符集：

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

    TiDB 支持以下排序规则：

    1. mysql> show collation;
    2. +-------------+---------+------+---------+----------+---------+
    3. | Collation   | Charset | Id   | Default | Compiled | Sortlen |
    4. +-------------+---------+------+---------+----------+---------+
    5. | utf8mb4_bin | utf8mb4 |   46 | Yes     | Yes      |       1 |
    6. | latin1_bin  | latin1  |   47 | Yes     | Yes      |       1 |
    7. | binary      | binary  |   63 | Yes     | Yes      |       1 |
    8. | ascii_bin   | ascii   |   65 | Yes     | Yes      |       1 |
    9. | utf8_bin    | utf8    |   83 | Yes     | Yes      |       1 |
    10. +-------------+---------+------+---------+----------+---------+
    11. 5 rows in set (0.01 sec)

    利用以下的语句可以查看字符集对应的排序规则（以下是）下的结果：

    1. SHOW COLLATION WHERE Charset = 'utf8mb4';
    1. +--------------------+---------+------+---------+----------+---------+
    2. | Collation          | Charset | Id   | Default | Compiled | Sortlen |
    3. +--------------------+---------+------+---------+----------+---------+
    4. | utf8mb4_bin        | utf8mb4 |   46 | Yes     | Yes      |       1 |
    5. | utf8mb4_general_ci | utf8mb4 |   45 |         | Yes      |       1 |
    6. 2 rows in set (0.00 sec)

    TiDB 中的 utf8 和 utf8mb4

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

    默认情况下，TiDB 同样限制字符集 utf8 为最多 3 个字节，以确保 TiDB 中创建的数据可以在 MySQL 中顺利恢复。你可以禁用此功能，方法是在 TiDB 配置文件中将 check-mb4-value-in-utf8 的值更改为 FALSE。

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

    1. mysql> CREATE TABLE utf8_test (
    2.     ->  c char(1) NOT NULL
    3.     -> ) CHARACTER SET utf8;
    4. Query OK, 0 rows affected (0.09 sec)
    6. mysql> CREATE TABLE utf8m4_test (
    7.     ->  c char(1) NOT NULL
    8.     -> ) CHARACTER SET utf8mb4;
    9. Query OK, 0 rows affected (0.09 sec)
    11. mysql> INSERT INTO utf8_test VALUES ('😉');
    12. ERROR 1366 (HY000): incorrect utf8 value f09f9889(😉) for column c
    13. mysql> INSERT INTO utf8m4_test VALUES ('😉');
    14. Query OK, 1 row affected (0.02 sec)
    16. mysql> SELECT char_length(c), length(c), c FROM utf8_test;
    17. Empty set (0.01 sec)
    19. mysql> SELECT char_length(c), length(c), c FROM utf8m4_test;
    20. +----------------+-----------+------+
    21. | char_length(c) | length(c) | c    |
    22. +----------------+-----------+------+
    23. |              1 |         4 | 😉     |
    24. +----------------+-----------+------+
    25. 1 row in set (0.00 sec)

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

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

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

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

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

    1. CREATE SCHEMA test1 CHARACTER SET utf8mb4 COLLATE uft8mb4_general_ci;
    1. Query OK, 0 rows affected (0.09 sec)
    1. USE test1;
    1. SELECT @@character_set_database, @@collation_database;
    1. +--------------------------|----------------------+
    2. | @@character_set_database | @@collation_database |
    3. +--------------------------|----------------------+
    4. | utf8mb4                  | uft8mb4_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]]
    5. ALTER TABLE tbl_name
    6.     [[DEFAULT] CHARACTER SET charset_name]
    7.     [COLLATE collation_name]

    例如：

      如果表的字符集和排序规则没有设置，那么数据库的字符集和排序规则就作为其默认值。

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

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

      如果列的字符集和排序规则没有设置，那么表的字符集和排序规则就作为其默认值。

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

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

      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_name 和 COLLATE collation_name，则直接使用 charset_name 字符集和 collation_name 排序规则。
      • 规则 2：如果指定了 CHARACTER SET charset_name 且未指定 COLLATE collation_name，则使用 charset_name 字符集和 charset_name 对应的默认排序规则。
      • 规则 3：如果 CHARACTER SET charset_name 和 COLLATE collation_name 都未指定，则使用 character_set_connection 和 collation_connection 系统变量给出的字符集和排序规则。
      • 服务器的字符集和排序规则可以通过系统变量 character_set_server 和 collation_server 获取。
      • 数据库的字符集和排序规则可以通过环境变量 character_set_database 和 collation_database 获取。

      对于每一个客户端的连接，也有相应的变量表示字符集和排序规则：character_set_connection 和 collation_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;
        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_name 和 COLLATE collation_name，则直接使用 charset_name 字符集和 collation_name 排序规则。
      • 规则 2：如果指定了 CHARACTER SET charset_name 且未指定 COLLATE collation_name，则使用 charset_name 字符集和 charset_name 对应的默认排序规则。
      • 规则 3：如果 CHARACTER SET charset_name 和 COLLATE collation_name 都未指定，则使用更高优先级的字符集和排序规则。

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

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

      排序规则支持

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

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

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

      旧框架下的排序规则支持

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

      1. CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);
      2. Query OK, 0 rows affected
      3. INSERT INTO t VALUES ('A');
      4. Query OK, 1 row affected
      5. INSERT INTO t VALUES ('a');
      6. Query OK, 1 row affected # TiDB 会执行成功，而在 MySQL 中，则由于 utf8mb4_general_ci 大小写不敏感，报错 Duplicate entry 'a'。
      7. INSERT INTO t VALUES ('a ');
      8. Query OK, 1 row affected # TiDB 会执行成功，而在 MySQL 中，则由于补齐空格比较，报错 Duplicate entry 'a '。

      TiDB 4.0 新增了完整的排序规则支持框架，从语义上支持了排序规则，并新增了配置开关 ，在集群初次初始化时决定是否启用新排序规则框架。在该配置开关打开之后初始化集群，可以通过 mysql.tidb 表中的 new_collation_enabled 变量确认是否启用新排序规则框架：

      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_ci 和 utf8mb4_general_ci 这两种排序规则，与 MySQL 兼容。

      使用 utf8_general_ci 或者 utf8mb4_general_ci 时，字符串之间的比较是大小写不敏感 (case-insensitive) 和口音不敏感 (accent-insensitive) 的。同时，TiDB 还修正了排序规则的 PADDING 行为：

      1. CREATE TABLE t(a varchar(20) charset utf8mb4 collate utf8mb4_general_ci PRIMARY KEY);
      2. Query OK, 0 rows affected (0.00 sec)
      3. INSERT INTO t VALUES ('A');
      4. Query OK, 1 row affected (0.00 sec)
      5. INSERT INTO t VALUES ('a');
      6. ERROR 1062 (23000): Duplicate entry 'a' for key 'PRIMARY' # TiDB 兼容了 MySQL 的 case insensitive collation。
      7. INSERT INTO t VALUES ('a ');
      8. ERROR 1062 (23000): Duplicate entry 'a ' for key 'PRIMARY' # TiDB 修正了 `PADDING` 行为，与 MySQL 兼容。

      注意：

      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 > utf8_bin > utf8_general_ci > latin1_bin > ascii_bin

      如果两个子表达式的排序规则不相同，而且表达式的 coercibility 值都为 0 时，TiDB 无法推断排序规则并报错。

      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 |