MySQL标识符大小写敏感问题

标识符大小写敏感的理解

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

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

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 配置,如果是大小写不敏感的,则全部转成小写后来处理。