MySQL :: MySQL 8.4 参考手册 :: 12.3.5 列字符集和排序规则

MySQL 8.4 参考手册 · 中文镜像

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

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

MySQL 8.4 参考手册 / 12 种字符集、排序规则、Unicode / 12.3 指定字符集和排序规则 / 12.3.5 列字符集和排序规则

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

12.3.5 列字符集与排序规则12.3.5 Column Character Set and Collation

每个 “字符” 列（即 CHAR、VARCHAR、TEXT 类型，或任何同义词列）都具有列字符集和列排序规则。CREATE TABLE 和 ALTER TABLE 的列定义语法具有可选子句，用于指定列字符集和排序规则：Every “character” column (that is, a column of type CHAR, VARCHAR, a TEXT type, or any synonym) has a column character set and a column collation. Column definition syntax for CREATE TABLE and ALTER TABLE has optional clauses for specifying the column character set and collation:

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

这些子句也可以用于 ENUM 和 SET 列：These clauses can also be used for ENUM and SET columns:

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

示例：Examples:

CREATE TABLE t1
(
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

MySQL 以以下方式选择列的字符集和排序规则：MySQL chooses the column character set and 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.
```
CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) CHARACTER SET latin1 COLLATE latin1_bin;
```
该列指定了字符集和校对规则，因此将被使用。该列的字符集为 utf8mb4，校对规则为 utf8mb4_unicode_ci。The character set and collation are specified for the column, so they are used. The column has character set utf8mb4 and collation utf8mb4_unicode_ci.
如果指定了 CHARACTER SET charset_name 而没有指定 COLLATE，将使用字符集 charset_name 及其默认校对规则。If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used.
```
CREATE TABLE t1
(
    col1 CHAR(10) CHARACTER SET utf8mb4
) CHARACTER SET latin1 COLLATE latin1_bin;
```
该列指定了字符集，但未指定校对规则。该列的字符集为 utf8mb4，默认校对规则为 utf8mb4 的 utf8mb4_0900_ai_ci。要查看每个字符集的默认校对规则，请使用 SHOW CHARACTER SET 语句或查询 INFORMATION_SCHEMA CHARACTER_SETS 表。The character set is specified for the column, but the collation is not. The column has character set utf8mb4 and the default collation for utf8mb4, which is utf8mb4_0900_ai_ci. 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.
```
CREATE TABLE t1
(
    col1 CHAR(10) COLLATE utf8mb4_polish_ci
) CHARACTER SET latin1 COLLATE latin1_bin;
```
列指定了排序规则，但未指定字符集。该列的排序规则为 utf8mb4_polish_ci，字符集为与该排序规则关联的字符集，即 utf8mb4。The collation is specified for the column, but the character set is not. The column has collation utf8mb4_polish_ci and the character set is the one associated with the collation, which is utf8mb4.
否则（未指定 CHARACTER SET 或 COLLATE），则使用表的字符集和排序规则。Otherwise (neither CHARACTER SET nor COLLATE is specified), the table character set and collation are used.
```
CREATE TABLE t1
(
    col1 CHAR(10)
) CHARACTER SET latin1 COLLATE latin1_bin;
```
列未指定字符集或排序规则，因此使用表的默认值。该列的字符集为 latin1，排序规则为 latin1_bin。Neither the character set nor collation is specified for the column, so the table defaults are used. The column has character set latin1 and collation latin1_bin.

CHARACTER SET 和 COLLATE 子句是标准 SQL。The CHARACTER SET and COLLATE clauses are standard SQL.

如果你使用 ALTER TABLE 将某列从一个字符集转换为另一个字符集，MySQL 会尝试映射数据值，但如果字符集不兼容，可能会导致数据丢失。If you use ALTER TABLE to convert a column from one character set to another, MySQL attempts to map the data values, but if the character sets are incompatible, there may be data loss.

首页上一级下一节