深入浅析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)

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>

参考资料：

https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html

总结