MySQL中Identifier Case Sensitivity

在MySQL当中，有可能遇到表名大小写敏感的问题。其实这个跟平台（操作系统）有关，也跟系统变量lower_case_table_names有关系。下面总结一下，有兴趣可以查看官方文档“Identifier Case Sensitivity”

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.8.1, “MySQL Extensions to Standard SQL”. Thelower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

在 MySQL 中, 数据库对应于数据目录中的目录。数据库中的每个表对应于数据库目录中至少一个文件 (可能更多, 具体取决于存储引擎)。触发器也对应于文件。因此, 底层操作系统的区分大小写在数据库、表和触发器名称的大小写敏感度方面起着重要作用。这意味着这些名称在 Windows 中不区分大小写, 但在大多数类型的 Unix 中都是区分大小写的。一个显著的例外是 macOS, 它是基于 Unix 的, 但使用的是不区分大小写的默认文件系统类型 (HFS+)。但是, macOS 还支持 UFS 卷, 它们与任何 Unix 一样都是区分大小写的。参见1.8.1 节, “MySQL Extensions to Standard SQL”。lower_case_table_names 系统变量还影响服务器处理标识符大小写灵敏度的方式, 如本节后面所述。

Linux系统：

• 数据库名与表名是严格区分大小写的；

• 表的别名是严格区分大小写的；

• 列名与列的别名在所有的情况下均是忽略大小写的；

• 变量名也是严格区分大小写的；

Windows系统：

• 都不区分大小写

• Mac OS下（非UFS卷）：

• 都不区分大小写

注意事项：列名、索引、存储过程、事件名称在任何平台上都不区分大小写，列别名也不区分大小写。

Notice：Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.

下面在测试环境为Red Hat Enterprise Linux Server release 5.7， MySQL 5.6.20：

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
 
mysql> 
 
mysql> use mydb;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.07 sec)
 
mysql> create table TEST(id int);
Query OK, 0 rows affected (0.09 sec)
 
mysql> insert into test values(1);
Query OK, 1 row affected (0.03 sec)
 
mysql> insert into TEST value(2);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
mysql> select * from TEST;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
 
mysql> 

在配置文件my.cnf中设置lower_case_table_names=1后（1表示不区分大小写，0表示区分大小写），重启MySQL服务后，进行如下测试：

mysql> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select * from test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
mysql> select * from TEST;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
mysql> 

可以看到此时不管是test、TEST抑或Test，都是访问的test，此时不能访问”TEST”表了,系统变量lower_case_table_names是只读变量，也无法在当前会话修改，这种设置下，如果存在相同的表名的话，使用mysqldump备份数据库时会遇到下面错误:

mysqldump: Got error: 1066: Not unique table/alias: ‘test’ when using LOCK TABLES

遇到这种情况就比较麻烦了，必须在配置文件my.cnf中设置变量lower_case_table_names=0，重启MySQL服务，所以提前规划，使用统一的命名规则就非常重要，可以避免这样的问题出现。另外系统变量lower_case_table_names有三个值：分别是0、1、2.

1. 设置成0：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时大小写敏感。

2. 设置成1：表名转小写后存储到硬盘，比较时大小写不敏感。

3. 设置成2：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时统一转小写比较。

关于数据库名大小写敏感，可能会遇到下面问题：

1：ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./xxxx’, errno: 39)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyDB               |
| mydb               |
| mysql              |
| performance_schema |
| tmonitor           |
| xiangrun           |
+--------------------+
7 rows in set (0.01 sec)
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)
 
mysql> drop database mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)
mysql> 

解决方法：在配置文件my.cnf中设置变量lower_case_table_names=0，重启MySQL服务，然后就可以drop 掉数据库了。

2：ERROR 1049 (42000): Unknown database ‘xxx’

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.01 sec)
 
mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyDB               |
| mysql              |
| performance_schema |
| tmonitor           |
| xiangrun           |
+--------------------+
6 rows in set (0.01 sec)
 
mysql> use MyDB;
ERROR 1049 (42000): Unknown database 'mydb'
mysql> 

上面内容在MySQL 8之前都OK，但是MySQL 8.*下，如果你在my.cnf下添加了lower_case_table_names=1，那么有可能MySQL重启会失败。如下所示：

2020-09-15T01:04:48.075545Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
2020-09-15T01:04:48.419856Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 27060
2020-09-15T01:04:49.558739Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2020-09-15T01:04:49.559138Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-09-15T01:04:49.559417Z 0 [ERROR] [MY-010119] [Server] Aborting

lower_case_table_names=2,重启也会失败，如下所示：

[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 2087
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting

为什么有这个问题呢？这个要怎么处理呢？在MySQL 5.7之前则允许数据库初始化和启动的参数值不一致，而且以启动值为准。在MySQL 官方提供的RPM包中默认是使用lower-case-table-names=0，但是从MySQL8.0开始，新增了data dictionary的概念，数据初始化的时候在Linux下默认使用lower_case_table_names=0的参数，数据库启动的时候读取的my.cnf文件中的值。若二者值不一致则在MySQL的错误日志中记录报错信息。另外，这个参数也不允许修改，只能在初始化化时指定。官方文档介绍如下：

lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

解决方案：

重新初始化时，配置好my.cnf， 例如my.cnf中已有datadir等参数，则不需要指定。在初始化之前，必须做下面一些操作

1：关闭MySQL实例

systemctl stop mysqld
systemctl status mysqld

2：备份数据库，备份原数据目录，删除原数据目录。

备份数据目录后删除原数据库目录，如果你将数据目录指向其它位置则不需要删除原数据目录文件。另外，对于已经运行了一段时间的数据库实例，相对来说要麻烦一些，例如需要备份用户数据库，然后删除原数据目录。

3：重新初始化

mysqld --defaults-file=/etc/my.cnf --initialize --lower_case_table_names=1 
或
mysqld --defaults-file=/etc/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

在执行上面命令前（参数根据实际情况定制），刷新输出错误日志信息，观察初始化是否成功或失败。

# tail -60f /var/log/mysqld.log 

例如，有一次忘记了删除数据目录，日志中提示初始化时，由于数据目录已经存在，导致初始化中断。

[System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.18) initializing of server in progress as process 2534
[ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
[ERROR] [MY-013236] [Server] The designated data directory /home/mysql/mysql_data/mysql/ is unusable. You can remove all files that the server added to it.

4：重置密码

首先找到初始化密码，登陆数据库后，重置root密码

#grep 'temporary password' /var/log/mysql/error.log