MySQL 字符集：指定字符集和排序规则

字符集和排序规则有四个级别的默认设置：服务器，数据库，表 和 列。

“CHARACTER SET”用于指定字符集的子句中。【 “CHARSET”可以用作“CHARACTER SET”的同义词】

字符集问题不仅影响数据存储，还影响 Client 端程序与 MySQL 服务器之间的通信。如果希望 Client 端程序使用与默认字符集不同的字符集与服务器通信，则需要指出哪个字符集。

例如，要使用 utf8 Unicode 字符集，请在连接到服务器后发出以下语句：

SET NAMES 'utf8';

排序规则命名约定

MySQL 排序规则名称遵循以下约定：

1. 排序规则名称以与其关联的字符集的名称开头，通常后跟一个或多个后缀，以指示其他排序规则特征。例如，“utf8_general_ci”和“latin1_swedish_ci”分别是“utf8”和“latin1”字符集的排序规则。

   binary字符集具有单个排序规则，也称为binary，没有后缀。

2. 特定于语言的排序规则包括语言名称。例如，“utf8”字符集的“utf8_turkish_ci”和“utf8_hungarian_ci”使用土耳其语和匈牙利语规则分别对字符进行排序。
3. 排序规则后缀指示排序规则是区分大小写，区分重音还是假名敏感(或其某种组合)还是二进制。下表显示了用于指示这些特性的后缀。

排序规则后缀的含义：

Suffix	Meaning
_ai	重音：不敏感（Accent-sensitive）
_as	重音：敏感（Accent-sensitive）
_ci	大小写：不敏感（Case-insensitive）
_cs	大小写：敏感（Case-sensitive）
_bin	二进制（Binary）

• 对于未指定重音符号敏感性的非二进制排序规则名称，它由区分大小写确定。如果排序规则名称不包含“_ai”或“_as”，则名称中的“_ci”表示“_ai”，名称中的“_cs”表示“_as”。

  例如，“latin1_general_ci”显式不区分大小写，并且隐含不区分重音，而“latin1_general_cs”显式区分大小写，并且隐含区分重音。

• 对于二进制字符集的二进制排序规则，比较基于数字字节值。对于非二进制字符集的“_bin”排序规则，比较基于数字字符代码值，该值不同于多字节字符的字节值。
• Unicode 字符集的排序规则名称可以包括版本号，以指示排序规则所基于的 Unicode 排序规则算法(UCA)的版本。名称中没有版本号的基于 UCA 的归类使用版本 4.0.0 UCA 配重键。

  例如：

  utf8_unicode_520_ci：基于 UCA 5.2.0 配重键(http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt)。

  utf8_unicode_ci(未命名版本)：基于 UCA 4.0.0 重量键(http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt)。

• 对于 Unicode 字符集，“xxx_general_mysql500_ci”排序规则保留原始“xxx_general_ci”排序规则在 5.1.24 之前的顺序，并允许升级在 MySQL 5.1.24 之前创建的表(错误#27877)。

服务器字符集和排序规则

MySQL Server 具有服务器字符集和服务器排序规则。默认情况下，它们是“latin1”和“latin1_swedish_ci”，但是可以在服务器启动时在命令行或配置文件中显式设置它们，并在运行时进行更改。

最初，服务器字符集和排序规则取决于启动 mysqld 时使用的选项：可以使用“--character-set-server”作为字符集，同时，可以使用“--collation-server”作为排序规则添加：

1. 如果未指定字符集，则与“--character-set-server=latin1”相同。
2. 如果仅指定字符集而不指定排序规则，则使用的默认排序规则。

因此，以下三个命令都具有相同的效果：

mysqld
mysqld --character-set-server=latin1
mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

更改设置的一种方法是重新编译。要从源代码构建时更改默认服务器字符集和排序规则，请对“CMake”使用“DEFAULT_CHARSET”和“DEFAULT_COLLATION”选项。例如：

cmake . -DDEFAULT_CHARSET=latin1

Or:

cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci

• mysqld和 CMake 都验证字符集/排序规则组合是否有效。如果不是，则每个程序都会显示一条错误消息并终止。

• 如果未在“CREATE DATABASE”语句中指定数据库字符集和排序规则，则将服务器字符集和排序规则用作默认值。

• 可以从“character_set_server”和“collation_server”系统变量的值确定当前服务器的字符集和排序规则。这些变量可以在运行时更改。

数据库字符集和排序规则

每个数据库都有一个数据库字符集和一个数据库排序规则。“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]
可以使用关键字SCHEMA代替DATABASE。

• 可以使用关键字“SCHEMA”代替“DATABASE”。
• 所有数据库选项都存储在名为“db.opt”的文本文件中，该文件可在数据库目录中找到。
• “CHARACTER SET”和“COLLATE”子句可以在同一 MySQL 服务器上创建具有不同字符集和排序规则的数据库：

  CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
  

MySQL 通过以下方式选择数据库字符集和数据库排序规则：

1. 如果同时指定了“CHARACTER SET charset_name”和“COLLATE collation_name”，则使用字符集“charset_name”和排序规则“collation_name”。
2. 如果指定的“CHARACTER SET charset_name”不带“COLLATE”，则使用字符集“charset_name”及其默认排序规则。
   • 【要查看每个字符集的默认排序规则，请使用“SHOW CHARACTER SET”语句或查询“INFORMATION_SCHEMA”的“CHARACTER_SETS”表。】
3. 如果指定的“COLLATE collation_name”不带“CHARACTER SET”，则使用与“collation_name”关联的字符集和排序规则“collation_name”。
4. 否则（既未指定“CHARACTER SET”也未指定“COLLATE”），则使用服务器字符集和服务器排序规则。

• 可以从“character_set_database”和“collation_database”系统变量的值确定默认数据库的字符集和排序规则。每当默认数据库更改时，服务器都会设置这些变量。如果没有默认数据库，则变量的值与相应的服务器级系统变量“character_set_server”和“collation_server”相同。

要查看给定数据库的默认字符集和排序规则，请使用以下语句：

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

或者，在不更改默认数据库的情况下显示值：

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

数据库字符集和排序规则会影响服务器操作的以下方面：

1. 对于“CREATE TABLE”语句，如果未指定表字符集和排序规则，则将数据库字符集和排序规则用作表定义的默认值。要覆盖此设置，请提供显式的“CHARACTER SET”和“COLLATE”表选项。
2. 对于不包含“CHARACTER SET”子句的LOAD DATA语句，服务器使用“character_set_database”系统变量指示的字符集来解释文件中的信息。要覆盖此内容，请提供一个明确的“CHARACTER SET”子句。
3. 对于存储的例程(过程和函数)，将在例程创建时生效的数据库字符集和排序规则用作声明不包含“CHARACTER SET”或“COLLATE”属性的字符数据参数的字符集和排序规则。要覆盖它，请显式提供“CHARACTER SET”或“COLLATE”。

表字符集和排序规则

每个表都有一个表字符集和一个表排序规则。“CREATE TABLE”和“ALTER TABLE”语句具有用于指定表字符集和排序规则的可选子句：

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

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

例如:

CREATE TABLE t1 ( ... ) CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL 通过以下方式选择表字符集和排序规则：

1. 如果同时指定了“CHARACTER SET charset_name”和“COLLATE collation_name”，则使用字符集“charset_name”和排序规则“collation_name”。
2. 如果指定的“CHARACTER SET charset_name”不带“COLLATE”，则使用字符集“charset_name”及其默认排序规则。
   • 【要查看每个字符集的默认排序规则，请使用“SHOW CHARACTER SET”语句或查询“INFORMATION_SCHEMA”的“CHARACTER_SETS”表。】
3. 如果指定的“COLLATE collation_name”不带“CHARACTER SET”，则使用与“collation_name”关联的字符集和排序规则“collation_name”。
4. 否则（既未指定“CHARACTER SET”也未指定“COLLATE”），则使用数据库字符集和排序规则。

• 如果未在单个列定义中指定列字符集和排序规则，则将表字符集和排序规则用作列定义的默认值。【表字符集和排序规则是 MySQL 的扩展，标准 SQL 中没有这样的东西】

列字符集和排序规则

每个“字符”列（即“CHAR”，“VARCHAR”，“TEXT”类型或任何同义词的列）都有一个列字符集和一个列排序规则。 “CREATE TABLE”和“ALTER TABLE”的列定义语法具有用于指定列字符集和排序规则的可选子句：

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

这些子句也可用于ENUM和SET列：

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

例如:

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 通过以下方式选择列字符集和排序规则：

1. 如果同时指定了“CHARACTER SET charset_name”和“COLLATE collation_name”，则使用字符集“charset_name”和排序规则“collation_name”。

   CREATE TABLE t1
   (
       col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
   ) CHARACTER SET latin1 COLLATE latin1_bin;
   

2. 如果指定的“CHARACTER SET charset_name”不带“COLLATE”，则使用字符集“charset_name”及其默认排序规则。

   CREATE TABLE t1
   (
       col1 CHAR(10) CHARACTER SET utf8
   ) CHARACTER SET latin1 COLLATE latin1_bin;
   

   • 【要查看每个字符集的默认排序规则，请使用“SHOW CHARACTER SET”语句或查询“INFORMATION_SCHEMA”的“CHARACTER_SETS”表。】
3. 如果指定的“COLLATE collation_name”不带“CHARACTER SET”，则使用与“collation_name”关联的字符集和排序规则“collation_name”。

   CREATE TABLE t1
   (
       col1 CHAR(10) COLLATE utf8_polish_ci
   ) CHARACTER SET latin1 COLLATE latin1_bin;
   

4. 否则（既未指定“CHARACTER SET”也未指定“COLLATE”），则使用数据库字符集和排序规则。

   CREATE TABLE t1
   (
       col1 CHAR(10)
   ) CHARACTER SET latin1 COLLATE latin1_bin;
   

• “CHARACTER SET”和“COLLATE”子句是标准 SQL。
• 如果使用“ALTER TABLE”将列从一个字符集转换为另一个字符集，则 MySQL 会尝试映射数据值，但是如果字符集不兼容，则可能会丢失数据。

字符串常量字符集和排序规则

每个字符串常量都有一个字符集和一个排序规则。对于简单语句“SELECT 'string'”，该字符串具有由“character_set_connection”【区别于“character_set_server”和“character_set_database”】和“collation_connection”【区别于“collation_server”和“collation_database”】系统变量定义的连接默认字符集和排序规则。

字符串常量可能具有可选的字符集导引器和“COLLATE”子句，以将其指定为使用特定字符集和排序规则的字符串：

[_charset_name]'string' [COLLATE collation_name]

“_charset_name”表达式正式名称为“导引器”（introducer） 。它告诉解析器，“后面的字符串使用字符集‘charset_name’”引入程序不会像“CONVERT()”那样将字符串更改为引入程序字符集。尽管可能会发生填充，但它不会更改字符串值。

示例：

SELECT 'abc';
SELECT _latin1'abc';
SELECT _binary'abc';
SELECT _utf8'abc' COLLATE utf8_danish_ci;

MySQL 通过以下方式确定字符集和字符串常量的排序规则：

1. 如果同时指定了“_charset_name”和“COLLATE collation_name”，则使用字符集“charset_name”和排序规则“collation_name”。（“collation_name”必须是“charset_name”的允许排序规则）
2. 如果指定“_charset_name”但未指定“COLLATE”，则使用字符集“charset_name”及其默认排序规则。
3. 如果未指定“_charset_name”但已指定“COLLATE collation_name”，则使用“character_set_connection”系统变量和排序规则“collation_name”给出的连接默认字符集。（“collation_name”必须是连接默认字符集允许的排序规则）
4. 否则（既未指定“_charset_name”也未指定“COLLATE collation_name”），将使用“character_set_connection”和“collation_connection”系统变量给出的连接默认字符集和排序规则。

示例：

1. 具有latin1字符集和latin1_german1_ci归类的非二进制字符串：

   SELECT _latin1'Müller' COLLATE latin1_german1_ci;
   

2. 具有utf8字符集及其默认排序规则(即utf8_general_ci)的非二进制字符串：

   SELECT _utf8'Müller';
   

3. 具有binary字符集和默认排序规则(即binary)的二进制字符串：

   SELECT _binary'Müller';
   

4. 具有连接默认字符集和utf8_general_ci排序规则的非二进制字符串(如果连接字符集不是utf8，则失败)：

   SELECT 'Müller' COLLATE utf8_general_ci;
   

5. 具有连接默认字符集和排序规则的字符串：

   SELECT 'Müller';
   

“导引器”仅表示其后字符串的字符集，而不更改解析器在字符串内执行转义处理的方式。解析器始终根据“character_set_connection”给出的字符集来解释转义。

以下示例显示，即使存在引入程序，也使用“character_set_connection”进行转义处理。这些示例使用“SET NAMES”（将更改“character_set_connection”）并使用“HEX()”函数显示结果字符串，以便可以看到确切的字符串内容。

示例 1:

mysql> SET NAMES latin1;
mysql> SELECT HEX('à\n'), HEX(_sjis'à\n');
+------------+-----------------+
| HEX('à\n')  | HEX(_sjis'à\n')  |
+------------+-----------------+
| E00A       | E00A            |
+------------+-----------------+

在此，“à”（十六进制值“E0”）后跟“\n”（换行符的转义序列）。使用 latin1 的“character_set_connection”值解释转义序列以产生常量换行符（十六进制值“0A”）。即使对于第二个字符串，也会发生这种情况。也就是说，“_sjis”引入器不会影响解析器的转义处理。

示例 2:

mysql> SET NAMES sjis;
mysql> SELECT HEX('à\n'), HEX(_latin1'à\n');
+------------+-------------------+
| HEX('à\n')  | HEX(_latin1'à\n')  |
+------------+-------------------+
| E05C6E     | E05C6E            |
+------------+-------------------+

在这里，“character_set_connection”是“sjis”，是一个字符集，其中“à”后跟“\”（十六进制值“05”和“5C”）的序列是有效的多字节字符。因此，字符串的前两个字节被解释为单个“sjis”字符，而“\”不被解释为转义字符。以下“n”（十六进制值“6E”）不解释为转义序列的一部分。即使对于第二个字符串也是如此。“_latin1”引入器不会影响转义处理。

国家字符集

字符集简介

字符集和排序规则分配的示例

与其他 DBMS 的兼容性