[[403661]]

本文轉(zhuǎn)載自微信公眾號(hào)「MySQL技術(shù)」，作者M(jìn)ySQL技術(shù)。轉(zhuǎn)載本文請(qǐng)聯(lián)系MySQL技術(shù)公眾號(hào)。

前言：

一般在數(shù)據(jù)庫(kù)使用規(guī)范中，我們都會(huì)看到這么一條：庫(kù)名及表名一律使用小寫英文。你有沒有思考過，為什么推薦使用小寫呢?庫(kù)表名是否應(yīng)該區(qū)分大小寫呢?帶著這些疑問，我們一起來(lái)看下本篇文章。

1.決定大小寫是否敏感的參數(shù)

在 MySQL 中，數(shù)據(jù)庫(kù)與 data 目錄中的目錄相對(duì)應(yīng)。數(shù)據(jù)庫(kù)中的每個(gè)表都對(duì)應(yīng)于數(shù)據(jù)庫(kù)目錄中的至少一個(gè)文件(可能是多個(gè)文件，具體取決于存儲(chǔ)引擎)。因此，操作系統(tǒng)的大小寫是否敏感決定了數(shù)據(jù)庫(kù)大小寫是否敏感，而 Windows 系統(tǒng)是對(duì)大小寫不敏感的，Linux 系統(tǒng)對(duì)大小寫敏感。

默認(rèn)情況下，庫(kù)表名在 Windows 系統(tǒng)下是不區(qū)分大小寫的，而在 Linux 系統(tǒng)下是區(qū)分大小寫的。列名，索引名，存儲(chǔ)過程、函數(shù)及事件名稱在任何操作系統(tǒng)下都不區(qū)分大小寫，列別名也不區(qū)分大小寫。

除此之外，MySQL 還提供了 lower_case_table_names 系統(tǒng)變量，該參數(shù)會(huì)影響表和數(shù)據(jù)庫(kù)名稱在磁盤上的存儲(chǔ)方式以及在 MySQL 中的使用方式，在 Linux 系統(tǒng)，該參數(shù)默認(rèn)為 0 ，在 Windows 系統(tǒng)，默認(rèn)值為 1 ，在 macOS 系統(tǒng)，默認(rèn)值為 2 。下面再來(lái)看下各個(gè)值的具體含義：

一般很少將 lower_case_table_names 參數(shù)設(shè)置為 2 ，下面僅討論設(shè)為 0 或 1 的情況。Linux 系統(tǒng)下默認(rèn)為 0 即區(qū)分大小寫，我們來(lái)看下 lower_case_table_names 為 0 時(shí)數(shù)據(jù)庫(kù)的具體表現(xiàn)：

# 查看參數(shù)設(shè)置 
mysql> show variables like 'lower_case_table_names'; 
+------------------------+-------+ 
| Variable_name          | Value | 
+------------------------+-------+ 
| lower_case_table_names | 0     | 
+------------------------+-------+ 
 
# 創(chuàng)建數(shù)據(jù)庫(kù) 
mysql> create database TestDb; 
Query OK, 1 row affected (0.01 sec) 
 
mysql> create database testdb; 
Query OK, 1 row affected (0.02 sec) 
 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| TestDb             | 
| mysql              | 
| performance_schema | 
| sys                | 
| testdb             | 
+--------------------+ 
 
mysql> use testdb; 
Database changed 
mysql> use TestDb; 
Database changed 
mysql> use TESTDB; 
ERROR 1049 (42000): Unknown database 'TESTDB' 
 
# 創(chuàng)建表 
mysql> CREATE TABLE if not exists `test_tb` ( 
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    ->   `stu_id` int(11) NOT NULL COMMENT '學(xué)號(hào)', 
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名', 
    ->   PRIMARY KEY (`increment_id`), 
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE 
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb'; 
Query OK, 0 rows affected (0.06 sec) 
mysql> CREATE TABLE if not exists `Student_Info` ( 
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    ->   `Stu_id` int(11) NOT NULL COMMENT '學(xué)號(hào)', 
    ->   `Stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名', 
    ->   PRIMARY KEY (`increment_id`), 
    ->   UNIQUE KEY `uk_stu_id` (`Stu_id`) USING BTREE 
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Student_Info'; 
Query OK, 0 rows affected (0.06 sec) 
mysql> show tables; 
+------------------+ 
| Tables_in_testdb | 
+------------------+ 
| Student_Info     | 
| test_tb          | 
+------------------+ 
 
# 查詢表 
mysql> select Stu_id,Stu_name from test_tb limit 1; 
+--------+----------+ 
| Stu_id | Stu_name | 
+--------+----------+ 
|   1001 | from1    | 
+--------+----------+ 
1 row in set (0.00 sec) 
mysql> select stu_id,stu_name from test_tb limit 1; 
+--------+----------+ 
| stu_id | stu_name | 
+--------+----------+ 
|   1001 | from1    | 
+--------+----------+ 
 
mysql> select stu_id,stu_name from Test_tb; 
ERROR 1146 (42S02): Table 'testdb.Test_tb' doesn't exist 
mysql> select Stu_id,Stu_name from test_tb as A where A.Stu_id = 1001;  
+--------+----------+ 
| Stu_id | Stu_name | 
+--------+----------+ 
|   1001 | from1    | 
+--------+----------+ 
1 row in set (0.00 sec) 
mysql> select Stu_id,Stu_name from test_tb as A where a.Stu_id = 1001; 
ERROR 1054 (42S22): Unknown column 'a.Stu_id' in 'where clause' 
 
# 查看磁盤上的目錄及文件 
[root@localhost ~]#:/var/lib/mysql# ls -lh 
total 616M 
drwxr-x--- 2 mysql mysql   20 Jun  3 14:25 TestDb 
... 
drwxr-x--- 2 mysql mysql  144 Jun  3 14:40 testdb 
[root@localhost ~]#:/var/lib/mysql# cd testdb/ 
[root@localhost ~]#:/var/lib/mysql/testdb# ls -lh 
total 376K 
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:33 Student_Info.frm 
-rw-r----- 1 mysql mysql 112K Jun  3 14:33 Student_Info.ibd 
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:40 TEST_TB.frm 
-rw-r----- 1 mysql mysql 112K Jun  3 14:40 TEST_TB.ibd 
-rw-r----- 1 mysql mysql   67 Jun  3 14:25 db.opt 
-rw-r----- 1 mysql mysql 8.6K Jun  3 14:30 test_tb.frm 
-rw-r----- 1 mysql mysql 112K Jun  3 14:30 test_tb.ibd 

通過以上實(shí)驗(yàn)我們發(fā)現(xiàn) lower_case_table_names 參數(shù)設(shè)為 0 時(shí)，MySQL 庫(kù)表名是嚴(yán)格區(qū)分大小寫的，而且表別名同樣區(qū)分大小寫但列名不區(qū)分大小寫，查詢時(shí)也需要嚴(yán)格按照大小寫來(lái)書寫。同時(shí)我們注意到，允許創(chuàng)建名稱同樣但大小寫不一樣的庫(kù)表名(比如允許 TestDb 和 testdb 庫(kù)共存)。

你有沒有考慮過 lower_case_table_names 設(shè)為 0 會(huì)出現(xiàn)哪些可能的問題，比如說：一位同事創(chuàng)建了 Test 表，另一位同事在寫程序調(diào)用時(shí)寫成了 test 表，則會(huì)報(bào)錯(cuò)不存在，更甚者可能會(huì)出現(xiàn) TestDb 庫(kù)與 testdb 庫(kù)共存，Test 表與 test 表共存的情況，這樣就更加混亂了。所以為了實(shí)現(xiàn)最大的可移植性和易用性，我們可以采用一致的約定，例如始終使用小寫名稱創(chuàng)建和引用庫(kù)表。也可以將 lower_case_table_names 設(shè)為 1 來(lái)解決此問題，我們來(lái)看下此參數(shù)為 1 時(shí)的情況：

# 將上述測(cè)試庫(kù)刪除 并將 lower_case_table_names 改為 1 然后重啟數(shù)據(jù)庫(kù) 
mysql> show variables like 'lower_case_table_names'; 
+------------------------+-------+ 
| Variable_name          | Value | 
+------------------------+-------+ 
| lower_case_table_names | 1     | 
+------------------------+-------+ 
 
# 創(chuàng)建數(shù)據(jù)庫(kù) 
mysql> create database TestDb; 
Query OK, 1 row affected (0.02 sec) 
 
mysql> create database testdb; 
ERROR 1007 (HY000): Can't create database 'testdb'; database exists 
mysql> show databases; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| sys                | 
| testdb             | 
+--------------------+ 
7 rows in set (0.00 sec) 
 
mysql> use testdb; 
Database changed 
mysql> use TESTDB; 
Database changed 
 
# 創(chuàng)建表 
mysql> CREATE TABLE if not exists `test_tb` ( 
    ->   `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    ->   `stu_id` int(11) NOT NULL COMMENT '學(xué)號(hào)', 
    ->   `stu_name` varchar(20) DEFAULT NULL COMMENT '學(xué)生姓名', 
    ->   PRIMARY KEY (`increment_id`), 
    ->   UNIQUE KEY `uk_stu_id` (`stu_id`) USING BTREE 
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='test_tb'; 
Query OK, 0 rows affected (0.05 sec) 
mysql> create table TEST_TB (id int); 
ERROR 1050 (42S01): Table 'test_tb' already exists 
 
mysql> show tables; 
+------------------+ 
| Tables_in_testdb | 
+------------------+ 
| test_tb          | 
+------------------+ 
 
# 查詢表 
mysql> select stu_id,stu_name from test_tb limit 1; 
+--------+----------+ 
| stu_id | stu_name | 
+--------+----------+ 
|   1001 | from1    | 
+--------+----------+ 
1 row in set (0.00 sec) 
 
mysql> select stu_id,stu_name from Test_Tb limit 1;        
+--------+----------+ 
| stu_id | stu_name | 
+--------+----------+ 
|   1001 | from1    | 
+--------+----------+ 
1 row in set (0.00 sec) 
 
mysql> select stu_id,stu_name from test_tb as A where a.stu_id = 1002; 
+--------+----------+ 
| stu_id | stu_name | 
+--------+----------+ 
|   1002 | dfsfd    | 
+--------+----------+ 
1 row in set (0.00 sec) 

當(dāng) lower_case_table_names 參數(shù)設(shè)為 1 時(shí)，可以看出庫(kù)表名統(tǒng)一用小寫存儲(chǔ)，查詢時(shí)不區(qū)分大小寫且用大小寫字母都可以查到。這樣會(huì)更易用些，程序里無(wú)論使用大寫表名還是小寫表名都可以查到這張表，而且不同系統(tǒng)間數(shù)據(jù)庫(kù)遷移也更方便，這也是建議將 lower_case_table_names 參數(shù)設(shè)為 1 的原因。

2.參數(shù)變更注意事項(xiàng)

lower_case_table_names 參數(shù)是全局系統(tǒng)變量，不可以動(dòng)態(tài)修改，想要變動(dòng)時(shí)，必須寫入配置文件然后重啟數(shù)據(jù)庫(kù)生效。如果你的數(shù)據(jù)庫(kù)該參數(shù)一開始為 0 ，現(xiàn)在想要改為 1 ，這種情況要格外注意，因?yàn)槿粼瓕?shí)例中存在大寫的庫(kù)表，則改為 1 重啟后，這些庫(kù)表將會(huì)不能訪問。如果需要將 lower_case_table_names 參數(shù)從 0 改成 1 ，可以按照下面步驟修改：

首先核實(shí)下實(shí)例中是否存在大寫的庫(kù)及表，若不存在大寫的庫(kù)表，則可以直接修改配置文件然后重啟。若存在大寫的庫(kù)表，則需要先將大寫的庫(kù)表轉(zhuǎn)化為小寫，然后才可以修改配置文件重啟。

當(dāng)實(shí)例中存在大寫庫(kù)表時(shí)，可以采用下面兩種方法將其改為小寫：

1、通過 mysqldump 備份相關(guān)庫(kù)，備份完成后刪除對(duì)應(yīng)庫(kù)，之后修改配置文件重啟，最后將備份文件重新導(dǎo)入。此方法用時(shí)較長(zhǎng)，一般很少用到。

2、通過 rename 語(yǔ)句修改，具體可以參考下面 SQL：

# 將大寫表重命名為小寫表  
rename table TEST to test; 
 
# 若存在大寫庫(kù) 則需要先創(chuàng)建小寫庫(kù) 然后將大寫庫(kù)里面的表轉(zhuǎn)移到小寫庫(kù) 
rename table TESTDB.test_tb to testdb.test_tb; 
 
# 分享兩條可能用到的SQL 
# 查詢實(shí)例中有大寫字母的表 
SELECT 
 TABLE_SCHEMA, 
 TABLE_NAME 
FROM 
 information_schema.`TABLES`  
WHERE 
 TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )  
 AND table_type = 'BASE TABLE'  
 AND TABLE_NAME REGEXP BINARY '[A-Z]' 
   
  
# 拼接SQL 將大寫庫(kù)中的表轉(zhuǎn)移到小寫庫(kù) 
SELECT 
 CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' )  
FROM 
 information_schema.TABLES  
WHERE 
 TABLE_SCHEMA = 'TESTDB'; 

總結(jié)：

本篇文章主要介紹了 MySQL 庫(kù)表大小寫問題，相信你看了這篇文章后，應(yīng)該明白為什么庫(kù)表名建議使用小寫英文了。如果你想變更 lower_case_table_names 參數(shù)，也可以參考下本篇文章哦。