在MySQL當(dāng)中，有可能遇到表名大小寫(xiě)敏感的問(wèn)題。其實(shí)這個(gè)跟平臺(tái)（操作系統(tǒng)）有關(guān)，也跟系統(tǒng)變量lower_case_table_names有關(guān)系。下面總結(jié)一下，有興趣可以查看官方文檔“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 中, 數(shù)據(jù)庫(kù)對(duì)應(yīng)于數(shù)據(jù)目錄中的目錄。數(shù)據(jù)庫(kù)中的每個(gè)表對(duì)應(yīng)于數(shù)據(jù)庫(kù)目錄中至少一個(gè)文件 (可能更多, 具體取決于存儲(chǔ)引擎)。觸發(fā)器也對(duì)應(yīng)于文件。因此, 底層操作系統(tǒng)的區(qū)分大小寫(xiě)在數(shù)據(jù)庫(kù)、表和觸發(fā)器名稱(chēng)的大小寫(xiě)敏感度方面起著重要作用。這意味著這些名稱(chēng)在 Windows 中不區(qū)分大小寫(xiě), 但在大多數(shù)類(lèi)型的 Unix 中都是區(qū)分大小寫(xiě)的。一個(gè)顯著的例外是 macOS, 它是基于 Unix 的, 但使用的是不區(qū)分大小寫(xiě)的默認(rèn)文件系統(tǒng)類(lèi)型 (HFS+)。但是, macOS 還支持 UFS 卷, 它們與任何 Unix 一樣都是區(qū)分大小寫(xiě)的。參見(jiàn)1.8.1 節(jié), “MySQL Extensions to Standard SQL“。lower_case_table_names 系統(tǒng)變量還影響服務(wù)器處理標(biāo)識(shí)符大小寫(xiě)靈敏度的方式, 如本節(jié)后面所述。

 Linux系統(tǒng)：

數(shù)據(jù)庫(kù)名與表名是嚴(yán)格區(qū)分大小寫(xiě)的；
表的別名是嚴(yán)格區(qū)分大小寫(xiě)的；
列名與列的別名在所有的情況下均是忽略大小寫(xiě)的；
變量名也是嚴(yán)格區(qū)分大小寫(xiě)的；

Windows系統(tǒng)：

都不區(qū)分大小寫(xiě)
Mac OS下（非UFS卷）：
都不區(qū)分大小寫(xiě)

注意事項(xiàng)：列名、索引、存儲(chǔ)過(guò)程、事件名稱(chēng)在任何平臺(tái)上都不區(qū)分大小寫(xiě)，列別名也不區(qū)分大小寫(xiě)。

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

下面在測(cè)試環(huán)境為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中設(shè)置lower_case_table_names=1后（1表示不區(qū)分大小寫(xiě)，0表示區(qū)分大小寫(xiě)），重啟MySQL服務(wù)后，進(jìn)行如下測(cè)試：

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>

可以看到此時(shí)不管是test、TEST抑或Test，都是訪問(wèn)的test，此時(shí)不能訪問(wèn)”TEST”表了,系統(tǒng)變量lower_case_table_names是只讀變量，也無(wú)法在當(dāng)前會(huì)話修改，這種設(shè)置下，如果存在相同的表名的話，使用mysqldump備份數(shù)據(jù)庫(kù)時(shí)會(huì)遇到下面錯(cuò)誤:

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

遇到這種情況就比較麻煩了，必須在配置文件my.cnf中設(shè)置變量lower_case_table_names=0，重啟MySQL服務(wù)，所以提前規(guī)劃，使用統(tǒng)一的命名規(guī)則就非常重要，可以避免這樣的問(wèn)題出現(xiàn)。另外系統(tǒng)變量lower_case_table_names有三個(gè)值：分別是0、1、2.

1. 設(shè)置成0：表名按你寫(xiě)的SQL大小寫(xiě)存儲(chǔ)，大寫(xiě)就大寫(xiě)小寫(xiě)就小寫(xiě)，比較時(shí)大小寫(xiě)敏感。

2. 設(shè)置成1：表名轉(zhuǎn)小寫(xiě)后存儲(chǔ)到硬盤(pán)，比較時(shí)大小寫(xiě)不敏感。 

3. 設(shè)置成2：表名按你寫(xiě)的SQL大小寫(xiě)存儲(chǔ)，大寫(xiě)就大寫(xiě)小寫(xiě)就小寫(xiě)，比較時(shí)統(tǒng)一轉(zhuǎn)小寫(xiě)比較。

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中設(shè)置變量lower_case_table_names=0，重啟MySQL服務(wù)，然后就可以drop 掉數(shù)據(jù)庫(kù)了。

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

總結(jié)

以上所述是小編給大家介紹的MySQL 中 Identifier Case Sensitivity問(wèn)題，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

最新評(píng)論