Skip to main content

第 28 篇 字符集与排序规则

imodouAbout 10 minMySQL

《MySQL 入门教程》第 28 篇 字符集与排序规则

本篇我们介绍一下 MySQL 中的字符集（Character Set）和排序规则（Collation），它们决定了系统支持的字符数据以及它们的存储方式、不同字符比较和排序的结果。

28.1 字符集

我们可以为 MySQL 服务器、数据库、表、字符类型的字段以及字符串常量指定一个字符集（Character Set）和排序规则（Collation）。其中，字符集决定了能够存储哪些字符，比如 ASCII 字符集只能存储简单的英文、数字和一些控制字符；GB2312 字符集可以存储中文；Unicode 字符集能够支持世界上的各种语言。排序规则定义了字符集中字符的排序和比较顺序，包括是否区分大小写，是否区分重音等。

MySQL 为不同的环境提供了多种字符集支持，可以使用SHOW CHARACTER SET命令查看系统支持的所有字符集。例如：

SHOW CHARACTER SET;

Charset |Description                    |Default collation  |Maxlen|
--------|-------------------------------|-------------------|------|
armscii8|ARMSCII-8 Armenian             |armscii8_general_ci|     1|
ascii   |US ASCII                       |ascii_general_ci   |     1|
big5    |Big5 Traditional Chinese       |big5_chinese_ci    |     2|
...
utf32   |UTF-32 Unicode                 |utf32_general_ci   |     4|
utf8    |UTF-8 Unicode                  |utf8_general_ci    |     3|
utf8mb4 |UTF-8 Unicode                  |utf8mb4_0900_ai_ci |     4|

MySQL 8.0 开始，默认字符集改为 utf8mb4，而不是之前的 latin1；Maxlen 表示该字符集中单个字符最多可能占用的字节数，utf8mb4 中的一个字符最多占用 4 个字节，用于存储表情符号（emoji）；Default collation 表示该字符集默认的排序规则，具体参考下文。

28.1.1 服务器字符集

MySQL 服务器端支持多个不同级别的字符集设置，包括服务器级别、数据库级别、表级别、字段级别以及字符串常量级别。使用 SHOW 命令查看当前的设置：

SHOW variables LIKE '%character%';

Variable_name           |Value                         |
------------------------|------------------------------|
character_set_client    |utf8mb4                       |
character_set_connection|utf8mb4                       |
character_set_database  |utf8mb4                       |
character_set_filesystem|binary                        |
character_set_results   |                              |
character_set_server    |utf8mb4                       |
character_set_system    |utf8                          |
character_sets_dir      |/usr/share/mysql-8.0/charsets/|

其中，character_set_server 表示 MySQL 服务器的全局默认字符集，可以在服务器配置文件中 [mysqld] 部分的 character-set-server 选项或者启动服务时通过命令行参数 --character-set-server 进行设置，也可以在运行时动态修改。

character_set_database 表示当前默认数据库的字符集。当我们使用 CREATE DATABASE 或者 ALTER DATABASE 时，可以指定一个字符集和排序规则：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE，MySQL 默认使用 character_set_server 服务器字符集（utf8mb4）和排序规则（utf8mb4_0900_ai_ci）创建或者修改数据库。

对于某个具体数据库，可以使用 SHOW CREATE DATABASE 语句或者通过 information_schema.schemata 视图查看默认的字符集和排序规则。例如：

SHOW CREATE DATABASE hrdb;
Database|Create Database                                                                                                               |
--------|------------------------------------------------------------------------------------------------------------------------------|
hrdb    |CREATE DATABASE `hrdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */|

SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE SCHEMA_NAME = 'hrdb';
DEFAULT_CHARACTER_SET_NAME|DEFAULT_COLLATION_NAME|
--------------------------|----------------------|
utf8mb4                   |utf8mb4_0900_ai_ci    |

同样，对于 CREATE TABLE 和 ALTER TABLE 语句，也可以指定一个字符集和排序规则：

CREATE TABLE table_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE table_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE，MySQL 默认使用 character_set_database 数据库字符集和排序规则创建或者修改表。

对于某个具体的表，可以使用 SHOW CREATE TABLE 语句或者通过 information_schema.table 视图查看默认的字符集和排序规则。例如：

SHOW CREATE TABLE employee;
Table   |Create Table                                                                                                                                                                                                                                                   |
--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
employee|CREATE TABLE `employee` (
  `emp_id` int NOT NULL,
  `emp_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `sex` varchar(10) CHARACTER SET utf8 NOT NULL,
  `dept_id` int NOT NULL,
  `manager` int DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` int NOT NULL,
  `salary` decimal(8,2) NOT NULL,
  `bonus` decimal(8,2) DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `uk_emp_email` (`email`),
  KEY `idx_emp_name` (`emp_name`),
  KEY `idx_emp_job` (`job_id`),
  KEY `idx_emp_manager` (`manager`),
  KEY `idx_emp_dept` (`dept_id`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`),
  CONSTRAINT `fk_emp_job` FOREIGN KEY (`job_id`) REFERENCES `job` (`job_id`),
  CONSTRAINT `fk_emp_manager` FOREIGN KEY (`manager`) REFERENCES `employee` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci|

SELECT table_collation
FROM information_schema.tables
WHERE table_name = 'employee';
TABLE_COLLATION   |
------------------|
utf8mb4_0900_ai_ci|

每个 CHAR、VARCHAR、TEXT、ENUM、SET 类型的字段也可以拥有自己的字符集和排序规则：

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

如果没有指定字符集 CHARACTER SET 或者排序规则 COLLATE，MySQL 默认使用表的字符集和排序规则创建或者修改字段。使用 SHOW CREATE TABLE 语句可以查看表中字段的字符集和排序规则。

📝MySQL 字符集和排序规则的优先级别为字段级别大于表级别，表级别大于数据库级别，数据库级别大于服务器级别。

另外，MySQL 中的字符串常量也有字符集和排序规则。例如，以下字符串常量使用相同的字符集：

SELECT '数据库';
SELECT _utf8mb4'数据库';
SELECT _utf8mb4'数据库' COLLATE utf8mb4_0900_ai_ci;

如果没有指定字符集或者排序规则 COLLATE，MySQL 使用默认的连接字符集 character_set_connection。

字符集和排序规则会对一些 MySQL 字符函数产生影响，例如：

SELECT length('abc'), char_length('abc'), length('数据库'), char_length('数据库');
length('abc')|char_length('abc')|length('数据库')|char_length('数据库')|
-------------|------------------|---------------|--------------------|
            3|                 3|              9|                   3|

LENGTH 函数返回以字节为单位的字符串长度，多字节字符集中字符占用的字节数可能不同；CHAR_LENGTH 函数返回以字符为单位的字符串长度。

CONVERT(expr USING transcoding_name) 函数可以实现不同字符集之间的数据转换。例如：

SELECT CONVERT('MySQL' USING ascii);

📝专栏的第 15 篇介绍了MySQL 字符处理函数和运算符。

28.1.2 客户端字符集

MySQL 客户端应用和服务器连接之后，通过连接输入 SQL 语句，发送命令，接收服务器端的结果或者错误信息。在这个过程中，还会涉及到以下字符集设置：

  • 客户端语句使用的字符集，MySQL 使用 character_set_client 的设置作为客户端字符集；
  • 服务器接收到语句之后需要将其转换为哪种字符集，MySQL 使用 character_set_connection 的值作为转换的目标字符集。同时它也是字符串常量默认的字符集；
  • 服务器在返回查询结果（包括字段名、字段值、错误信息等）之前需要将其转换为哪种字符集，MySQL 使用 character_set_results 作为这种转换的目标字符集。

客户端连接的字符集可以通过以下几种方法进行设置：

  • 连接数据库之后，使用 SET NAMES 'charset_name' [COLLATE 'collation_name'] 命令进行设置。例如：

    SET NAMES 'utf8';

    该语句相当于同时设置了 character_set_client、character_set_results 以及 character_set_connection 的值。

  • 使用 SET CHARACTER SET 'charset_name' 命令进行设置。例如：

    SET SET CHARACTER SET 'utf8';

    该语句相当于同时设置了 character_set_client、character_set_results 的值，并且将 character_set_connection 的值设置为 character_set_database。

  • 如果客户端应用支持 --default-character-set 选项，例如 mysql、mysqladmin 等，可以在配置文件中 [mysql] 部分的 default-character-set 配置项进行设置。

  • 某些 MySQL 驱动程序允许在连接字符串中指定一个字符集编码，例如 JDBC 中的 characterEncoding。

为了避免不同字符集转换可能带来的问题，最好将客户端连接字符集和服务器的字符集设置为一致。

📝系统变量 character_set_system 是服务器存储元数据（Metadata）使用的字符集，总是设置为 utf8。character_set_filesystem 用于设置文件系统的字符集，主要影响打开文件时对文件名的解析。character_sets_dir 表示字符集的存储目录。

28.1.3 国家字符集

SQL 标准定义了两种使用特定字符集的字符串类型：NCHAR（NATIONAL CHAR）和 NVARCHAR（NATIONAL VARCHAR）。MySQL 选择 utf8（不是 utf8mb4）作为这种类型的预定义字符集，以下三种形式的定义等价：

CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)

同样，以下语句等价：

VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NVARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)

对于国家字符集中的字符串常量，可以使用 N'literal' 的形式进行表示。例如：

SELECT N'数据库';
SELECT n'数据库';
SELECT _utf8'数据库';

28.2 排序规则

每个 MySQL 字符集可以支持一个或者多个排序规则，用于定义每个字符的比较规则，包括是否区分大小写，是否区分重音等。MySQL 使用 SHOW COLLATION 命令查看各种字符集支持的排序规则：

Collation                 |Charset |Id |Default|Compiled|Sortlen|Pad_attribute|
--------------------------|--------|---|-------|--------|-------|-------------|
armscii8_bin              |armscii8| 64|       |Yes     |      1|PAD SPACE    |
armscii8_general_ci       |armscii8| 32|Yes    |Yes     |      1|PAD SPACE    |
ascii_bin                 |ascii   | 65|       |Yes     |      1|PAD SPACE    |
...
utf8mb4_hu_0900_ai_ci     |utf8mb4 |274|       |Yes     |      0|NO PAD       |
utf8mb4_hu_0900_as_cs     |utf8mb4 |297|       |Yes     |      0|NO PAD       |
utf8mb4_icelandic_ci      |utf8mb4 |225|       |Yes     |      8|PAD SPACE    |

MySQL 8.0 默认使用 utf8mb4 字符集，默认的排序规则为 utf8mb4_0900_ai_ci，表示不区分重音和大小写。例如：

SELECT 'a' = 'A';
'a' = 'A'|
---------|
        1|

如果换成 utf8mb4_zh_0900_as_cs 排序规则，a 和 A 比较的结果如下：

SELECT 'a' COLLATE utf8mb4_zh_0900_as_cs = 'A';
'a' COLLATE utf8mb4_zh_0900_as_cs = 'A'|
---------------------------------------|
                                      0|

28.2.1 设置排序规则

排序规则和字符集一样支持不同级别的设置；如果没有指定排序规则，MySQL 会基于字符集设置一个默认的排序规则。使用 SHOW 语句查看当前的设置：

SHOW variables LIKE '%collation%';

Variable_name                |Value             |
-----------------------------|------------------|
collation_connection         |utf8mb4_0900_ai_ci|
collation_database           |utf8mb4_0900_ai_ci|
collation_server             |utf8mb4_0900_ai_ci|
default_collation_for_utf8mb4|utf8mb4_0900_ai_ci|

其中，collation_server 与 character_set_server 对应，表示 MySQL 服务器的全局默认排序规则，可以在服务器配置文件中 [mysqld] 部分的 collation-server 选项或者启动服务时通过命令行参数 --collation-server 进行设置，也可以在运行时动态修改。

collation_database 和 character_set_database 对应，表示当前默认数据库的排序规则。当我们使用 CREATE DATABASE 或者 ALTER DATABASE 时，可以指定一个排序规则，参考上文。

同样，对于 CREATE TABLE 和 ALTER TABLE 语句，也可以为表或者字符类型的字段指定一个排序规则，参考上文。

collation_connection 和 character_set_connection 对应，表示客户端连接使用的排序规则。

排序规则由字符集的名称、可选的本地语言代码和 Unicode 版本以及其他属性组成，例如 utf8mb4_zh_0900_as_cs 表示 9.0.0 版本 utf8mb4 字符集的中文排序规则，区分重音（Accent-sensitive）和大小写（Case-sensitive）。

📝由于排序规则中包含了字符集信息，设置排序规则通常意味着同时设置了字符集。例如 CREATE TABLE ... COLLATE utf8mb4_0900_ai_ci 表示使用 utf8mb4 字符集和 utf8mb4_0900_ai_ci 排序规则创建表。

28.2.2 中文排序规则

对于中文而言，排序方式与英文有所不同；中文通常需要按照拼音、偏旁部首或者笔画进行排序。

MySQL 8.0 默认使用的排序规则 utf8mb4_0900_ai_ci 对于中文按照偏旁部首进行排序。以下语句按照员工的姓名进行排序：

SELECT emp_name
FROM employee
ORDER BY emp_name;
emp_name  |
----------|
关兴      |
关平      |
关羽      |
刘备      |
周仓      |
孙丫鬟    |
孙乾      |
孙尚香    |
庞统      |
廖化      |
...

对于 utf8mb4 字符集，utf8mb4_zh_0900_as_cs 排序规则按照中文拼音进行排序。例如：

SELECT emp_name
FROM employee
ORDER BY emp_name collate 'utf8mb4_zh_0900_as_cs';
emp_name |
----------|
邓芝      |
法正      |
关平      |
关兴      |
关羽      |
黄权      |
黄忠      |
简雍      |
蒋琬      |
廖化      |
...

也可以将数据转换为其他支持特定排序规则的字符集，例如 gbk 字符集默认的 gbk_chinese_ci 排序规则就是按照拼音进行排序：

SELECT emp_name
FROM employee
ORDER BY convert(emp_name using gbk);

该语句和上一个示例返回的结果相同。

Default footer
Copyright © 2024 imodou