阿超 > mysql大小写踩坑

mysql大小写踩坑

2022-04-12

运维

不求苍天俯就我的美意，但求永远恣意挥洒。——惠特曼《自我之歌》

设置了mysqld下的配置

1
2
[mysqld]
lower_case_table_names = 1

lower_case_table_names的官方文档：

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

描述为：

If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case-sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.3, “Identifier Case Sensitivity”.

翻译过来大致

  1. 按大小写敏感存储表名
  2. 按小写形式存储表名
    • 比较时不区分大小写
  3. 按大小写敏感存储表名
    • 以小写字母进行比较

他们的默认值在不同系统下面有区分

MacOS2 Unix0 Windows1

然后注意ubuntu下重启mysql服务不是mysqld而是mysql，下面是ubuntu的三种重启方式

1
2
3
4
5
6
# 重启服务
service mysql restart;
# 重启系统服务
systemctl restart mysql
# 重启mysql，请确认路径
/etc/init.d/mysql restart

修改后可以用这三个sql查询是否生效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select @@lower_case_table_names;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        1 |
+--------------------------+

show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

show global variables like '%lower_case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

关键点来了，quartz查询mysql中的表是按照大写表名去查，而我们此处配置的，并不是查询时忽略表名大小写，而是全部以小写表名去查询

因此配置了lower_case_table_names = 1后，大写表名用大写sql一律作废匹配不到

1
SELECT * FROM QRTZ_LOCKS

查询表名为大写QRTZ_LOCKS的表找不到

但由于我windows开发环境下的表名又为小写qrtz_locks

此时就有以下方案：

  • 配置改回lower_case_table_names = 0或者移除掉该配置(Unix下默认为0)
  • 修改表名为小写

百度有的说可以设置为2

官方文档表示：

On Linux (and other Unix-like systems), setting the value to 2 is not supported; the server forces the value to 0 instead.

翻译过来就是说Linux或者其他类似Unix的系统，不支持设置为2[1]，mysql服务端会强制使用0代替

那么这么多表名，一个一个改不是很麻烦？

这里我们可以使用mysql数据库信息函数去按规则[2]查询出表名转换为小写，再拼接成多条sql

或者使用存储过程、代码处理等方式

这里我使用以下sql：

1
2
3
4
5
6
7
SELECT
	CONCAT( 'ALTER TABLE ', table_name, ' RENAME ', LOWER( table_name ), ";" ) AS statement 
FROM
	information_schema.TABLES 
WHERE
	table_schema = '改成你的库名' 
	AND table_name LIKE 'QRTZ_%';

然后就会出现这样的结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+-----------------------------------------------------------------------+
| statement                                                             |
+-----------------------------------------------------------------------+
| ALTER TABLE QRTZ_BLOB_TRIGGERS RENAME qrtz_blob_triggers;             |
| ALTER TABLE QRTZ_CALENDARS RENAME qrtz_calendars;                     |
| ALTER TABLE QRTZ_CRON_TRIGGERS RENAME qrtz_cron_triggers;             |
| ALTER TABLE QRTZ_FIRED_TRIGGERS RENAME qrtz_fired_triggers;           |
| ALTER TABLE QRTZ_JOB_DETAILS RENAME qrtz_job_details;                 |
| ALTER TABLE QRTZ_LOCKS RENAME qrtz_locks;                             |
| ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS RENAME qrtz_paused_trigger_grps; |
| ALTER TABLE QRTZ_SCHEDULER_STATE RENAME qrtz_scheduler_state;         |
| ALTER TABLE QRTZ_SIMPLE_TRIGGERS RENAME qrtz_simple_triggers;         |
| ALTER TABLE QRTZ_SIMPROP_TRIGGERS RENAME qrtz_simprop_triggers;       |
| ALTER TABLE QRTZ_TRIGGERS RENAME qrtz_triggers;                       |
+-----------------------------------------------------------------------+

我们把结果中的sql复制下来再执行一遍即可

1
2
3
4
5
6
7
8
9
10
11
ALTER TABLE QRTZ_BLOB_TRIGGERS RENAME qrtz_blob_triggers;
ALTER TABLE QRTZ_CALENDARS RENAME qrtz_calendars;
ALTER TABLE QRTZ_CRON_TRIGGERS RENAME qrtz_cron_triggers;
ALTER TABLE QRTZ_FIRED_TRIGGERS RENAME qrtz_fired_triggers;
ALTER TABLE QRTZ_JOB_DETAILS RENAME qrtz_job_details;
ALTER TABLE QRTZ_LOCKS RENAME qrtz_locks;
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS RENAME qrtz_paused_trigger_grps;
ALTER TABLE QRTZ_SCHEDULER_STATE RENAME qrtz_scheduler_state;
ALTER TABLE QRTZ_SIMPLE_TRIGGERS RENAME qrtz_simple_triggers;
ALTER TABLE QRTZ_SIMPROP_TRIGGERS RENAME qrtz_simprop_triggers;
ALTER TABLE QRTZ_TRIGGERS RENAME qrtz_triggers;

如果不知道这些表是怎么来的，可以查看这篇quartz

结束后，我们配置的lower_case_table_names = 1即可生效，可以用show tables;检查一下

  1. 好坑，亏我还傻兮兮的试 ↩︎

  2. 此处为QRTZ_开头 ↩︎