最近一直在弄一个有关 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 这样选择数据库字符集和数据库校对规则：
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 按照下面的方式选择表字符集和校对规则：
如果在列定义中没有指定列字符集和校对规则，则默认使用表字符集和校对规则。表字符集和校对规则是 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 和 COLLATE 子句是标准的 SQL 。
5、连接字符集和校对
一些字符集和校对规则系统变量与客户端和服务器的交互有关，前面已经提到：
在客户端和服务器的连接处理中也涉及了字符集和校对规则变量。每一个客户端有一个连接相关的字符集和校对规则变量。
考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送 SQL 语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集。对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决：
服务器使用 character_set_client 变量作为客户端发送的查询中使用的字符集。
转换时，服务器使用 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
如果不希望服务器执行任何转换，设置 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 这样确定一个文字字符集和校对规则：
select_latin1'Müller' COLLATE latin1_german1_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' 都有 校对规 则，哪个校对规则优先？这可能比较难解决，但是以下规则适合大多数情况：
上述可压缩性值是 mysql 当前所用的。
这样上述规则可以模糊解决：
使用 COERCIBILITY （）函数确定一个字符串表达式的可压缩性：
mysql> SELECT COERCIBILITY('A' COLLATE latin1_swedish_ci);-> 0mysql> SELECT COERCIBILITY(VERSION());-> 3mysql> SELECT COERCIBILITY('A');-> 4
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
