MySQL表名的大小写敏感设定

MySQL的Table名大小写敏感是使用lower_case_table_names进行的控制，由于版本和安装的OS不同导致缺省动作不同，所以会产生此问题。

问题描述

MacOS上安装的MySQL，目前版本为8.0，而大小写是不敏感的，所以对同一表名的引用可以使用大小写不必区分的方式，示例如下

mysql> desc QRTZ_LOCKS;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| SCHED_NAME | varchar(120) | NO   | PRI | NULL    |       |
| LOCK_NAME  | varchar(40)  | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc qrtz_locks;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| SCHED_NAME | varchar(120) | NO   | PRI | NULL    |       |
| LOCK_NAME  | varchar(40)  | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql>

确认开关设定lower_case_table_names

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 2     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> 

而在MySQL官方镜像5.7.16版本进行确认则得到如下结果

mysql> desc qrtz_locks;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| SCHED_NAME | varchar(120) | NO   | PRI | NULL    |       |
| LOCK_NAME  | varchar(40)  | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc QRTZ_LOCKS;
ERROR 1146 (42S02): Table 'jeecg-boot.QRTZ_LOCKS' doesn't exist
mysql> 

确认开关设定lower_case_table_names，则发现设定值不同

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql>

官方说明

在Mysql8.0的说明文档中，有如下说明内容，明确说明了缺省方式下，Unix为0，Windows为1，MacOS为2，这也导致上上述行为的不同。

设定方式

此设定在MySQL初始化阶段完成，所以需要重启，这里设定到设定文件中，在MySQL5.7.16的标准镜像中，可以设定如下文件

设定文件：/etc/mysql/mysql.conf.d/mysqld.cnf

设定示例

# echo "lower_case_table_names=1" >>/etc/mysql/mysql.conf.d/mysqld.cnf
# 
注：注意不要设定成2，会有可能设定不上

重启mysql镜像即可确认lower_case_table_names设定已然生效

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.01 sec)

mysql>

再次确认结果，发现大小写已经不再敏感

mysql> desc qrtz_locks;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| SCHED_NAME | varchar(120) | NO   | PRI | NULL    |       |
| LOCK_NAME  | varchar(40)  | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc QRTZ_LOCKS;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| SCHED_NAME | varchar(120) | NO   | PRI | NULL    |       |
| LOCK_NAME  | varchar(40)  | NO   | PRI | NULL    |       |
+------------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> 

总结

这篇文章memo了MySQL表名大小写敏感相关的确认和设定方式并给出了基于官方镜像MySQL5.7.16的设定示例，需要注意的是示例中直接修改的方式如果删除镜像会导致未持久化存储的设定丢失。

参考内容

https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html