By

本节主要讲述MySQL字符集与校对集。

MySQL内置一系列不同的字符集，你可以选择不同的字符集来存储数据，并根据不同的校对集(collations)来进行数据比较。你可以在MySQL Server层级、database层级、table层级、column层级来设置字符集。

本节主要包含以下内容：

  • 字符集与校对集是什么？

  • 多级系统默认字符集设置

  • 指定字符集与校对集的语法

  • 受影响的函数及操作

  • Unicode支持

  • 可用的字符集及校对集变量

  • 为错误消息指定语言

  • 为月/日选择本地名称

字符集设置问题不仅会影响数据存储，也会影响客户端程序与MySQL Server之间的通信。假如MySQL Server端设置的字符集并不是系统默认的字符集，这时如果一个客户端应用程序要与Server端进行通信，则客户端必须指定相应的字符编码。例如，要使用utf-8 Unicode字符集，则客户端连接服务器之后需要执行如下语句：

SET NAMES 'utf8';

更多关于为应用程序设置字符集，及client/server之间字符集的相关问题，我们后序都会进行介绍。

1. 字符集和校对集概述

一个字符集就是一系列符号和编码。一个校对集就是一个字符集中一系列用于字符比较的规则。下面举个例子：假设我们有一个字母表:A、B、a、b， 我们给每一个字母一个编号： A = 0, B = 1, a = 2, b = 3。这里A即为一个符号，数字0即为A的编码。这4个字母及相应的编码组成一个字符集。

假设我们想要比较两个字符串的值： A 和 B。最简单的方法是查看A和B的编码(0与1)。这里因为0<1，因此我们可以说A<B。这里我们即是通过了一个校对集(比较字符编码)完成了字符的比较。我们称这个最简单的校对集(基于字符编码的比较）为binary collation。

但是假如我们认为大小写字母相等的话，应该如何处理呢？ 这时我们需要两条规则： 1） 将小写字母a看成等于A， 将小写字母b看成等于B; 2) 比较相应编码。我们称之为大小写不敏感校对。它比binary collation稍微复杂一点。

而在实际生活中，大部分字符集都有很多字符： 可能包含整个字母表， 对于一些东方语言（例如中文）则可能包含更多的字符、特殊符号及标点符号。并且大多数的校对集也拥有很多规则， 而不仅仅是区分大小写，也包括是否区分重音等。

幸运的是，MySQL可以帮我们处理这些情况：

  • 使用不同的字符集来存储字符串

  • 使用不同的校对集来比较字符串

  • 在同一个server、database、甚至table中可以混合使用多种不同的字符集和校对集

  • 可以在任何层级(server、database、table、column）指定字符集和校对集

为了有效的使用这些特性，你必须知道有哪些字符集和校对集可用，并且知道如何更改默认的字符集与校对集，还需要了解它们是如何影响相关字符串操作和函数的。

2 MySQL中的字符集和校对集

MySQL Server支持很多字符集，我们可以通过使用INFORMATION_SCHEMA CHARACTER_SETS表或者SHOW CHARACTER SET命令来查看支持哪些字符集：

mysql> use information_schema;
Database changed
mysql> select * from CHARACTER_SETS limit 10;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                 | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese    |      2 |
| dec8               | dec8_swedish_ci      | DEC West European           |      1 |
| cp850              | cp850_general_ci     | DOS West European           |      1 |
| hp8                | hp8_english_ci       | HP West European            |      1 |
| koi8r              | koi8r_general_ci     | KOI8-R Relcom Russian       |      1 |
| latin1             | latin1_swedish_ci    | cp1252 West European        |      1 |
| latin2             | latin2_general_ci    | ISO 8859-2 Central European |      1 |
| swe7               | swe7_swedish_ci      | 7bit Swedish                |      1 |
| ascii              | ascii_general_ci     | US ASCII                    |      1 |
| ujis               | ujis_japanese_ci     | EUC-JP Japanese             |      3 |
+--------------------+----------------------+-----------------------------+--------+
10 rows in set (0.00 sec)

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

默认情况下，SHOW CHARACTER SET会显示所有可用的字符集。它也支持一个可选的LIKE或WHERE选项来过滤某种类型的匹配。例如：

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | cp1252 West European        | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+
4 rows in set (0.00 sec)

一个给定的字符集通常有至少一种校对集，而大多数的字符集都有多种不同的校对集。如果要找出一种字符集有哪些校对集的话，可以使用INFORMATION_SCHEMA COLLATIONS表或者通过SHOW COLLATION命令。

默认情况下，SHOW COLLATION会显示所有可用的校对集，其也可以通过一个可选的LIKE或WHERE选项来匹配显示哪些校对集。例如，我们要查询默认的字符集latin1的校对集，则可以通过如下命令：

mysql> SHOW COLLATION WHERE Charset = 'latin1';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+
8 rows in set (0.00 sec)

关于上表latin1校对集的含义如下：

mysql-collation-latin1

一般来说，校对集具有如下的特性：

  • 两个不同的字符集，其校对集一般不同；

  • 每一个字符集都有一个默认的校对集。例如，latin1与utf8的默认校对集分别是latin1_swedish_ci与utf8_general_ci。可以通过INFORMATION_SCHEMA CHARACTER_SETS表或者SHOW CHARACTER SET来查询默认的校对集。

  • 校对集的名字一般以对应的字符集名字开头，后边跟着一个或多个后缀

当一个字符集拥有多个校对集时，也许我们并不清楚使用哪一个校对集更符合我们应用程序的要求。为了避免选择一个不适当的校对集，这时我们通常会选择使用一些有代表性的数据来进行测试验证。

2.1 字符集Repertoire(曲目)

一个字符集的repertoire就是该字符集所包含的一系列字符。字符串表达式拥有一个repertoire属性，其可能含有两种值：

  • ASCII： 该表达式只能包含Unicode范围为[U+0000, U+007F]之间的字符

  • UNICODE： 表达式可以包含Unicode范围为[U+0000, U+10FFFF]之间的字符。这包含了基本的多语言层字符(Basic Multilingual Plane, BMP)范围[U+0000, U+FFFF]，也包括BMP范围外的超级字符[U+10000, U+10FFFF]。

ASCII范围是UNICODE范围的一个子集，因此一个只含ASCII曲目的字符串可以安全的转换为UNICODE曲目的字符串，或者是另一种是ASCII超级的字符集。（注： 除swe7之外，所有的字符集都是ASCII的超级)。通过使用repertoire，使得在表达式中可以进行自动的字符集转换，这样就可以避免MySQL中很多类似illegal mix of collations这样的错误。

如下的讨论展示了一些表达式示例及与它们对应的repertoires，并且描述了如何使用repertoire来改变表达式的评估求值：

  • 一个字符串常量的repertiore取决于字符串的内容，并且可能与该字符串的字符集的repertiore不同：
SET NAMES utf8; SELECT 'abc';
SELECT _utf8'def';
SELECT N'MySQL';

这里尽管在开始时将字符集设置为了utf8，但是实际上字符串所包含的字符均为ASCII范围内的字符，因此它们的repertoire是ASCII而不是UNICODE。

  • ASCII字符集的列。下表中，c1具有ASCII repertoire
CREATE TABLE t1 (c1 CHAR(1) CHARACTER SET ascii);

下面的列子展示了由于没有一个合适的repertoire而导致的错误：

CREATE TABLE t1 (
c1 CHAR(1) CHARACTER SET latin1,
c2 CHAR(1) CHARACTER SET ascii);
INSERT INTO t1 VALUES ('a','b');

假设没有repertoire,将会发生如下的错误：

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (ascii_general_ci,IMPLICIT) for operation 'concat'

使用repertoire之后，子集可以无损的转换成超级(ascii转换成latin1)，这样就可以正确的返回结果（这是MySQL默认支持的）：

+---------------+
| CONCAT(c1,c2) |
+---------------+
| ab            |
+---------------+

  • 只携带一个字符串参数的Function会继承该参数的repertoire。例如UPPER(_utf8'abc')具有ascii respertoire,这是因为它的参数具有ascii respertoire。

  • 针对一个返回值为字符串的函数（注意：该函数不带字符串参数），则会采用character_set_connection（参看p585)作为返回结果的字符集，假如character_set_connection是ascii的话，则返回结果respertoire是ASCII，否则为UNICODE.

FORMAT(numeric_column, 4);

下面的例子展示了respertoire是如何影响MySQL的查询结果的：

SET NAMES ascii;
CREATE TABLE t1 (a INT, b VARCHAR(10) CHARACTER SET latin1);
INSERT INTO t1 VALUES (1,'b');
SELECT CONCAT(FORMAT(a, 4), b) FROM t1;

假如没有repertoire的话，将会发生如下的错误：

ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,COERCIBLE)
and (latin1_swedish_ci,IMPLICIT) for operation 'concat'

而在有repertoire的情况下，将能够返回如下的结果：

+-------------------------+
| CONCAT(FORMAT(a, 4), b) |
+-------------------------+
| 1.0000b |
+-------------------------+
  • 带有两个或两个以上string参数的Function会继承最宽参数的repertoire作为返回结果的repertoire(UNICODE比ASCII宽)。考虑如下CONCAT()调用：
CONCAT(_ucs2 X'0041', _ucs2 X'0042')
CONCAT(_ucs2 X'0041', _ucs2 X'00C2')

上面第一个调用，返回结果的repertoire是ASCII，这是因为两个参数都在ascii字符集范围内。而对于第二个调用，返回结果的repertoire是UNICODE，这是因为第二个参数并不在ascii字符集范围内。

  • 函数返回结果的repertoire仅仅只受某一个参数字符集和校对集的影响
IF(column1 < column2, 'smaller', 'greater')

返回结果的repertoire是ASCII，这是因为两个字符串参数都是ASCII repertoire。函数的第一个参数并不会影响返回结果的repertoire。

2.2 UTF8 For Metadata

这里我们将utf8 for metadata翻译为采用utf8字符集的元数据。在MySQL中，任何描述一个数据库的数据都被称为元数据，这包括：列名、数据库名、用户名、版本名称、和大部分通过SHOW命令返回的字符串结果。此外，元数据还包括数据库INFORMATION_SCHEMA中所有的table的内容，这是因为这些表本身包含了数据库对象信息。

典型的metadata必须满足如下两个需求：

  • 所有的元数据必须有相同的字符集。否则，针对INFORMATION_SCHEMA数据库中的表不管是做SHOW命令操作，还是执行SELECT查询操作都可能会运行不正常，这是因为在这些表中即使针对同一列(column)，在不同的行都可能会有不同的字符集。

  • 元数据必须包含所有语言中的所有字符。否则，用户很可能不能用他们的本土语言来命名表和列。

为了满足这两个需求，MySQL采用UNICODE字符集（即UTF-8)来存储元数据。这样即使采用本土语言的话也不会造成任何问题。这里你应该记住，metadata都采用utf8字符集。

由于元数据上面的这些需求，使得USER()、CURRENT_USER()、SESSION_USER()、SYSTEM_USER()、DATABASE()和VERSION()这些函数的返回结果均采用utf8字符集编码。

MySQL server设置character_set_system系统变量的值为元数据的字符集：

mysql> SHOW VARIABLES LIKE 'character_set_system';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_system | utf8 |
+----------------------+-------+

虽然说元数据是采用UNICODE来存储的，但是这并不意味着执行查询操作时返回的column headers信息以及DESCRIBE函数的返回结果都必须与character_set_system所设置的字符集一致。当你使用SELECT column1 FROM t时，column1这个头本身也会从MySQL Server返回给客户端，而其字符集编码是受character_set_results系统变量决定的，该变量的默认值为utf8。假如你想要MySQL Server将元数据的结果以不同的字符集返回，那么可以使用SET NAMES语句来强制MySQL Server端进行字符集转换。SET NAMES命令会设置character_set_results系统变量和一些其他的相关系统变量。另一方面，客户端应用程序在从server端收到响应结果后，也可以进行字符集转换。值得指出的是在客户端进行转换通常会更加高效，但是并不能够保证所有的客户端都支持这样的转换。

假如character_set_results被设置为了NULL，则并不会进行转换，MySQL直接将元数据以其原始的字符集进行返回（即```character_set_system所设置的字符集)。

来自MySQL Server端的错误消息也与metadata一样自动的进行字符集转换。

假如在一条SQL语句中，你正使用类似于USER()这样的函数(注： 返回结果为utf8字符集)来比较或赋值，这时你并不需要担心，因为MySQL会自动的帮你进行转换：

SELECT * FROM t1 WHERE USER() = latin1_column;

上面这条语句之所以可以正常的工作，这是因为在比较之前，latin1_column的内容会自动转换成utf8字符集。

INSERT INTO t1 (latin1_column) SELECT USER();

上面这条语句之所以可以正常工作，是因为在进行赋值之前，USER()函数的返回结果会自动转换为latin1字符集。

尽管自动字符集转换本身并不属于SQL标准，标准并未说明任何一个所支持的字符集都是UNICODE字符集的子集。但是大家知道what applies to a superset can apply to a subset，因此我们相信针对unicode的校对集可以用在其他非Unicode类型的字符串上。

3. 设置字符集与校对集

在四个不同的层级（server、database、table、column）都有默认的字符集与校对集设置。在如下章节的描述尽管看起来可能很复杂，但实际上这样一个多级默认值可以让用户很明晰的知道返回结果的字符集。

CHARACTER SET子句被用于设置一个字符集。此外,CHARSET也被用于设置字符集，与CHARACTER SET等价。

字符集问题不仅会影响数据存储，也会影响客户端与MySQL服务器之间的通信。假如一个客户端应用程序与一个非默认字符集的MySQL Server进行通信，这时你需要知道确切的字符集。例如，如果要使用utf8 unicode字符集，客户端需要在连接MySQL服务器之后执行如下的命令：

SET NAMES 'utf8';

更多关于client/server通信之间的字符集问题，请参看如下的章节。

3.1 校对集命名规范

MySQL校对集的名字通常遵循如下规范：

  • 校对集的名字以其所对应的字符集名字开头，后面跟着一个或多个后缀以表明该校对集的特性。例如: utf8_general_ci与latin1_swedish_cli是针对utf8和latin1字符集的校对集。这里需要指出的是，binary字符集，它只有一个校对集，且名称也叫binary，并没有任何后缀。

  • 针对特定语言的校对集包含该语言名称。例如： utf8-turkish_ci与utf8_hungarian_ci按utf8字符集turkish、Hungarian校对集进行排序。

  • 校对集的后缀用于指明该校对集是否大小写敏感、音调敏感(accent sensitive)或者是直接binary比较。下表列出了后缀表示的一些含义：

mysql-collation-sense

对于非二进制(nonbinary)校对集名称来说，如果没有指定accent sensitiviey，那么其就由case sensitivity决定。假如一个校对集名称并未包含_ai或_as，则名字中的_ci后缀隐含着_ai, 名字中的_cs隐含着_as。例如，latin1_general_ci显示的指明了忽略大小写，同时隐含的指明了accent insensitive；而latin1_general_cs显示的指明了大小写敏感，同时隐含的指明了accent sensitive。

对于binary字符集的binary校对集，比较是基于字节数字的值来进行的。针对非二进制字符集(nobinary character set)的_bin校对集，比较是基于该字符编码的数值来进行的（有一些字符可能占用多个字节），

  • 针对unicode字符集来说，校对集的名称也可能会含有一个版本号，以表明采用的是哪个版本的UCA(Unicode Collation Algorithm)。基于UCA的校对集假如不包含版本的话，则默认采用的是UCA 4.0.0版本。

  • utf8_unicode_ci(并没有版本号）是基于UCA 4.0.0算法

3.2 Server级别字符集和校对集

MySQL Server有一个server字符集和server校对集。这可以在MySQL Server启动的时候通过命令行或者配置文件来设置，也可以在运行时通过命令来进行设置。

起初，server的字符集与校对集依赖于你启动ysqld时传入的选项，你可以使用--character-set-server来设置server字符集。另外，你也可以在后面添加--collation-server来设置校对集。假如你并未进行设置的话，则等价于设置为--character-set-server=latin1。假如你只设置了字符集（例如:latin1)，并未设置校对集，则等价于设置:

--character-set-server=latin1 --collationserver=latin1_swedish_ci

这是因为latin1的默认校对集为latin1_swedish_ci。因此，如下三个命令的含义是一致的：

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

其中一种方法来改变mysqld启动时的默认值就是通过重新编译源代码：

cmake . -DDEFAULT_CHARSET=latin1

或
cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci

mysqld与cmake都会检查字符集/校对集的组合是否有效，假如无效的话，两个程序都会打印出相关的错误信息并退出。

假如在通过CREATE DATABASE子句创建数据库的时候未指定数据库的字符集与校对集，则默认会采用server的字符集与校对集。

当前运行的MySQL Server的字符集与校对集是由character_set_server与collation_server这两个系统变量决定的，这些值可以在运行时被更改。

3.3 数据库级别字符集与校对集

每一个数据库都有一个数据库级别的字符集与校对集。可以在执行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]

这里假如将关键字DATABASE换成SCHEMA,则是创建数据库schema。

所有的数据库选项都存放在一个名为db.opt的文本文件中，该文件可以在数据库目录中找到。上面的CHARACTER SET与COLLATE子句可以使得在创建数据库时指定不同的字符集与校对集。例如：

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

MySQL按如下的步骤来选择的数据库的字符集与校对集：

  • 假如CHARACTER SET charset_name与COLLATE collation_name都被指定，则charset_name字符集、collation_name校对集会被使用；

  • 假如只指定了CHARACTER SET charset_name，而并未指定COLLATE，那么charset_name字符集会被使用，并采用该字符集默认的校对集。要查看一个字符集所对应的默认校对集可以通过执行SHOW CHARACTER SET命令或者查询INFORMATION_SCHEMA CHARACTER_SETS表。

  • 假如只指定了COLLATE collation_name，并未指定CHARACTER SET，则与collation_name所关联的字符集会被使用，校对集使用collation_name。

  • 否则（CHARACTER SET与COLLATE均为被指定），采用server的字符集与校对集

默认数据库(default database)的字符集与校对集由character_set_database与collation_database这两个系统变量决定。无论什么时候默认数据库改变，MySQL server都会去设置这两个值。假如没有默认数据库的话，则这两个值与系统级别的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';

数据库的字符集与校对集会从以下方面影响MySQL Server的操作：

  • 对于CREATE TABLE语句，假如在创建表的时候并未指定字符集与校对集，则会继承所对应库的字符集与校对集。如果要覆盖该继承，则需要在创建表的时候显示通过CHARACTER SET与COLLATE来指定。

  • 针对LOAD DATA命令，如果并未指定CHARACTER SET子句，则MySQL server会使用character_set_database系统变量所指定的字符集来解释文件内容。如果要覆盖此字符集，需要提供一个显示的CHARACTER SET子句。

  • 针对存储方法（存储过程或函数），在该方法创建时并未指定CHARACTER SET与COLLATE属性的话，那么则会采用数据库的字符集与校对集。

3.4 表级别字符集与校对集

每一个表都有对应的字符集与校对集。在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会按照如下的方式来选择表的字符集与校对集：

  • 假如在创建表时同时指定了CHARACTER SET charset_name与COLLATE collation_name，则所指定的charset_name及collation_name会被使用；

  • 假如创建表时只指定了CHARACTER SET charset_name，并未指定COLLATE，那么charset_name默认的校对集将会被使用。要查看一个字符集所对应的默认校对集可以通过执行SHOW CHARACTER SET命令或者查询INFORMATION_SCHEMA CHARACTER_SETS表。

  • 假如只指定了COLLATE collation_name，并未指定CHARACTER SET，则与collation_name所关联的字符集会被使用，校对集使用collation_name。

  • 否则（CHARACTER SET与COLLATE均为被指定），采用database的字符集与校对集

假如一个表的列(column)并未指定字符集与校对集的话，那么将会使用该表对应的字符集与校对集。注意：表的字符集与校对集是MySQL的扩展，标准的SQL是没有表级别的字符集与校对集的。

3.5 列级别字符集与校对集

每一个character列(即类型为CHAR、VARCHAR或TEXT)都有一个列级别的字符集与校对集。可以在定义列时加上字符集与校对集选项：

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会按照如下的方式来选择表的字符集与校对集：

  • 假如在创建列时同时指定了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;

上面指定了该列的字符集与校对集，因此会使用utf8字符集utf8_unicode_ci校对集。

  • 假如创建列时只指定了CHARACTER SET charset_name，并未指定COLLATE，那么charset_name默认的校对集将会被使用。
CREATE TABLE t1
(
col1 CHAR(10) CHARACTER SET utf8
) CHARACTER SET latin1 COLLATE latin1_bin;

这里在创建列时只指定了字符集，并未指定校对集。因此会使用utf8字符集，采用utf8默认的校对集utf8_general_ci。要查看一个字符集所对应的默认校对集可以通过执行SHOW CHARACTER SET命令或者查询INFORMATION_SCHEMA CHARACTER_SETS表。

  • 假如只指定了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;

这里在创建列时只指定了校对集，并未指定字符集。校对集是utf8_polish_ci，与该校对集关联的字符集是utf8。

  • 否则（CHARACTER SET与COLLATE均为被指定），则继承table的字符集与校对集
CREATE TABLE t1
(
col1 CHAR(10)
) CHARACTER SET latin1 COLLATE latin1_bin;

上面创建列时，并未指定字符集与校对集，因此采用表级别的字符集latin1与校对集latin1_bin。

CHARACTER SET与COLLATE是标准SQL所支持的。假如你使用ALTER TABLE来将一列从一种字符集转换成另一种字符集，MySQL首先会阐释映射该值，但是假如字符集不兼容的话，则可能产生数据丢失。

3.6 字符串常量的字符集与校对集

每一个字符串常量都有一个对应的字符集与校对集。

对于SELECT 'string'这样一个简单的子句，string具有该连接(connection)所默认的字符集与校对集，连接默认的字符集与校对集可以通过系统变量character_set_connection和collation_connection来获取。

一个字符串常量也有一个可选的字符集说明（introducer)与COLLATE子句，这样可以表明该字符串常量所采用的字符集与校对集：

[_charset_name]'string' [COLLATE collation_name]

这里_charset_name就是上面提到的introducer。它告诉MySQL分析器“接下来的字符串使用的是charset_name字符集”。这里需要注意的是，introducer只是一个指示作用，并不会像CONVERT()函数那样将该字符串转换为introducer字符集。例如：

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

字符集introducer和COLLATE子句的实现是依据标准SQL文档的。

MySQL会按照如下的方式来决定一个字符串常量的字符集与校对集：

  • 假如同时指定了_charset_name与COLLATE collation_name，则字符集charset_name和校对集collation_name将会被使用。collation_name必须是该charset_name所支持的校对集。

  • 假如_charset_name被指定，但是COLLATE未被指定的话，则字符集charset_name与其默认的校对集将会被使用。要查看一个字符集所对应的默认校对集可以通过执行SHOW CHARACTER SET命令或者查询INFORMATION_SCHEMA CHARACTER_SETS表。

  • 假如只指定了COLLATE collation_name，但是并未指定charset_name，那么由character_set_connection系统变量所指定的默认字符集与collation_name校对集将会被使用。这里注意collation_name必须要被该默认字符集所支持。

  • 否则（_charset_name与COLLATE collation_name均未被指定），由系统变量character_set_connection与collation_connection所指定的默认字符集与校对集将会被使用。

例如：

  • 针对一个非二进制字符串，指定了latin1字符集和latin1_german1_ci:
SELECT _latin1'Müller' COLLATE latin1_german1_ci;
  • 针对一个非二进制字符串，指定了utf8字符集和默认的校对集(utf8默认的校对集是utf8_general_ci)：
SELECT _utf8'Müller';
  • 二进制字符串采用binary字符集和其默认的校对集(binary):
SELECT _binary'Müller';
  • 非二进制字符串采用该连接默认的字符集和utf8_general_ci校对集（注意：假如连接默认的字符集不是utf8的话，将会失败):
SELECT 'Müller' COLLATE utf8_general_ci;
  • 使用连接默认的字符集与校对集：
SELECT 'Müller';

introducer用于指示接下来的字符串常量所采用的字符集，但是并不会改变SQL解析器对该字符串常量转义的处理，转义总是由解析器根据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'，该转义字符表示一个换行符。该转义序列通过character_set_connection所设置的latin1字符集被解释成为一个换行符(十六进制值为0A)。这即使在第二个字符串_sjis'à\n'也是正确的。也就是说,_sjis这个introducer并不会影响解析器对转义字符的解析。

例2

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

这里character_set_connection是sjis，在该字符集下序列à\(十六进制是E0和5C)是一个有效的宽字节字符。因此，开始的两个字节会被解释成为一个单独的sjis字符，这里\并不会被解释为转义字符。接下来的n(十六进制为6E)也不再是转义序列的一部分。因此这里可以看到_latin1这个introducer并不会影响解析器对转义字符的处理。

3.7 The National Character Set

标准的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

你也可以使用N'literal'(或n'literal')来创建一个national字符集。例如：

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

上面三个是等价的。

3.8 Character Set Introducers

一个string literal、hexadecimal literal、bit-value literal都可以添加一个字符集说明符和COLLATE子句。语法如下：

[_charset_name] literal [COLLATE collation_name]

例如：

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;

而MySQL对决定这些字面量的字符集与校对集的方法与字符串常量的类似，这里不再赘述。

3.9 指定字符集与校对集示例

1) 例1

CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

这里我们给列c1指定了latin1字符集和latin1_german1_ci校对集。这里在定义中显示的直接指定。需要注意的是，我们这里将一个字符集为latin1的列存放在字符集为latin2的表中，这样并不会产生任何问题。

2) 例2

CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

这里我们对列c1使用latin1字符集和默认的校对集。这里的默认校对集是指latin1对应的默认校对集latin1_swedish_ci.

3) 例3

CREATE TABLE t1
(
c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

我们这里定义了一列c1，其拥有默认的字符集与校对集。在这种情况下，MySQL会检查表级别的字符集与校对集，因此这里列c1的的字符集是latin1、校对集是latin1_danish_ci。

4） 例4

CREATE DATABASE d1
DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
c1 CHAR(10)
);

这里我们创建列c1时并未指定任何的字符集与校对集。我们也并未在表级别指定字符集与校对集，在这种情况下，MySQL会检查数据库级别的字符集与校对集，从而决定表级别的字符集与校对集，然后再决定列级别。因此，这里列c1的字符集是latin2、校对集是latin2_czech_ci。

4. 连接(Connection)的字符集与校对集

一个connection就是指一个客户端应用程序连接到MySQL服务器，并与其所交互的服务器开启的一个会话。客户端通过该会话连接发送SQL语句，例如查询语句。然后服务器通过该连接返回响应结果或者错误消息。本节主要包括如下几个方面的内容：

  • 连接字符集与校对集系统变量(connection character set and collation system variables)

  • 用于配置连接字符集的SQL语句（SQL Statements for Connection Character Set Configuration)

  • 客户端应用程序连接字符集配置（Client Program Connection Character Set Configuration)

  • 用于错误处理的连接字符集配置（Connection Character Set Configuration Error Handling)

4.1 Connection字符集与校对集系统变量

有多个MySQL客户端与服务端交互相关的字符集/校对集系统变量。其中的一些我们前面提到过：

  • character_set_server和collation_server系统变量用于指示服务器端的字符集与校对集；

  • character_set_database和collation_database系统变量用于指示默认数据库的字符集与校对集；

另外，字符集与校对集系统变量也涉及到client/server之间的消息处理。每一个客户端都有一个针对该连接的会话字符集与校对集系统变量(session-specific character set and collation)。这些会话系统变量会在连接的时候被初始化，但是可以在会话过程中被更改。

有多个关于客户端连接(client connection)方面的字符集与校对集方面的问题，可以通过系统变量来进行回答：

  • 当SQL语句离开客户端之后，它是采用什么样的字符集？

MySQL Server会使用character_set_client系统变量来决定客户端发送过来的SQL语句的字符集。

  • 在服务器接收到客户端发送过来的SQL语句后，MySQL Server采用何种字符集来解析它们？

为了确定要用何种字符集，MySQL Server会根据character_set_connection和collation_connection系统变量：

1） MySQL Server会将Client发送的SQL语句从character_set_client转换成character_set_connection字符集。这里有一个特例需要注意，针对带有introducer的字符串字面量，比如_utf8mb4或者_latin2，此时MySQL Server采用introducer所指定的字符集与校对集来解析该字符串字面量。

2） collation_connection对于比较字符串字面量是很重要的。对于列(column)类型是字符串的值，采用的是该列本身的校对集来比较，而不是用collation_connection指定的校对集。

  • 在MySQL将查询结果返回给客户端之前，应当对查询结果采用何种字符集？

character_set_results系统变量用于决定MySQL Server采用何种字符集将查询结果返回给客户端。返回值包括：result data(比如column values)、results metadata(比如column names)、错误消息(error messages)。


客户端可以对这些系统变量值进行微调，或者是直接采用默认值。假如你不想使用默认值，你必须为每一个连接到服务器连接进行字符设置。

4.2 配置连接字符集的SQL语句

有两个SQL语句可用于设置连接相关的字符集系统变量：

  • SET NAMES ‘charset_name’ [COLLATE ‘collation_name’]

这里SET NAMES用于指示将采用何种字符集向MySQL服务器发送SQL语句。因此，SET NAMES 'cp1251'这样一条SQL语句告诉MySQL服务器，后序从本客户端发送出去的消息都采用cp1251字符集。它也用于指定服务器应该以何种字符集来返回结果信息（例如，当你执行SELECT语句返回查询结果的时候，其用于指定采用何种字符集）

一条SET NAMES 'charset_name'语句等价于如下三条语句：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

这里设置character_set_connection为charset_name，也隐含着将collation_connection设置为charset_name所默认的校对集。我们一般并不需要显示的设定collation。我们我们要显示的为collation_connection设定校对集，可以通过如下：

SET NAMES 'charset_name' COLLATE 'collation_name'
  • SET CHARACTER SET ‘charset_name’

SET CHARACTER SET类似于SET NAMES，但是将character_set_connection和collation_connection的值设置为character_set_database和collation_database（即用于设置默认数据库的字符集与校对集）。

一条SET CHARACTER SET charset_name语句等价于如下三条语句：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

设置collation_connection也隐含着设置character_set_connection。（因为一个collation默认对应着一个字符集）。这里我们并没有必要显示的设置character_set_connection。


如果要查看当前我们为连接所设置的字符集与校对集，使用如下命令：

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

另外，假如你并不想要MySQL Server在返回结果或者错误消息时进行字符集相关的转换，那么可以将character_set_results设置为NULL或者binary:

SET character_set_results = NULL;
SET character_set_results = binary;

4.3 配置客户端程序的连接字符集

客户端应用程序如mysql, mysqladmin, mysqlcheck, mysqlimport, 和 mysqlshow按如下的步骤来决定默认字符集：

  • 假如没有任何其他信息，默认情况下每个客户端在编译时设置的默认字符集为latin1;

  • 上述每一个客户端应用都会根据操作系统的设置自动检测使用哪种字符集，例如在Unix系统下检测LANG或LC_ALL这样的环境变量。

  • 上述每一个客户端应用程序都支持一个--default-character-set选项，该选项使用户能够显示的指定采用何种字符集。

对于mysql这一客户端工具，你可以在配置文件中进行如下配置：

[mysql]
default-character-set=koi8r

也可以直接在连接上之后，通过执行SET NAMES语句进行修改。

5. 配置应用程序字符集与校对集

对于应用程序来说，如果采用MySQL默认的字符集与校对集（latin1, latin1_swedish_ci)，那么一般不需要做任何的配置。假如应用程序需要用不同的字符集与校对集来存储数据，那么你可以通过如下几种方式来进行字符集的配置：

  • 为每个数据库指定字符设置。
CREATE DATABASE mydb
CHARACTER SET utf8
COLLATE utf8_general_ci;
  • 在MySQL Server启动的时候，为server指定字符集配置；
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
  • 在编译MySQL源代码的时候指定默认的字符集配置；
cmake . -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci



[参看]:

  1. MySQL教程

  2. MySQL 里设置或修改系统变量的几种方法

  3. MySQL 5.6 Reference Manual SET/SHOW的使用(p1842)