|
LES
遇到这种情况就比较麻烦了，必须在配置文件my.cnf中设置变量lower_case_table_names=0，重启MySQL服务，所以提前规划，使用统一的命名规则就非常重要，可以避免这样的问题出现。另外系统变量lower_case_table_names有三个值：分别是0、1、2.
1. 设置成0：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时大小写敏感。
2. 设置成1：表名转小写后存储到硬盘，比较时大小写不敏感。
3. 设置成2：表名按你写的SQL大小写存储，大写就大写小写就小写，比较时统一转小写比较。
| Value |
Meaning |
| 0 |
Table and database names are stored on disk using the lettercase specified in theCREATE TABLE or CREATE DATABASEstatement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS). If you force this variable to 0 with–lower-case-table-names=0 on a case-insensitive file system and access MyISAMtablenames using different lettercases, index corruption may result. |
| 1 |
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. |
| 2 |
Table and database names are stored on disk using the lettercase specified in theCREATE TABLE or CREATE DATABASEstatement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as forlower_case_table_names=1. |
关于数据库名大小写敏感，会遇到下面问题：
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
|