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服务器初始化时配置，初始化后不允许修改。
lower_case_table_names 不能在线修改。
系统库information_schema及其之下的表名是不区分大小写的。
在5.7版本中，原来的表中既有大写，也有小写的表名情况下，修改参数不区分大小后：
1）原来大写的表无法访问，如果存在同名的小写表，则实际操作、访问的都是小写的表；
2）新建的表，即使指定表名是大写的，实际存在的也是小写的表名。
8.0 版本lower_case_table_names变量只能在MySQL服务器初始化时配置，初始化后不允许修改。
所以，是否需要启用大小写不区分参数，要提前规划好，尤其是在8.0版本中，初始化之后，不再允许修改，需要更加小心，防止需要数据迁移，导致其他问题。
