引  言


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


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


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

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

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

  • 2019年至少25场社区活动


欢迎大家持续关注~


本文以 一个字符集设置引起的故障现象 入手, 介绍 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. 三组设置字符集+校验集的参数:

    1. character_set_connection/collation_connection

    2. character_set_server/collation_server

    3. character_set_database/collation_database,此组参数已被废弃。

  2. 四个只能设置字符集的参数:

    1. character_set_client

    2. character_set_results

    3. character_set_filesystem

    4. character_set_system,此参数值固定为utf8,且不可改变。本文不涉及此项。

  3. MySQL client中, 有一个内存变量`charset_info`,本文中称其为`mysql.client.charset`

  4. 存储层:数据库/数据表/数据列 均由单独的字符集+校验集参数,通过CREATE语句可进行设置。MySQL文档中有详细记述。


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


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


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


(为描述方便,之后将`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`已被废弃,但尚未移除。


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

  1. client连接握手时指定字符集,通过`–default-character-set`参数启动client可设置。

  2. `SET NAMES `语句

  3. `SET CHARSET `语句


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

  1. `SET NAMES` 并不会影响客户端解析SQL文件的字符集`mysql.client.charset`

  2. `SET CHARSET` 会将 `cs_connection` 设置成 `cs_database`的值,而并非设置的字符集。




故障分析


了解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_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


精选系列


| MySQL分布式中间件使用指南

DBLE系列公开课第一课 DBLE概述

DBLE系列公开课第二课 DBLE的配置及使用

DBLE系列公开课第三课 DBLE的管理端口

MyCat的坑如何在分布式中间件DBLE上改善

| DBLE 快速入门

深度分析 | MyCat与DBLE的对比性能调

开源分布式中间件 DBLE 快速入门指南

DBLE 自定义拆分算法
DBLE Server.xml 配置解析

DBLE Schema.xml 配置解析

DBLE rule.xml 配置解析

| MySQL深度分析

MGR相同GTID产生不同transaction故障分析

DBLE和Mycat跨分片查询结果不一致案例分析

基于Xtrabackup及可传输表空间实现多源数据恢复

多从库时半同步复制不工作的BUG分析

[缺陷分析]半同步下多从库复制异常

[缺陷分析] Table cache 导致 MySQL 崩溃


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


开源分布式中间件DBLE

社区官网:https://opensource.actionsky.com/

GitHub主页:https://github.com/actiontech/dble

技术交流群:669663113


开源数据传输中间件DTLE

社区官网:https://opensource.actionsky.com/

GitHub主页:https://github.com/actiontech/dtle

技术交流群:852990221


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


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

喜欢点分享”,不行就看”

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

多喝热水,重启试试