關于MySQL庫表名大小寫問題
本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。
前言：
一般在數據庫使用規范中，我們都會看到這么一條：庫名及表名一律使用小寫英文。你有沒有思考過，為什么推薦使用小寫呢?庫表名是否應該區分大小寫呢?帶著這些疑問，我們一起來看下本篇文章。
1.決定大小寫是否敏感的參數
在 MySQL 中，數據庫與 data 目錄中的目錄相對應。數據庫中的每個表都對應于數據庫目錄中的至少一個文件(可能是多個文件，具體取決于存儲引擎)。因此，操作系統的大小寫是否敏感決定了數據庫大小寫是否敏感，而 Windows 系統是對大小寫不敏感的，Linux 系統對大小寫敏感。
默認情況下，庫表名在 Windows 系統下是不區分大小寫的，而在 Linux 系統下是區分大小寫的。列名，索引名，存儲過程、函數及事件名稱在任何操作系統下都不區分大小寫，列別名也不區分大小寫。
除此之外，MySQL 還提供了 lower_case_table_names 系統變量，該參數會影響表和數據庫名稱在磁盤上的存儲方式以及在 MySQL 中的使用方式，在 Linux 系統，該參數默認為 0 ，在 Windows 系統，默認值為 1 ，在 macOS 系統，默認值為 2 。下面再來看下各個值的具體含義：
|
Value |
Meaning |
|
0 |
庫表名以創建語句中指定的字母大小寫存儲在磁盤上，名稱比較區分大小寫。 |
|
1 |
庫表名以小寫形式存儲在磁盤上，名稱比較不區分大小寫。MySQL 在存儲和查找時將所有表名轉換為小寫。此行為也適用于數據庫名稱和表別名。 |
|
2 |
庫表名以創建語句中指定的字母大小寫存儲在磁盤上，但是 MySQL 在查找時將它們轉換為小寫。名稱比較不區分大小寫。 |
一般很少將 lower_case_table_names 參數設置為 2 ，下面僅討論設為 0 或 1 的情況。Linux 系統下默認為 0 即區分大小寫，我們來看下 lower_case_table_names 為 0 時數據庫的具體表現：
- # 查看參數設置
- mysql> show variables like 'lower_case_table_names';
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | lower_case_table_names | 0 |
- +------------------------+-------+
- # 創建數據庫
- 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'
- # 創建表
- mysql> CREATE TABLE if not exists `test_tb` (
- -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
- -> `stu_id` int(11) NOT NULL COMMENT '學號',
- -> `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
- -> 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 '學號',
- -> `Stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
- -> 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
通過以上實驗我們發現 lower_case_table_names 參數設為 0 時，MySQL 庫表名是嚴格區分大小寫的，而且表別名同樣區分大小寫但列名不區分大小寫，查詢時也需要嚴格按照大小寫來書寫。同時我們注意到，允許創建名稱同樣但大小寫不一樣的庫表名(比如允許 TestDb 和 testdb 庫共存)。
你有沒有考慮過 lower_case_table_names 設為 0 會出現哪些可能的問題，比如說：一位同事創建了 Test 表，另一位同事在寫程序調用時寫成了 test 表，則會報錯不存在，更甚者可能會出現 TestDb 庫與 testdb 庫共存，Test 表與 test 表共存的情況，這樣就更加混亂了。所以為了實現最大的可移植性和易用性，我們可以采用一致的約定，例如始終使用小寫名稱創建和引用庫表。也可以將 lower_case_table_names 設為 1 來解決此問題，我們來看下此參數為 1 時的情況：
- # 將上述測試庫刪除 并將 lower_case_table_names 改為 1 然后重啟數據庫
- mysql> show variables like 'lower_case_table_names';
- +------------------------+-------+
- | Variable_name | Value |
- +------------------------+-------+
- | lower_case_table_names | 1 |
- +------------------------+-------+
- # 創建數據庫
- 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
- # 創建表
- mysql> CREATE TABLE if not exists `test_tb` (
- -> `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵',
- -> `stu_id` int(11) NOT NULL COMMENT '學號',
- -> `stu_name` varchar(20) DEFAULT NULL COMMENT '學生姓名',
- -> 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)
當 lower_case_table_names 參數設為 1 時，可以看出庫表名統一用小寫存儲，查詢時不區分大小寫且用大小寫字母都可以查到。這樣會更易用些，程序里無論使用大寫表名還是小寫表名都可以查到這張表，而且不同系統間數據庫遷移也更方便，這也是建議將 lower_case_table_names 參數設為 1 的原因。
2.參數變更注意事項
lower_case_table_names 參數是全局系統變量，不可以動態修改，想要變動時，必須寫入配置文件然后重啟數據庫生效。如果你的數據庫該參數一開始為 0 ，現在想要改為 1 ，這種情況要格外注意，因為若原實例中存在大寫的庫表，則改為 1 重啟后，這些庫表將會不能訪問。如果需要將 lower_case_table_names 參數從 0 改成 1 ，可以按照下面步驟修改：
首先核實下實例中是否存在大寫的庫及表，若不存在大寫的庫表，則可以直接修改配置文件然后重啟。若存在大寫的庫表，則需要先將大寫的庫表轉化為小寫，然后才可以修改配置文件重啟。
當實例中存在大寫庫表時，可以采用下面兩種方法將其改為小寫：
1、通過 mysqldump 備份相關庫，備份完成后刪除對應庫，之后修改配置文件重啟，最后將備份文件重新導入。此方法用時較長，一般很少用到。
2、通過 rename 語句修改，具體可以參考下面 SQL：
- # 將大寫表重命名為小寫表
- rename table TEST to test;
- # 若存在大寫庫 則需要先創建小寫庫 然后將大寫庫里面的表轉移到小寫庫
- rename table TESTDB.test_tb to testdb.test_tb;
- # 分享兩條可能用到的SQL
- # 查詢實例中有大寫字母的表
- 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 將大寫庫中的表轉移到小寫庫
- SELECT
- CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' )
- FROM
- information_schema.TABLES
- WHERE
- TABLE_SCHEMA = 'TESTDB';
總結：
本篇文章主要介紹了 MySQL 庫表大小寫問題，相信你看了這篇文章后，應該明白為什么庫表名建議使用小寫英文了。如果你想變更 lower_case_table_names 參數，也可以參考下本篇文章哦。
