mysql大小写踩坑

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql大小写踩坑

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

设置了mysqld下的配置

[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. 按小写形式存储表名
  • 比较时不区分大小写
  1. 按大小写敏感存储表名
  • 以小写字母进行比较

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

MacOS2Unix0Windows1

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

# 重启服务
service mysql restart;
# 重启系统服务
systemctl restart mysql
# 重启mysql,请确认路径
/etc/init.d/mysql restart

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

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一律作废匹配不到

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

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

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

+-----------------------------------------------------------------------+
| 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复制下来再执行一遍即可

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_开头 ↩︎
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
5月前
|
SQL 关系型数据库 MySQL
MySQL中的校对集/大小写敏感/sql_mode实践
MySQL中的校对集/大小写敏感/sql_mode实践
75 0
|
5天前
|
SQL 关系型数据库 MySQL
mysql 大小写、sql_mode
mysql 大小写、sql_mode
|
5天前
|
存储 关系型数据库 MySQL
docker安装mysql8忽略大小写
docker安装mysql8忽略大小写
|
5天前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
|
5天前
|
Ubuntu 关系型数据库 MySQL
解决方案:MySQL数据表明明存在,但是就是报错表不存在,原来是需要配置 MySQL 忽略表名大小写!
解决方案:MySQL数据表明明存在,但是就是报错表不存在,原来是需要配置 MySQL 忽略表名大小写!
27 0
|
5月前
|
关系型数据库 MySQL 容器
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
170 0
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
|
5天前
|
SQL 关系型数据库 MySQL
mysql的大小写对性能的影响问题
mysql的大小写对性能的影响问题
53 0
|
5天前
|
关系型数据库 MySQL 数据库
解决mysql导入新数据库大小写问题(Table 'zup.Domain_System' doesn't exist)
解决mysql导入新数据库大小写问题(Table 'zup.Domain_System' doesn't exist)
40 0
|
6月前
|
关系型数据库 MySQL Linux
linux下mysql 8.0配置大小写不敏感
linux下mysql 8.0配置大小写不敏感
115 0
|
11月前
|
关系型数据库 MySQL
Mysql字符串转换大小写
Mysql字符串转换大小写
39 0