修改lower_case_table_names导致库表无法访问、大版本升级失败

取值说明：

• 0：库名和表名严格区分大小写存储，且访问时保留原大小写形式（区分大小写匹配）。

• 1：库名和表名强制转为小写存储，访问时需使用小写名称（不区分大小写匹配）。

• 2：库名和表名保留大小写存储，但访问时会自动转为小写（仅适用于某些文件系统，RDS MySQL不支持此取值）。

RDS MySQL实例限制：

• 仅支持取值0或1，不支持取值2。

• 版本差异：

  • MySQL 8.0以下版本：该参数为只读，可通过修改cnf文件并重启实例生效。

  • MySQL 8.0版本：参数值在实例初始化时固定，初始化完成后无法修改。

参数值从0改为1后库表不可访问

1. 参数初始值为0（区分大小写存储）时，创建了包含大写字母的库名和表名：

   mysql> SHOW GLOBAL VARIABLES LIKE "lower_case_table_names";
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 0     |
   +------------------------+-------+
   1 row in set (0.01 sec)
   
   mysql> CREATE DATABASE db_1;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> USE db_1;
   Database changed
   
   # 创建包含大写字母的表名
   mysql> CREATE TABLE T1(a int);
   Query OK, 0 rows affected (0.00 sec)
   
   # 创建包含大写字母的库名
   mysql> CREATE DATABASE DB_2;
   Query OK, 1 row affected (0.00 sec)

2. 修改参数为1（强制小写存储）后，系统无法正确识别原大小写混合的库表名，可能出现库表访问失败的情况：

   mysql> SHOW GLOBAL VARIABLES LIKE "lower_case_table_names";
   +------------------------+-------+
   | Variable_name          | Value |
   +------------------------+-------+
   | lower_case_table_names | 1     |
   +------------------------+-------+
   1 row in set (0.00 sec)
   
   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | DB_2               |
   | db_1               |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+
   6 rows in set (0.00 sec)
   
   # 库名访问失败：DB_2 无法访问，切换、修改、删除 DB_2 失败
   mysql> USE DB_2;
   ERROR 1049 (42000): Unknown database 'db_2'
   mysql> USE db_2;
   ERROR 1049 (42000): Unknown database 'db_2'
   mysql> ALTER DATABASE DB_2 CHARACTER SET utf8mb3;
   ERROR 1 (HY000): Can't create/write to file './db_2/db.opt' (Errcode: 2 - No such file or directory)
   mysql> ALTER DATABASE db_2 CHARACTER SET utf8mb3;
   ERROR 1 (HY000): Can't create/write to file './db_2/db.opt' (Errcode: 2 - No such file or directory)
   mysql> DROP DATABASE DB_2;
   ERROR 1008 (HY000): Can't drop database 'db_2'; database doesn't exist
   mysql> DROP DATABASE db_2;
   ERROR 1008 (HY000): Can't drop database 'db_2'; database doesn't exist
     
   mysql> USE db_1;
   Database changed
   
   mysql> SHOW TABLES;
   +----------------+
   | Tables_in_db_1 |
   +----------------+
   | T1             |
   +----------------+
   1 rows in set (0.00 sec)
   
   # 表名访问失败：T1 无法访问，查询、修改、删除 T1 失败
   mysql> SELECT * FROM T1;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> SELECT * FROM t1;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> ALTER TABLE T1 ENGINE=INNODB;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> ALTER TABLE t1 ENGINE = INNODB;
   ERROR 1146 (42S02): Table 'db_1.t1' doesn't exist
   mysql> DROP TABLE T1;
   ERROR 1051 (42S02): Unknown table 'db_1.t1'
   mysql> DROP TABLE t1;
   ERROR 1051 (42S02): Unknown table 'db_1.t1'

大版本升级到MySQL 8.0失败

MySQL 5.7实例中存在大写库表名，且lower_case_table_names = 1，升级到MySQL 8.0时失败，错误日志示例：

[ERROR] [MY-013520] [Server] Schema name 'DB_2' containing upper case characters is not allowed with lower_case_table_names = 1.
[ERROR] [MY-013521] [Server] Table name 'db_1.T1' containing upper case characters is not allowed with lower_case_table_names = 1.
[ERROR] [MY-010022] [Server] Failed to Populate DD tables.
[ERROR] [MY-010119] [Server] Aborting