图解MySQL | [原理解析] 设置字符集的参数控制了哪些行为

最新推荐文章于 2024-04-02 16:41:38 发布

爱可生开源社区

最新推荐文章于 2024-04-02 16:41:38 发布

阅读量201

点赞数

分类专栏： MySQL 文章标签： MySQL 数据库图解

MySQL 专栏收录该内容

195 篇文章 10 订阅

订阅专栏

本文转载自[图解MySQL]，原创作者：王悦，黄炎，周海鸣

引言

本文是由爱可生研发团队出品的「图解MySQL」系列文章，不定期更新，但篇篇精品。

爱可生开源社区持续运营维护的小目标：

每周至少推送一篇高质量技术文章

每月研发团队发布开源组件新版

每年1024开源一款企业级组件

2019年至少25场社区活动

欢迎大家持续关注～

本文以 一个字符集设置引起的故障现象 入手, 介绍 MySQL字符集的各个参数 的作用。

故障现象描述

在向MySQL导入数据时，先设置 set names gbk ，然后通过source导入一个很大的SQL文件 (文件字符集为gbk)，发现如下行为：

正常情况下，SQL文件中的SQL以分号分割，发往MySQL的每一个数据包会带有一个SQL。
在语句"INSERT INTO ... VALUES(...,'璡',...); "之后，所有数据会打包在一起，通过大数据包协议一起发往MySQL (若单个数据大于16M，MySQL则使用大数据包协议，参考[1])。

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

MySQL的字符集参数

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

MySQL有以下字符集相关的设置：

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

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

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

(为描述方便，之后将 character_set_xxx 简写为 cs_xxx )

说明:

client从sql.txt读取SQL。SQL在sql.txt中遵循文件的字符集，client按照 mysql.client.charset 进行读取。
client将SQL发往server。SQL按照 cs_client 字符集进行发送。server接收SQL后, 将其中的字符串常量 转换成 cs_connection 字符集。
server接收SQL后, 将其中的 文件名常量 转换成 cs_filesystem 。
server将常量传给存储层InnoDB时, 需将常量转换成存储层的字符集。
SQL中的文件名，以 cs_filesystem 字符集写入文件系统。
若查询结果要存入文件，可在 SELECT...INTO... 语句中指定字符集，或默认使用binary。
若查询结果直接返回client，则将结果转换为 cs_results 后返回。

关于存储层的字符集:

数据库/数据表/数据列级别的字符集可分别指定，如图中左下部分所示，子级别可指定字符集或从父级别继承。

其中 cs_database 已被废弃，但尚未移除。

关于字符集的设置：除直接设置变量，字符集的常见设置方法为:

client连接握手时指定字符集，通过 --default-character-set 参数启动client可设置。
SET NAMES 语句
SET CHARSET 语句

其三种设置方式对参数的影响参看图中最后的列表, 可以看出：

SET NAMES 并不会影响客户端解析SQL文件的字符集 mysql.client.charset
SET CHARSET 会将 cs_connection 设置成 cs_database 的值，而并非设置的字符集。

故障分析

了解MySQL的字符集各项参数后，故障的原因就比较明显了：

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

回顾

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

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

扩展阅读

https://dev.mysql.com/doc/refman/5.7/en/charset.html

https://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html

[1] https://dev.mysql.com/doc/internals/en/sending-more-than-16mbyte.html