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 參數,也可以參考下本篇文章哦。
以上就是MySQL庫表名大小寫的選擇的詳細內容,更多關於MySQL庫表名大小寫的資料請關註WalkonNet其它相關文章!
推薦閱讀:
- MySQL修改字符集的實戰教程
- 詳解記錄MySQL中lower_case_table_names的坑
- MySQL基礎快速入門知識總結(附思維導圖)
- MySQL基於索引的壓力測試的實現
- MySQL 8.0 之不可見列的基本操作