MySQL设置字符集的参数控制了哪些行为

故障现象描述

在向MySQL导入数据时,先设置set names gbk,然后通过source导入一个很大的SQL文件 (文件字符集为gbk),发现如下行为:
1 正常情况下,SQL文件中的SQL以分号分割,发往MySQL的每一个数据包会带有一个SQL。
2 在语句"INSERT INTO ... VALUES(...,'璡',...); "之后,所有数据会打包在一起,通过大数据包协议一起发往MySQL (若单个数据大于16M,MySQL则使用大数据包协议,参考[1])。

初步猜测, 认为是字符集的设置问题。

MySQL的字符集参数

在谈及MySQL字符集时,还必须介绍校验集。字符集(character set)表示字符以何种规则进行编码,校验集(collation)表示字符以何种规则进行比较和排序 (例如:是否大小写敏感)。

MySQL有以下字符集相关的设置:
1 三组设置字符集+校验集的参数:
character_set_connection/collation_connection
character_set_server/collation_server
character_set_database/collation_database,此组参数已被废弃。
2 四个只能设置字符集的参数:
character_set_client
character_set_results
character_set_filesystem
character_set_system,此参数值固定为utf8,且不可改变。本文不涉及此项。
3 MySQL client中, 有一个内存变量charset_info,本文中称其为mysql.client.charset
4 存储层:数据库/数据表/数据列 均由单独的字符集+校验集参数,通过CREATE语句可进行设置。MySQL文档中有详细记述。

(后文描述字符集+校验集时,以字符集为例,校验集的出现位置与对应的字符集相同)

各种配置的关系如下图所示,下图示例为用source命令导入sql.txt,其中包含一个SELECT...INTO OUTFILE语句:

(为描述方便,之后将character_set_xxx简写为cs_xxx)
说明:
1 client从sql.txt读取SQL。SQL在sql.txt中遵循文件的字符集,client按照mysql.client.charset进行读取。
2 client将SQL发往server。SQL按照cs_client字符集进行发送。server接收SQL后, 将其中的 字符串常量 转换成cs_connection字符集。
3 server接收SQL后, 将其中的 文件名常量 转换成cs_filesystem
4 server将常量传给存储层InnoDB时, 需将常量转换成存储层的字符集。
5 SQL中的文件名,以cs_filesystem字符集写入文件系统。
6 若查询结果要存入文件,可在SELECT...INTO...语句中指定字符集,或默认使用binary。
7 若查询结果直接返回client,则将结果转换为cs_results后返回。

关于存储层的字符集:
数据库/数据表/数据列 级别的字符集可分别指定,如图中左下部分所示,子级别可指定字符集 或 从父级别继承。
其中cs_database已被废弃,但尚未移除。

关于字符集的设置:除直接设置变量,字符集的常见设置方法为:
client连接握手时指定字符集,通过--default-character-set参数启动client可设置。
SET NAMES 语句
SET CHARSET 语句

其三种设置方式对参数的影响参看图中最后的列表, 可以看出:
SET NAMES 并不会影响客户端解析SQL文件的字符集mysql.client.charset

故障分析

了解MySQL的字符集各项参数后,故障的原因就比较明显了:
1 SET NAMES并不影响mysql.client.charset,因此MySQL解析sql.txt时,使用了默认字符集utf8。
2 "璡"字的二进制编码为ad5c,5c的对应字符为""。因此"(璡)(单引号)"会被误读为"(之前字符的编码xx + ad对应的字符)(被转义的单引号)",导致后面的SQL因为单引号不封闭而被认为是一个字符串,因此MySQL client无法正确切分SQL。

回顾

通过之前的说明,可以猜测MySQL的字符串参数的意图:

  • 可以 设置字符集+校验集 的机制,都需要进行字符串的比较。比如cs_connection 可用于 WHERE 条件中的比较运算,存储层的字符集 可用于 存储内的排序操作。
  • 可以 设置字符集但不能设置校验集 的机制,都跟外部系统相关 (相对于MySQL server)。比如cs_clientcs_results跟MySQL client相关,cs_filesystem与服务器的文件系统相关。
  • mysql.client.charset在MySQL文档中并未有介绍,但会影响MySQL client对SQL文件的解析。

扩展阅读

引用自:微信公众号"图解MySQL"

posted @ 2020-07-09 10:16  lzmbdr  阅读(172)  评论(0编辑  收藏  举报