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

来自Wikioe
跳到导航 跳到搜索


关于

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


“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”引入器不会影响转义处理。

国家字符集【？？？】

标准 SQL 定义“NCHAR”或“NATIONAL CHAR”作为指示“CHAR”列应使用某些预定义字符集的方式。 MySQL 使用 utf8 作为预定义字符集。

例如，这些数据类型声明是等效的： 

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'”(或“n'literal'”)在国家字符集中创建一个字符串。这些语句是等效的： 

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

字符集导引器【？？？】

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

[_charset_name] literal [COLLATE collation_name]

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

  1. 对于字符串常量，导引器和字符串之间的空格是允许的，但是可选的。
  2. 对于字符集常量，导引器指表示其后字符串的字符集，但不更改解析器在字符串内执行转义处理的方式。解析器始终根据“character_set_connection”给出的字符集来解释转义。

示例： 

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

SELECT _latin1 X'4D7953514C';
SELECT _utf8 0x4D7953514C COLLATE utf8_danish_ci;

SELECT _latin1 b'1000001';
SELECT _utf8 0b1000001 COLLATE utf8_danish_ci;


可以使用“_binary”导引器将字符串常量指定为二进制字符串。十六进制常量和位值常量默认情况下是二进制字符串，因此_binary是允许的，但不必要。


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”：
    1. 对于字符串常量：使用“character_set_connection”系统变量和排序规则“collation_name”给出的连接默认字符集。（“collation_name”必须是连接默认字符集允许的排序规则）
    2. 对于十六进制常量 或 位值常量：唯一允许的排序规则是“binary”，因为默认情况下这些常量类型是二进制字符串。
  4. 否则（既未指定“_charset_name”也未指定“COLLATE collation_name”）：
    1. 对于字符串常量：将使用“character_set_connection”和“collation_connection”系统变量给出的连接默认字符集和排序规则。
    2. 对于十六进制常量 或 位值常量：字符集和排序规则为“binary”。

示例：

  1. 具有 latin1 字符集和 latin1_german1_ci 归类的非二进制字符串： 
    SELECT _latin1'Müller' COLLATE latin1_german1_ci;
SELECT _latin1 X'0A0D' COLLATE latin1_german1_ci;
SELECT _latin1 b'0110' COLLATE latin1_german1_ci;
  2. 具有 utf8 字符集及其默认排序规则(即 utf8_general_ci)的非二进制字符串： 
    SELECT _utf8'Müller';
SELECT _utf8 X'0A0D';
SELECT _utf8 b'0110';
  3. 具有 binary 字符集及其默认排序规则(即 binary)的二进制字符串： 
    SELECT _binary'Müller';
SELECT X'0A0D';
SELECT b'0110';
    十六进制常量和位值常量不需要引入符，因为默认情况下它们是二进制字符串。
  4. 具有连接默认字符集和 utf8_general_ci 排序规则的非二进制字符串(如果连接字符集不是 utf8，则失败)： 
    SELECT 'Müller' COLLATE utf8_general_ci;
    此构造(仅COLLATE)不适用于十六进制常量或位常量，因为无论连接字符集如何，其字符集均为 binary，并且 binary 与 utf8_general_ci 排序规则不兼容。在没有简介的情况下，唯一允许的“COLLATE”子句是“COLLATE binary”。
  5. 具有连接默认字符集和排序规则的字符串： 
    SELECT 'Müller';

与其他 DBMS 的兼容性

为了实现 MaxDB 兼容性，这两个语句是相同的： 

CREATE TABLE t1 (f1 CHAR(N) UNICODE);
CREATE TABLE t1 (f1 CHAR(N) CHARACTER SET ucs2);
取自“http://wiki.eijux.com/index.php?title=MySQL_字符集：指定字符集和排序规则&oldid=6504”