小知识：MySQL修改lower_case_table_names参数

Alfred Zhao

发布于 2020-07-02 17:03:22

5.1K0

发布于 2020-07-02 17:03:22

文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

环境：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进行导出备份，然后删除所有含有大写的表，成功修改参数后再进行导入即可。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-07-01 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库