mysql字符集设置_MySQL

最近一直在弄一个有关 mysql proxy 的项目，中间涉及到对 sql 语句的解析。之前对 mysql 了解的并不是太多，随着项目的推进，不得不补补了。今天突然看到 mysql 的 charset 设置，自己很多地方都没法解释清楚，对一些相关的 sql 语句背后的工作机制一知半解，于是找了点资料补习下，顺便做点儿笔记。

一、字符集和校对规则

字符集是一套符号和编号。校对规则是在字符集内用于比较字符的一套规则。

字符（ character) 是人类 语言中最小的表义符号。例如字母 ’A’、’B’等；给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码 (Encoding) 。例如，我们给字符 ’A’赋予数值 0 ，给字符 ’B’赋予数值 1 ，则 0 就是字符 ’A’的编码， 1 就是字符 ’B’的编码；给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集 (Character Set) 。

校对规则 (collation) 是指在同一字符集内字符之间的比较规则；确定校对规则后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系；每个校对规则唯一对应一种字符集，但一个字符集可以对应多种校对规则，其中有一个是默认校对规则 (Default Collation) 。

mysql 能够做这些事情：

• 使用多种字符集来存储字符串
• 使用多种校对规则来比较字符串
• 在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字 符串
• 允许定义任何级别的字符集和校对规则

二、mysql 的字符集和校对

mysql 服务器能够支持多种字符集，可以使用 show character set; 语句列出可用的字符集：

要想列出校对规则可以使用 show collation; 语句：

两个不同的字符集不能有相同的校对规则；另外， mysql 中的校对规则名称遵从命名惯例：它们以对应的字符集名称开头；以 _ci( 大小写不敏感 ) 、 _cs( 大小写敏感 ) 或 _bin( 按编码值比较 ) 结尾。

mysql 的字符集和校对规则有 4 个级别的默认设置：服务器级、数据库级、表级和连接级。

1 、服务器字符集和校对

mysql 服务器有一个服务器字符集和一个服务器校对规则，它们均不能设置为空。

mysql 按照如下方法确定服务器字符集和服务器校对规则：

• 当服务器启动时根据有效的选项设置
• 根据运行时的设定值

当启动 mysqld 时，根据使用的初始选项设置来确定服务器字符集和校对规则。可以使用 --default-character-set 设置字符集，并且可以在字符集后面为校对规则添加 --default-collation 。如果没有指定一个字符集，那就与 --default-character-set=latin1 相同。如果仅指定了一个字符集（例如， latin1 ），但是没有指定一个校对规则，那就与 --default-charset=latin1 --default- collation=latin1_swedish_ci 相同，因为 latin1_swedish_ci 是 latin1 的默认校对规则。因此，以下三个命令有相 同的效果：

shell> mysqldshell> mysqld --default-character-set=latin1shell> mysqld --default-character-set=latin1 --default-collation=latin1_swedish_ci

如果希望在从源程序构建时更改默认服务器字符集和校对规则，可以通过重新编译，使用： --with-charset 和 --with-collation 作为 configure 的参量。例如：

shell> ./configure --with-charset=latin1

或者：

shell> ./configure --with-charset=latin1 --with-collation=latin1_german1_ci

mysqld 和 configure 都验证字符集 / 校对规则组合是否有效。如果无效，每个程序都显示一个错误信息，然后终止。

当前的服务器字符集和校对规则可以用作 character_set_server 和 collation_server 系统变量的值。在运行时能够改变这些变量的值，从而改变运行时的服务器字符集和校对规则。

2、数据库字符集和校对

每一个数据库有一个数据库字符集和一个数据库校对规则，它不能够为空。

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];

create database db_name default CHARACTER SET latin1 COLLATE latin1_swedish_ci;

mysql 这样选择数据库字符集和数据库校对规则：

• 如果指定了 CHARACTER SET X 和 COLLATE Y ，那么采用字符集  X  和校对规则  Y  。
• 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y ，那么采用 CHARACTER SET X 的默认校对规则。
• 否则，采用服务器字符集和服务器校对规则。

mysql 的 create database ... default CHARACTER SET ... 语法与标准 sql 的 create table ... CHARACTER SET ... 语法类似。因此，可以在同一个 mysql 服务器上创建使用不同字符集和校对规则的数据库。

如果在 create table 语句中没有指定表字符集和校对规则，则使用数据库字符集和校对规则作为默认值。

默认数据库的字符集和校对规则可以用作 character_set_database 和  collation_database 系统变量。无论何时默认数据库更改了，服务器都设置这两个变量的值。如果没有默认数据库，这两个变量与相应的服务器级别的变量（ character_set_server 和 collation_server ）具有相同的值。

3、表字符集和校对

每一个表有一个表字符集和一个校对规则，它不能为空。

为指定表字符集和校对规则， 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 ( ... ) default CHARACTER SET latin1 COLLATE latin1_danish_ci;

mysql 按照下面的方式选择表字符集和校对规则：

• 如果指定了 CHARACTER SET X 和 COLLATE Y ，那么采用 CHARACTER SET X 和 COLLATE Y 。
• 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y ，那么采用 CHARACTER SET X 的默认校对规则。
• 否则，采用服务器字符集和服务器校对规则。

如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是 mysql 的扩展 ; 在标准 SQL 中没有。

4、列字符集和校对

每一个“字符”列（即， CHAR 、 VARCHAR 或 TEXT 类型的列）有一个列字符集和一个列校对规则，它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则：

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

create table table1(column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci);

mysql 按照下面的方式选择列字符集和校对规则：

• 如果指定了 CHARACTER SET X 和 COLLATE Y ，那么采用 CHARACTER SET X 和 COLLATE Y 。
• 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y ，那么采用 CHARACTER SET X 的默认校对规则。
• 否则，采用表字符集和校对规则。

CHARACTER SET 和 COLLATE 子句是标准的 SQL 。

5、连接字符集和校对

一些字符集和校对规则系统变量与客户端和服务器的交互有关，前面已经提到：

• 服务器字符集和校对规则可以用作 character_set_server 和 collation_server 变量的值。
• l默认数据库的字符集和校对规则可以用作 character_set_database 和 collation_database 变量的值。

在客户端和服务器的连接处理中也涉及了字符集和校对规则变量。每一个客户端有一个连接相关的字符集和校对规则变量。

考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送 SQL 语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集。对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决：

• 当查询离开客户端后，在查询中使用哪种字符集？

服务器使用 character_set_client 变量作为客户端发送的查询中使用的字符集。

• l服务器接收到查询后应该转换为哪种字符集？

转换时，服务器使用 character_set_connection 和 collation_connection 系统变量。它将客户端发送的查询从 character_set_client 所指示的字符集转换到 character_set_connection 所指示的字符集（除非字符串文字具有象 _latin1  或 _utf8 的引介词）。 collation_connection 对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的校对规则优先级。

进行内部操作前将请求数据从 character_set_connection 转换为内部操作字符集，其确定方法如下：

a、使用每个数据字段的 CHARACTER SET 设定值；

b、若上述值不存在，则使用对应数据表的 DEFAULT CHARACTER SET 设定值 (mysql 扩展，非 sql 标准 ) ；

c、若上述值不存在，则使用对应数据库的 DEFAULT CHARACTER SET 设定值；

d、若上述值不存在，则使用 character_set_server 设定值。

• 服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？

character_set_results 变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。

能够调整这些变量的设置，或可以依赖默认值。

有两个语句影响连接字符集：

set names ' charset_name ' SET CHARACTER SET charset_name;

set names 显示客户端发送的 sql 语句中使用什么字符集。因此， set names 'cp1251' 语句告诉服务器“将来从这个客户端传来的信息采用字符集 cp1251 ”。它还为服务器发送回客户端的结果指定了字符集。（例如，如果你使用一个 SELECT 语句，它表示列值使用了什么字符集）

set names ' x ' 语句与这三个语句等价：

set character_set_client = x ; set character_set_results = x ; set character_set_connection = x ;

将 x 设置为 character_set_connection 也就设置了 collation_connection 是 x 的默认校对规则。

set CHARACTER SET 语句是类似的，但是为默认数据库设置连接字符集和校对规则。 set CHARACTER SET x 语句与这三个语句等价：

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

当一个客户端连接时，它向服务器发送希望使用的字符集名称。服务器为那个字符集设置  character_set_client 、 character_set_results 和  character_set_connection 变量。（实际上，服务器为使用该字符集执行一个 set names 操作。）

对于 mysql 客户端，如果你希望使用与默认字符集不同的字符集，不需要每次启动时执行 set names 语句。可以在 mysql 语句行中或者选项文件中添加一个 --default-character-set 选项设置。例如，你每次运行 mysql 时，以下的选项文件设置把三个字符集变量修改为 utf8 ：

[mysql]default-character-set=utf8

例如：假设 column1 定义为 CHAR(5) CHARACTER SET latin2 。如果没有设定 set names 或 set CHARACTER SET ，那么对于 select column1 from t ，当连接后，服务器使用客户端指定的字符集返回列 column1 的所有值。另一方面，如果你设定 set names 'latin1' 或 set CHARACTER SET latin1 ，那么发送结果之前，服务器转换 latin2 值到 latin1 。转换可能会丢失那些不属于两种字符集的字符。

如果不希望服务器执行任何转换，设置 character_set_results 为 NULL ：

mysql> SET character_set_results = NULL;

6、字符串文字字符集和校对

每一字符串字符文字有一个字符集和一个校对规则，它不能为空。

一个字符串文字可能有一个可选的字符集引介词和 COLLATE 子句：

[_ charset_name ] ' string ' [COLLATE collation_name ]

select ' string ';select _latin1 ' string ';select _latin1' string ' COLLATE latin1_danish_ci;

对于简单的语句 select ' string ' ，字符串使用由 character_set_connection 和 collation_connection 系统变量定义的字符集和校对规则。

_ charset_name 表达式正式称做一个引介词。它告诉解析程序，“后面将要出现的字符串使用字符集 X 。”引介词在标准十六进制字母和数字十六进制符号（ x ' literal ' 和  0x nnnn  ）中是合法的。

select _latin1 x'AABBCC'; select _latin1 0xAABBCC;

MySQL 这样确定一个文字字符集和校对规则：

• 如果指定了 CHARACTER SET X 和 COLLATE Y ，那么使用 CHARACTER SET X 和 COLLATE Y 。
• 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y ，那么使用 CHARACTER SET X 的默认校对规则。
• 否则，使用通过 character_set_connection 和 collation_connection 系统变量给出的字符集和校对规则。

• 使用 latin1 字符集和 latin1_german1_ci 校对规则的字符串：

select_latin1'Müller' COLLATE latin1_german1_ci;

• 使用 latin1 字符集和其默认校对规则的字符串（即， latin1_swedish_ci ）：

select_latin1'Müller';

• 使用连接默认字符集和校对规则的字符串：

select 'Müller';

字符集引介词和 COLLATE 子句是根据标准 SQL 规范实现的。

COLLATE 可以用于多种 SQL 语 句中： order by 、 as 、 group by 、聚合函数、 distinct 、 where 、 having 等。 COLLATE 子句有较高的优先级（高于 || ）。

BINARY 操作符是 COLLATE 子句的一个速记符。 BINARY ' x ' 等价与 ' x ' COLLATE y  ，这里 y 是字符 集 ' x ' 二元校对规则的名字。每一个字符集有一个二元校对规则。例如， latin1 字符集的二元校对规则是 latin1_bin ，因此，如果列 a 是字符集 latin1 ，以下两个语句有相同效果： 

select * from t1 order by BINARY a;select * from t1 order by a COLLATE latin1_bin;

7、校对规则的“可压缩性”

在绝大多数查询中， mysql 使用哪种校对规则进行比较是很显然的。例如，在下列情况中，校对规则明显的是“列 x 的列校对规则”：

select x from T order by x;select x from T where x = x;select distinct x from T;

但是，当涉及多个操作数时，可能不明确。例如：

select x from T where x = 'Y';

这个查询应该使用列 x 的校对规则，还是字符串文字 'Y' 的校对规则？

标准化 SQL 使用“可压缩性”规则解决这种问题。基本上，这个意思是：既然 x 和 'Y' 都有 校对规 则，哪个校对规则优先？这可能比较难解决，但是以下规则适合大多数情况：

• 一个外在的 COLLATE 子句可压缩性是 0 （根本不能压缩。）
• 使用不同校对规则的两个字符串连接的可压缩性是 1 。
• 列校对规则的可压缩性是 2 。
• “系统常数”（如 USER() 或 VERSION() 函数返回的字符串）可压缩性是 3 。
• 文字规则的可压缩性是 4 。
• NULL 或从 NULL 派生的表达式的可压缩性是  5 。

上述可压缩性值是 mysql 当前所用的。

这样上述规则可以模糊解决：

• 使用最低的可压缩性值的校对规则。
• 如果两侧有相同的可压缩性，那么如果校对规则不同则发生错误。

使用 COERCIBILITY （）函数确定一个字符串表达式的可压缩性：

mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);-> 0mysql> SELECT COERCIBILITY(VERSION());-> 3mysql> SELECT COERCIBILITY('A');-> 4