所有文档

          云数据库 RDS

          lower_case_table_names参数使用详解

          背景

          百度云支持更改数据库表的大小写设置参数,该参数修改后需要重启实例生效:

          • lower_case_table_names=0,表示区分大小写,创建的库表会原样大小写保存在磁盘上
          • lower_case_table_names=1,表示不区分大小写,创建库表时MySQL会将所有的库表名转换成小写字母保存在磁盘上,且SQL语句中涉及到库表无论写成大写或者小写,都会被转化成小写进行查询和写入。

          参数设置请参考:百度云RDS参数设置指南

          问题现象

          某百度云RDS用户在控制台发起删除数据库操作后控制台页面一直处于『删除中』的状态,用户发工单授权给百度云售后工程师协助排查,发现数据库仍然存在。

          原因分析

          • 用户通过RDS控制台向数据库发送drop database操作,查看binlog日志并未看到drop database语句,但存在drop table的语句,如下:
          SET TIMESTAMP=1562737970/*!*/;
          DROP TABLE IF EXISTS `tb_02`,`tb_03`,`tb_01`/*!*/;

          因此判断数据库执行drop database命令失败,转而生成drop table操作,由于表名都是小写字母,导致删除含大写字母的表失败。

          • 查询当前数据库lower_case_table_names取值为1(不区分大小写),但是操作系统中的表文件名是包含大写字母的 因此判断用户曾经建表时设置数据库为区分大小写,并且创建了含大写字母的表。后面用户修改lower_case_table_names参数为不区分大小写,这样导致drop database时mysql找不到对应的表。

          解决方案

          得到用户授权后,由百度云工程师登录用户的数据库服务器,删除残留的含大写字母的表空间文件,并且重新执行drop database命令即可删除数据库。

          案例复现

          1. 当前参数环境lower_case_table_names=0(区分大小写),在baidu_dba库下创建三张表,其中一个表名大写、两个表名是小写,SQL语句如下:
          CREATE TABLE TB_01 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
          
          CREATE TABLE tb_02 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
          
          CREATE TABLE tb_03 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          创建完成后,显示如下: image.png

          1. 在控制台修改lower_case_table_names=1(不区分大小写),然后重启RDS实例
          2. 在控制台删除数据库:baidu_dba,会发现控制台显示处于删除中状态 image.png
          3. 登陆主实例发现baidu_dba库的确存在,但是该库下中只剩下一张TB_01表,另外两个表tb_02\tb_03被删除了 image.png

          结论建议

          • 参数lower_case_table_names,需要在初始化数据库时明确设置是否区分大小写,不建议在线上环境中随意频繁的更改该参数取值
          • 参数lower_case_table_names设置不区分大小写时,无论数据库创建的库表是否有大写字母,都会被转为小写字母存储在操作系统;因此如果此前有区分大小写时创建的大写库、表都会在不区分大小写参数环境下失效,导致表无法识别
          • 数据库迁移时,需要关注参数lower_case_table_names,确保源库与目标库的取值一致
          • 如果想在已有数据库中修改参数lower_case_table_names的取值,合理的操作步骤如下:
          第一种情况:针对仅表名存在大写字母的情况:
          第1步:当lower_case_tables_name=0时,执行rename table语句将含大写字母的表名改成小写
          第2步:设置lower_case_tables_name=1,重启实例生效
          
          第二种情况:针对库名存在大写字母的情况:
          第1步:当lower_case_tables_name=0时,使用mysqldump导出当前数据库的全部数据,然后删除老的数据库
          第2步:设置lower_case_tables_name=1,重启实例生效
          第3步:导入数据至实例,此时包含大写字母的库名已自动转换为小写
          上一篇
          local_infile参数使用详解
          下一篇
          explicit_defaults_for_timestamp参数使用详解