[转帖]一文理清 TiDB 与 MySQL 中的常用字符集及排序规则

1.1. 字符集与编码规则

character setCode Point（码点）Code Point

1.2. 常见的字符集与编码规则

1. GB2312

GB2312 是 1980 年发布的中国国家标准的简体中文字符的集合与每个汉字的编码方式。也就是说 GB2312 即是字符集，又是编码规则。

1. GBK

GBK 是对 GB2312 的补充，完全兼容 GB2312。同 GB2312 一样， GBK 也即是字符集，又是编码规则。

1. Unicode（Universal Multiple-Octet Coded Character Set）

Unicode 是由名为 Unicode 的学术机构推出的字符集，其目的是收录人类目前已知在用的所有字符, 并为它们进行统一的分类和编号。但是， Unicode 只负责给字符编号，却不负责具体的编码规则。 也就是说 Unicode 只是字符集，其编码规则根据编码字节长度的不同，包括 UTF-8, UTF-16, UTF-32。

1.2.1. Unicode 编码规则

1. UTF-8

UTF-8 编码属于变长字节编码规则。即用 1-4 个字节表示 Unicode 字符集中的 1 个字符（如下表所示）。UTF-8 编码规定：最高位以 0 开头的，用 1 个字节表示 1 个字符，如英文字符；最高位以 110 开头的字节，与其后 1 个以 10 开头的字节拼接为一个整体，即用 2 个字节表示 1 个字符；最高位以 1110 开头的字节，与其后 2 个以 10 开头的字节拼接为一个整体，即用 3 个字节表示 1 个字符，如汉字。最高位以 11110 开头的字节，与其后 3 个以 10 开头的字节拼接为一个整体，即用 4 个字节表示 1 个字符，如 emoji 表情。

1. UTF-16

UTF-16 编码属于定长字节编码规则，固定地使用 2 个字节来表示 Unicode 中的 1 个字符。

1. UTF-32

UTF-32 编码属于定长字节编码规则，固定地使用 4 个字节来表示 Unicode 中的 1 个字符。其优点是根据 Unicode 中的字符编号，直接对字符编码，无需额外的解析工作。缺点是常用字符有大量的高位被 0 填充，造成空间浪费。

提示：为了便于统一管理各地区、各国家的编码规则，标准化组织用 “页码” 对众多的编码规则进行了编号，如 GBK 的页码为 956，而 UTF-8 的页码为 65001。这样，直接通过页码即可找到所需的编码规则。

注意：在 Windows 系统中，常常会同时出现 UTF-8 和 Unicode 两个编码规则。按道理， Unicode 只是字符集，不应出现在编码规则当中。其原因是，微软默认将 Unicode 当作 UTF-16 编码来处理。

CollationCollationCharacter Setutf8mb4_general_ciutf8mb4gbk_chinese_cigbk

Character SetCollationCollation

3.1. TiDB 与 MySQL 中的字符集及排序规则

_zh

utf8（utf8mb3）utf8mb3utf8mb3utf8mb3_utf8mb4_bin_cscase sensitivity_cicase insensitivity_asaccent sensitivity_aiaccent insensitivity_general_unicodeUnicode Collation Algorithmxxx_unicode_ci_520_0900UCA 5.2.0UCA 9.0.0UCA 4.0.0utf8_unicode_520_ciUCA 5.2.0utf8mb4_0900_ai_ciUCA 9.0.0_chinese_zhjaru

3.2. new_collations_enabled_on_first_bootstrap

_binnew_collations_enabled_on_first_bootstrap

server_config.tidbnew_collations_enabled_on_first_bootstrap:true

server_config:

  tidb:

    new_collations_enabled_on_first_bootstrap: true

utf8mb4_bintrueutf8_general_ciutf8mb4_general_ciutf8_unicode_ciutf8mb4_unicode_cigbk_chinese_cigbk_bin

可通过系统表 mysql.tidb 确认是否启用了新的排序框架：

SELECT VARIABLE_VALUE FROM mysql.tidb WHERE VARIABLE_NAME=’new_collation_enabled’;

aa aa aa a a

3.3. TiDB 中设置字符集与排序规则

可通过如下几种方式，分别为 TiDB 集群、数据库、库表、列设置字符集与排序规则：

1. 设置 TiDB 集群默认字符集及默认排序规则。

默认字符集为 utf8mb4，默认排序规则为 utf8mb4_bin。可通过系统变量 character_set_server 设置 TiDB 集群默认的字符集，通过 collation_server 设置TiDB 集群默认的排序规则。在 “CREATE DATABASE/SCHEMA” 时，若未显式指定字符集与排序规则，则默认继承系统变量 character_set_server 与 collation_server 的设置。

/* 1. 设置 TiDB 集群默认字符集的两种方式 */

mysql> SET @@GLOBAL.character_set_server = ”utf8mb4”;

mysql> SET GLOBAL character_set_server = ”utf8mb4”;

/* 2. 设置 TiDB 集群默认排序规则的两种方式 */

mysql> SET @@GLOBAL.collation_server = ”utf8mb4_bin”;

mysql> SET GLOBAL collation_server = ”utf8mb4_bin”;

/* 3. 查看 TiDB 集群默认字符集及默认排序规则 */

mysql> SELECT * FROM GLOBAL_VARIABLES WHERE variable_name IN (’character_set_server’,’collation_server’);

mysql> SHOW GLOBAL variables LIKE ’character_set_server’;

mysql> SHOW GLOBAL variables LIKE ’collation_server’;

1. 为数据库指定字符集及默认排序规则。

若未显式为数据库指定字符集、排序规则，则继承自 TiDB 集群的字符集设置。可通过系统变量@@character_set_database 和 @@collation_database 查看到当前数据库的字符集以及排序规则。也可通过系统表 information_schema.schemata 查看每个数据库的字符集与排序规则。

/* 1. 创建数据库（DATABASE/SCHEMA）时，指定默认字符集及排序规则 */

mysql> CREATE DATABASE DB_001 CHARACTER SET gbk COLLATE gbk_bin ;

mysql> CREATE SCHEMA DB_002 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;
/* 2. 通过 @@character_set_database 与 @@collation_database 查看数据库的字符集及排序规则 * /

mysql> USE db_001;

mysql> SELECT @@character_set_database, @@collation_database ;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

|           gbk            |        gbk_bin       |

+--------------------------+----------------------+

1 row in set (0.00 sec )
mysql> USE db_002;

mysql> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

|         utf8mb4          |  utf8mb4_general_ci  |

+--------------------------+----------------------+

1 row in set (0.00 sec )
/* 3. 通过系统表 information_schema.schemata 查看每个数据库字符集及排序规则 */

mysql> SELECT schema_name , default_character_set_name , default_collation_name FROM information_schema.schemata WHERE schema_name in ('DB_001', 'DB_002');
/* 4. 通过ALTER DATABASE 调整数据库的字符集与排序规则 */

mysql> ALTER DATABASE db_002 CHARACTER SET gbk COLLATE gbk_chinese_ci;

mysql> SELECT @@character_set_database , @@collation_database ;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

|         utf8mb4          |  utf8mb4_general_ci  |

+--------------------------+----------------------+

1 row in set (0.00 sec )

1. 为表指定字符集及排序规则。

若未为表显式指定字符集与排序规则，则继承自其所属数据库的字符集设置。

mysql> use db_001;

mysql> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+

| @@character_set_database | @@collation_database |

+--------------------------+----------------------+

|           gbk            |        gbk_bin       |

+--------------------------+----------------------+

mysql> CREATE TABLE tb_001(id int, name varchar(32)) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
/* 2. 未指定字符集设置，继承自 DB_001 数据库的字符集设置 */

mysql> CREATE TABLE tb_002(id int, name varchar(32));

mysql> SELECT table_name, table_collation FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'DB_001';

+------------+-----------------+

| table_name | table_collation |

+------------+-----------------+

|   tb_001   |   utf8mb4_bin   |

|   tb_002   |   gbk_bin       |

+------------+-----------------+

2 rows in set (0.00 sec)

1. 为列指定字符集及排序规则。

若未为列指定字符集与排序规则，则继承自其所属表的字符集设置。

mysql> USE db_001 ;

mysql> CREATE TABLE tb_003(

        id int ,

        name1 varchar(32) ,                                                  /* 继承自表的字符集设置   */

        name2 varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci   /* 显式指定列的字符集设置 */

      ) CHARACTER SET gbk COLLATE gbk_bin;                                   /* 显式指定表的字符集设置 */
mysql> SELECT table_name, column_name, column_type, character_set_name, collation_name

         FROM INFORMATION_SCHEMA.COLUMNS

        WHERE table_schema = 'DB_001' AND table_name = 'tb_003';

+------------+-------------+-------------+--------------------+--------------------+

| table_name | column_name | column_type | character_set_name |   collation_name   |

+------------+-------------+-------------+--------------------+--------------------+

|   tb_003   | id|int(11)  | NULL        |  NULL              |                    |

|   tb_003   | name1       | varchar(32) |  gbk               | gbk_bin            |

|   tb_003   | name2       | varchar(32) |  utf8mb4           | utf8mb4_unicode_ci |

+------------+-------------+-------------+--------------------+--------------------+

1. 为字符串指定字符集及排序规则。

character_set_connectioncollation_connection_gbk

mysql> SELECT _utf8mb4 '盛京征信' COLLATE utf8mb4_bin;

+--------------------------------------------+

|  _utf8mb4 '盛京征信' COLLATE utf8mb4_bin    |

+--------------------------------------------+

|   盛京征信                                  |

+--------------------------------------------+

1 row in set (0.00 sec )
mysql> SELECT _utf8mb4 '盛京征信' COLLATE utf8mb4_unicode_ci;

+---------------------------------------------------+

|   _utf8mb4 '盛京征信' COLLATE utf8mb4_unicode_ci   |

+---------------------------------------------------+

|   盛京征信                                         |

+---------------------------------------------------+

1 row in set (0.00 sec )

3.4. TiDB 中的 GBK 字符集

new_collations_enabled_on_first_bootstrap=truegbk_chinese_ci

gbk

ALTER TABLEgbk_gbkSELECT _gbk '盛京征信' COLLATE gbk_chinese_bin;ENUMSETutf8mb4charset=GBKcharset=GBKcharset=GBKcharset=GBKcharset=GBKcharset=GBK