MySQL标识符大小写敏感问题

发表于 分类于 阅读次数： 本文字数： 1.5k 阅读时长 ≈ 5 分钟

标识符大小写敏感的理解

很多时候我们讨论大小写敏感这个概念时，其实并没有确认讨论的维度。错误的讨论维度可能使得在处理和排查一些问题时处处碰壁。在讨论大小写敏感问题时，我们存在两个讨论维度：

  • 使用维度：
    • 大小写敏感：大写字母和小写字母在使用时认为是不同的标识符
    • 大小写不敏感：大写字母和小写字母在使用时认为是相同的
  • 存储层面：
    • 大小写敏感：具体存储文本时，是区分大小写的，认为是不同的标识符
    • 大小写不敏感：具体存储文本时，不区分大小写，比如全部统一按照小写存储

TIPS: 为什么要区分存储和使用维度？
存储上可以是大小写敏感的，但是使用可以大小写不敏感，比如读取存储的内容后统一转换成小写，就可以是大小写不敏感了。参考下面MySQL中的案例

MySQL标识符大小写敏感总结

这部分总结参考mysql官方文档Identifier Case Sensitiveity

标识符类型 使用维度是否大小写敏感 存储维度是否大小写敏感 备注信息
库名/表名 由lower_case_table_names确定 由lower_case_table_names确定 lower_case_table_names的值本质是按照文件系统是否大小写敏感来确定的。因为mysql的库和表在文件系统上都有对应的目录和文件。例如lower_case_table_names=1，则库表在meta history中都是按照小写存储的
列名 否 是 在information_schema的元信息存储中，列名都是大小写敏感的；但是实际在使用中，mysql并不区分列名大小写
日志文件的名称 是 取决于文件系统的大小写敏感

binlog中的标识符大小写

这个其实官方文档没有明确说明，只能自己测试。

测试环境

  • 阿里云RDS MySQL 8.0，lower_case_table_names=1
  • Linux MySQL 8.0, lower_case_table_names=0
  • MAC MySQL 8.0

测试表结构和语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#### 阿里云RDS案例/ MAC mysql8 案例
CREATE TABLE `tab_sxdEf`
(
    `id`    bigint(20) NOT NULL AUTO_INCREMENT,
    `name1` varchar(50) default null,
    `name2` varchar(50) default null,
    PRIMARY KEY (`id`)
);

INSERT INTO tab_sxdEf(name1,name2) values('1','2');
alter table `tab_sxdEf` add column `name3` varchar(50) default null;
#### Linux MySQL案例
CREATE TABLE `WS_db`.`ws_TAB`
(
    `id`    bigint(20) NOT NULL AUTO_INCREMENT,
    `NAME` varchar(50) default null,
    PRIMARY KEY (`id`)
);
insert into `WS_db`.`ws_TAB`(id, NAME) values (1,'1');
alter table `WS_db`.`ws_TAB` add column  `naME2` varchar(50) default null;

阿里云 RDS MySQL

event类型 存储是否大小写敏感 参考Binlog示例 说明
query event (DDL) 是 /* Query from DMS-WEBSQL-0-Qid_1655176937657 by user 214308381499714865 */ alter table tab_sxdEf add column name3 varchar(50) default null
/!/; 以原始SQL中的大小写为准，是区分大小写、大小写敏感的，和数据库lower_case_table_names的设置无关
TABLE_MAP_EVENT 由lower_case_table_names确定
不同lower_case_table_names导致event中table meta信息的大小写不同，例如大小写不敏感的话，均会处理成小写
WRITE_ROWS_EVENT/UPDATE_ROWS_EVENT/DELETE_ROWS_EVENT 由lower_case_table_names确定
不同lower_case_table_names导致binlog中存储的库表名标识符大小写不同，例如大小写不敏感的话，均会处理成小写

tips1: MAC上lower_case_table_names=2和lower_case_table_names=1的效果是一样的，meta信息和DML中都是小写存储，DDL则固定以创建时SQL中的表示为准。

tips2: lower_case_table_names为1和2时，show create table中的表标识符大小只与你命令中使用的大小写相关。lower_case_table_names=0时，show create table则需要严格匹配实际创建时表的大小写

lower_case_table_names

上文提到库表名的标识符在存储以及使用时是否大小写敏感是由lower_case_table_names参数决定。通过以下命令可以查看当前mysql的小写表名配置

1
show VARIABLES  like 'lower_case_table_names';

其取值有如下情况

值 含义 作为默认值的操作系统
0 存储和使用时都大小写敏感 windows
1 存储(全部小写存储)和使用都大小写不敏感 linux
2 存储大小写敏感，使用时大小写不敏感（全部转小写） mac

应用案例——数据同步中的DDL实时同步

以上知识的理解对于更好实现MySQL的DDL实时同步非常有帮助。如果没有正确处理好这些大小写情况，那么可能导致DDL的实时同步受到影响。

MySQL的基本DDL的实时同步实现方式

  1. 从binlog中解析出需要同步的DDL原始SQL语句
  2. 将DDL语句中的库名、表名和用户的订阅配置进行比较，如果不在订阅列表中则过滤
  3. 如果订阅了该库表的DDL变更，则进行SQL改写后写入对端数据库

这里可以看到，第二步是及其容易由于大小写误判导致不符合预期的过滤的。要确保这个判断正确，需要保证：

  • binlog DDL语句中的标识符大小写与订阅配置相同

CloudCanal中的DDL同步问题案例

CloudCanal是本人参与开发的一款数据迁移同步产品，这里有个case可以和大家分享。

问题现象

包含大小写混合的表名上的DDL没有同步

问题机制

对于MySQL来说，其中用户订阅信息中的库表标识符信息主要来源于information_schema库。我们遇到的一个实际案例就是数据库lower_case_table_names=1，因此标识符存储和使用都是大小写不敏感的，实际存储的都是小写的标识符。因此订阅配置中的标识符都是小写的，而实际接收到binlog中的DDL是区分大小写的，因此在过滤的时候没有正确过滤导致DDL没有正确同步。

问题解法

了解了问题的本质原因，解法自然也就有了。启动同步任务时探测下源端数据库的lower_case_table_names配置，如果是大小写不敏感的，则全部转成小写后来处理。