MySQL :: MySQL 8.4 参考手册 :: 12.3.3 数据库字符集和排序规则

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 12 种字符集、排序规则、Unicode / 12.3 指定字符集和排序规则 / 12.3.3 数据库字符集和排序规则

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

12.3.3 数据库字符集和排序规则12.3.3 Database Character Set and Collation

每个数据库都有一个数据库字符集和数据库排序规则。CREATE DATABASE 和 ALTER DATABASE 语句有可选的子句用于指定数据库字符集和排序规则：Every database has a database character set and a database collation. The CREATE DATABASE and ALTER DATABASE statements have optional clauses for specifying the database character set and collation:

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]

关键字 SCHEMA 可以代替 DATABASE 使用。The keyword SCHEMA can be used instead of DATABASE.

CHARACTER SET 和 COLLATE 子句可以使同一个 MySQL 服务器上创建具有不同字符集和排序规则的数据库成为可能。The CHARACTER SET and COLLATE clauses make it possible to create databases with different character sets and collations on the same MySQL server.

数据库选项存储在数据字典中，可以通过检查信息模式 SCHEMATA 表来查看。Database options are stored in the data dictionary and can be examined by checking the Information Schema SCHEMATA table.

示例：Example:

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL 以下列方式选择数据库字符集和数据库排序规则：MySQL chooses the database character set and database collation in the following manner:

如果同时指定了 CHARACTER SET charset_name 和 COLLATE collation_name，将使用字符集 charset_name 和校对规则 collation_name。If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.
如果指定了 CHARACTER SET charset_name 而未指定 COLLATE，则使用字符集 charset_name 及其默认排序规则。要查看每个字符集的默认排序规则，请使用 SHOW CHARACTER SET 语句或查询 INFORMATION_SCHEMA CHARACTER_SETS 表。If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.
如果指定了 COLLATE collation_name 而没有指定 CHARACTER SET，则使用与 collation_name 相关联的字符集和排序规则 collation_name。If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.
否则（既未指定 CHARACTER SET 也未指定 COLLATE），则使用服务器字符集和服务器排序规则。Otherwise (neither CHARACTER SET nor COLLATE is specified), the server character set and server collation are used.

默认数据库的字符集和排序规则可以从 character_set_database 和 collation_database 系统变量的值中确定。每当默认数据库更改时，服务器会设置这些变量。如果没有默认数据库，这些变量的值与相应的服务器级系统变量 character_set_server 和 collation_server 相同。The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.

要查看给定数据库的默认字符集和排序规则，请使用以下语句：To see the default character set and collation for a given database, use these statements:

USE db_name;
SELECT @@character_set_database, @@collation_database;

或者，要在不更改默认数据库的情况下显示这些值：Alternatively, to display the values without changing the default database:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

数据库字符集和排序规则会影响服务器操作的以下方面：The database character set and collation affect these aspects of server operation:

对于 CREATE TABLE 语句，如果未指定表的字符集和校对规则，则使用数据库字符集和校对规则作为表定义的默认值。要覆盖此设置，请提供明确的 CHARACTER SET 和 COLLATE 表选项。For CREATE TABLE statements, the database character set and collation are used as default values for table definitions if the table character set and collation are not specified. To override this, provide explicit CHARACTER SET and COLLATE table options.
对于未包含 CHARACTER SET 子句的 LOAD DATA 语句，服务器使用由 character_set_database 系统变量指示的字符集来解释文件中的信息。要覆盖此设置，请提供明确的 CHARACTER SET 子句。For LOAD DATA statements that include no CHARACTER SET clause, the server uses the character set indicated by the character_set_database system variable to interpret the information in the file. To override this, provide an explicit CHARACTER SET clause.
对于存储例程（存储过程和函数），在创建例程时生效的数据库字符集和排序规则将用于那些声明中未包含 CHARACTER SET 或 COLLATE 属性的字符数据参数。要覆盖此设置，请明确提供 CHARACTER SET 和 COLLATE。For stored routines (procedures and functions), the database character set and collation in effect at routine creation time are used as the character set and collation of character data parameters for which the declaration includes no CHARACTER SET or a COLLATE attribute. To override this, provide CHARACTER SET and COLLATE explicitly.

首页上一级下一节