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> 
1
2
3
4
5
6
7
8
9

而在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> 
1
2
3
4
5
6
7
8
9
10
11
12

确认开关设定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>
1
2
3
4
5
6
7
8
9

官方说明

在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，会有可能设定不上
1
2
3

重启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>
1
2
3
4
5
6
7
8
9

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

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>