代码改变世界

小知识:MySQL修改lower_case_table_names参数

2020-07-01 13:28  AlfredZhao  阅读(28361)  评论(0编辑  收藏  举报

环境:MySQL 5.7.25
起初创建环境时没有要求表名称不区分大小写,后续应用使用提出要设置lower_case_table_names=1的需求,期望表名不再区分大小写。
修改这个参数需要重启实例,另外一定要注意该参数修改会导致之前大写存储的表将无法识别,需要特殊处理。

1.默认区分大小写的环境

默认在lower_case_table_names=0的情况下,表名是严格区分大小写的,若查询时大小写弄混淆就会直接报错表不存在,现象如下:
root@mysqldb 12:33:  [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

root@mysqldb 12:33:  [test]> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.02 sec)

root@mysqldb 12:34:  [test]> select * from T1;
Empty set (0.00 sec)

root@mysqldb 12:34:  [test]> select * from t2;
Empty set (0.00 sec)

root@mysqldb 12:34:  [test]> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
root@mysqldb 12:34:  [test]> select * from T2;
ERROR 1146 (42S02): Table 'test.T2' doesn't exist
root@mysqldb 12:34:  [test]> 

2.修改参数lower_case_table_names

在my.cnf配置文件中[mysqld]标签的作用区域,增加`lower_case_table_names=1`的配置,然后重启MySQL服务:
service mysqld restart

3.验证表名区分大小写情况

重启实例后,确认参数已修改:
root@mysqldb 12:58:  [test]> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)

发现原表t2,在参数修改后,通过t2和T2都可以访问,满足需求。
但是原表T1,在参数修改后,通过t1和T1都无法访问,细看报错可以看到无论我们传入T1还是t1,都是按照小写的t1来解析。

root@mysqldb 12:46:  [(none)]> use test
Database changed
root@mysqldb 12:46:  [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
| t2             |
+----------------+
2 rows in set (0.01 sec)

root@mysqldb 12:46:  [test]> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
root@mysqldb 12:47:  [test]> select * from T2;
Empty set (0.01 sec)

root@mysqldb 12:47:  [test]> select * from T1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
root@mysqldb 12:47:  [test]> select * from t2;
Empty set (0.00 sec)

如果此时应用再重新创建T1表,就会被存储为小写的t1:

root@mysqldb 13:03:  [test]> create table T1(id int);
Query OK, 0 rows affected (0.08 sec)

root@mysqldb 13:03:  [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| T1             |
| t1             |
| t2             |
+----------------+
3 rows in set (0.00 sec)

root@mysqldb 13:03:  [test]> select * from T1;
Empty set (0.00 sec)

root@mysqldb 13:03:  [test]> select * from t1;
Empty set (0.00 sec)

至于之前的T1,如果想要清理删除,可改回lower_case_table_names=0后进行删除。

总结:如果有不区分大小写这样的需求,在建库配置时就要及早提出,不然后期更改不但要停机,还很可能需要特殊处理。
如果不幸遇到这样的情况,操作之前先使用mysqldump进行导出备份,然后删除所有含有大写的表,成功修改参数后再进行导入即可。