字符集
我们可以为 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 表示该字符集默认的排序规则。
服务器字符集
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);
客户端字符集
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 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 表示字符集的存储目录。
国家字符集
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'数据库';
排序规则
每个 MySQL 字符集可以支持一个或者多个排序规则,用于定义每个字符的比较规则,包括是否区分大小写,是否区分重音等。MySQL 使用 SHOW COLLATION 命令查看各种字符集支持的排序规则:
标签:set,name,utf8mb4,字符集,character,SET,规则,排序 From: https://www.cnblogs.com/xfeiyun/p/16963089.html