MySQL 字符集

恋喵大鲤鱼

发布于 2023-10-12 16:17:52

2300

发布于 2023-10-12 16:17:52

文章被收录于专栏：C/C++基础

1.简介

MySQL 支持多种字符集，使您能够使用各种字符集存储数据，并根据给定排序规则执行比较。

MySQL 服务器默认字符集和排序规则分别是 utf8mb4 和 utf8mb4_0900_ai_ci，但是您可以在服务器、数据库、表、列和字符串字面量级别指定字符集。

2.支持的字符集

MySQL 支持多种字符集，包括多个 Unicode 字符集。

使用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|

或查看视图 INFORMATION_SCHEMA.CHARACTER_SETS：

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS

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

3.字符集级别

MySQL 支持多个不同级别的字符集设置，包括服务器级别、数据库级别、表级别、字段级别以及字符串常量级别。

使用 SHOW VARIABLES 语句查看当前设置：

SHOW VARIABLES LIKE '%character%';

+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | latin1                                    |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.7.42.R6/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.08 sec)

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

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

4.数据库字符集

character_set_database 表示当前默认数据库的字符集，可以通过如下语句查看：

SHOW VARIABLES LIKE '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 db_name;

-- 或
SELECT default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE SCHEMA_NAME = 'db_name';

5.数据表字符集

对于 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]

注意，MySQL 没有那么智能，修改数据表字符集时，并不会自动将表中历史数据转换为新的字符集编码。如果表中有历史数据，那么需要做一次转换，如转换为 utf8mb4。

ALTER TABLE tbl_name
	CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

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

-- 查看字符集
SHOW CREATE TABLE tbl_name

-- 查看排序规则
SELECT TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_NAME = 'tbl_name';

6.表字段字符集

每个 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 语句可以查看表中字段的字符集和排序规则。

7.字符串常量字符集

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

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

如果没有指定字符集或者排序规则，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);

8.客户端字符集

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

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

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

（1）连接数据库之后，使用 SET NAMES 语句设置。

SET NAMES {'charset_name'
    [COLLATE 'collation_name'] | DEFAULT}

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

（2）使用 SET CHARACTER SET 语句。

SET {CHARACTER SET | CHARSET}
    {'charset_name' | DEFAULT}

该语句用给定的字符集映射服务器和当前客户端之间发送的所有字符串。SET CHARACTER SET设置了三个会话系统变量：character_set_client 和 character_set_results 设置为给定的字符集，而 character_set_connection 设置为character_set_database 的值。

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

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

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