Mysql 大小写参数lower_case_table_names 学习

发表于： 2023-08-17 17:26:28
分类：网络资源
浏览：浏览(60)

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版本中，初始化之后，不再允许修改，需要更加小心，防止需要数据迁移，导致其他问题。

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论