Mysql 大小寫參數lower_case_table_names 學習
?
?在MySQL中,操作系統對大小寫的敏感性決定了數據庫和表的大小寫敏感。所以,默認情況下,MySQL在Windows下是不區分大小寫的,而在Linux環境下數據庫名與表名是嚴格區分大小寫的。但是,Mysql中存在參數lower_case_table_names,可以控制數據庫名、表名的大小寫是否敏感。
需要注意的是,系統庫information_schema及其之下的表名是不區分大小寫的。
?
參數說明:
- 當lower_case_table_names為0時表示區分大小寫,為1時表示不區分大小寫。
- 在unix,linux下lower_case_table_names默認值為0,Windows下默認值是1。Mac OS 下默認值是2。
lower_case_table_names=0 -- 表名存儲為給定的大小和比較是區分大小寫的
lower_case_table_names=1 -- 表名存儲在磁盤是小寫的,但是比較的時候是不區分大小寫
lower_case_table_names=2 -- 表名存儲為給定的大小寫但是比較的時候是小寫的
1. 創建測試表
mysql> create database sxcdb;
Query OK, 1 row affected (0.00 sec)
mysql> use sxcdb;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table T1 (id int,name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> create table T3 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into T1 values (1,'T1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into T3 values (3);
Query OK, 1 row affected (0.00 sec)
我們新建t1、t2、T1、T3 四個測試表,表名既包含小寫,也包含大寫,并且 t1、T1 是兩張不一樣的表。
2. 5.7 默認情況下查詢
2.1 information_schema系統庫
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> use INformation_schema;
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 count(*) from CHARACTER_SETS;
+----------+
| count(*) |
+----------+
| 41 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from character_sets;
+----------+
| count(*) |
+----------+
| 41 |
+----------+
1 row in set (0.00 sec)
?默認情況下,也就是區分大小寫的情況下,系統庫information_schema及其之下的表名也是不區分大小寫的。但是 performance_schema 和?mysql 系統庫還是區分大小寫的。?
mysql> use Performance_schema;
ERROR 1049 (42000): Unknown database 'Performance_schema'
mysql> use Mysql
ERROR 1049 (42000): Unknown database 'Mysql'
2.2 非 information_schema系統庫
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> use sxcdb;
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 t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> select * from T1;
+------+------+
| id | name |
+------+------+
| 1 | T1 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from T3;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from T2;
ERROR 1146 (42S02): Table 'sxcdb.T2' doesn't exist
mysql> select * from t3;
ERROR 1146 (42S02): Table 'sxcdb.t3' doesn't exist
默認情況下,Mysql區分大小寫,t1、t2、T1、T3 都可以訪問,T2、t3 表不存在,很明顯是表名區分大小寫的。
3. 設置大小寫區分情況下查詢
3.1 修改參數
-- 不支持在線修改
mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
-- 在my.cnf文件中添加參數
[root@mysql57 ~]# cat /etc/my.cnf|grep lower_case_table_names
lower_case_table_names=1
--重啟mysql服務生效
[root@mysql57 ~]# systemctl restart mysqld
[root@mysql57 ~]#
[root@mysql57 ~]# mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.00 sec)
3.2 再次驗證查詢
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from T1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> select * from T2;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> select * from T3;
ERROR 1146 (42S02): Table 'sxcdb.t3' doesn't exist
mysql> select * from t3;
ERROR 1146 (42S02): Table 'sxcdb.t3' doesn't exist
mysql> show tables;
+-----------------+
| Tables_in_sxcdb |
+-----------------+
| T1 |
| T3 |
| t1 |
| t2 |
+-----------------+
4 rows in set (0.00 sec)
結論:
?1)t1、t2、T1 、T2 都可以訪問,但是T1 訪問的其實是t1 表中數據,原來大寫的T1 表中數據無法訪問,訪問的都是小寫的t1 表。
?2)t3 、T3 不管大寫小寫,都不能訪問,這是因為改完參數后,默認都已小寫的形式查詢,但是小寫的t3 表確實不存在,導致了原來大寫的T3表也無法訪問。
3.3 解決辦法
- 我們嘗試rename 大寫的T1表,改成小寫:
mysql> rename table T1 to t4;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_sxcdb |
+-----------------+
| T1 |
| T3 |
| t2 |
| t4 |
+-----------------+
4 rows in set (0.00 sec)
結論:可以看到rename 操作,一樣是操作的t1表,而不是大寫的T1表,我們需要先將參數lower_case_table_names先還原回去,再rename T1 表。
-- 先還原回去
mysql> rename table t4 to t1;
Query OK, 0 rows affected (0.00 sec)
-- 還原參數
[root@mysql57 ~]# cat /etc/my.cnf|grep lower_case_table_names
#lower_case_table_names=1
[root@mysql57 ~]# systemctl restart mysqld
mysql> use sxcdb;
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>
mysql> show tables;
+-----------------+
| Tables_in_sxcdb |
+-----------------+
| T1 |
| T3 |
| t1 |
| t2 |
+-----------------+
4 rows in set (0.00 sec)
mysql> rename table T1 to t4;
Query OK, 0 rows affected (0.00 sec)
mysql> rename table T3 to t3;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_sxcdb |
+-----------------+
| t1 |
| t2 |
| t3 |
| t4 |
+-----------------+
4 rows in set (0.00 sec)
3.4 再次查詢
- 重新修改參數,設置不區分大小寫
[root@mysql57 ~]# cat /etc/my.cnf|grep lower_case_table_names
lower_case_table_names=1
[root@mysql57 ~]# systemctl restart mysqld
mysql> use sxcdb;
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 t1;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from t2;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from t4;
+------+------+
| id | name |
+------+------+
| 1 | T1 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from T3;
+------+
| id |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from T4;
+------+------+
| id | name |
+------+------+
| 1 | T1 |
+------+------+
1 row in set (0.00 sec)
結論:t1、t2、t3、t4、T3、T4 都可以訪問,不再區分表名大小寫。?
3.5 建表
-- 在不區分大小寫情況下:
mysql> create table T5 (id int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t5 (id int);
ERROR 1050 (42S01): Table 't5' already exists
mysql> show tables;
+-----------------+
| Tables_in_sxcdb |
+-----------------+
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+-----------------+
5 rows in set (0.00 sec)
結論:我們可以看到,建表的時候,指定了大寫,實際存儲的也是小寫。
4. 8.0 版本不支持修改
4.1 修改參數
-- 當前參數是默認值 0
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.00 sec)
-- 修改參數
[root@mysql8 ~]# cat /etc/my.cnf|grep lower_case_table_names
lower_case_table_names=1
-- 啟動報錯
[root@mysql8 ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
-- 查看錯誤日志信息:提示lower_case_table_names參數值不一樣,數據字典初始化失敗。
2023-08-14T13:40:00.846774+08:00 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.19) starting as process 1424
2023-08-14T13:40:01.568343+08:00 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-08-14T13:40:01.568679+08:00 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-08-14T13:40:01.568870+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-08-14T13:40:02.102280+08:00 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.19) MySQL Community Server - GPL.
- 查看官方文檔 ,可以看到下面描述:8.0 版本lower_case_table_names變量只能在MySQL服務器初始化時配置,初始化后不允許修改。
lower_case_table_names variable can only be configured when the MySQL server is initialized.Changing the lower_case_table_names setting after the server is initialized is prohibited.
參考連接:MySQL :: MySQL 8.0 Reference Manual :: 9.2.3 Identifier Case Sensitivity
4.2 解決方案
--1)如果不需要數據遷移
刪除 data 目錄下的所有文件,重新初始化并且指定 lower_case_table_names 值。
--2)如果需要數據遷移,大致步驟如下:
a. 先轉化數據庫名、表名、字段名為想要的大小寫,然后mysqldump導出數據
b. 設置lower_case_table_names值,并重新初始化數據庫。
c. 創建新實例,導入之前mysqldump導出的數據。
5. 結論
-
lower_case_table_names 不能在線修改。
-
系統庫information_schema及其之下的表名是不區分大小寫的。
-
在5.7版本中,原來的表中既有大寫,也有小寫的表名情況下,修改參數不區分大小后:
? 1)原來大寫的表無法訪問,如果存在同名的小寫表,則實際操作、訪問的都是小寫的表;
? 2)新建的表,即使指定表名是大寫的,實際存在的也是小寫的表名。
-
8.0 版本lower_case_table_names變量只能在MySQL服務器初始化時配置,初始化后不允許修改。
所以,是否需要啟用大小寫不區分參數,要提前規劃好,尤其是在8.0版本中,初始化之后,不再允許修改,需要更加小心,防止需要數據遷移,導致其他問題。




