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版本中，初始化之后，不再允許修改，需要更加小心，防止需要數據遷移，導致其他問題。
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
