你有遇到过因为MySQL对大小写敏感而被坑的体验吗?

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 你有遇到过因为MySQL对大小写敏感而被坑的体验吗?

文章目录


前言


MySQL中操作系统对大小写的敏感性决定了数据库和表的大小写敏感,本文将自己遇到的坑分享给大家


⛳️ 1.一个表字母大小故事


🐴 1.1 故障现象

最近接了一个项目跟团队在开发,在自己本机Windows上开发和测试过程中一直没有问题,

临近项目交付的时候,部署到Linux服务器上后,发现有报错,日志信息大概是:

MySQLSyntaxErrorException: Table ‘mes_db.student’ doesn’t exist

image.png


出现了问题,有点郁闷,本地开发好好的,怎么部署服务器就不行了。

有鬼…不过莫慌。看着错误提示很明显,不就是student表不存在吗!

①于是我不慌不忙打开navicat,查看这个表在不在,一看还真在,

数据库中显示的student,不过s是小写;

②查看代码发现代码中还真把表名写成Student,就一个s写成大写S了。

问题找到了,原来是不小心写SQL的时候没有写对表名,改一下表名就搞定了,功能也一切正常了

我在想这个问题,

本地Window环境怎么就一直没有出现这个报错提示呢?

非要等我部署到Linux服务器才出现,这到底是什么问题?

📢📢📢📣📣📣

赶快请教了下我的师傅,发现Mysql在 Linux 下默认是区分大小写的。


🚩在本机Window环境查看如下:

mysql> show variables like ‘%case%’;

image.png

🚩在Linux服务器查看如下:

image.png

从上面的结果已经可以看出不同了

当 lower_case_table_names 为 0 时表示区分大小写,为 1 时表示不区分大小写

在Windows上,默认值为1;在macOS上,默认值为2;在Linux上不支持值2;服务器强制该值为0

0 --大小写敏感。(Unix,Linux默认)

1–大小写不敏感。(Windows默认)

2 --大小写不敏感(macOS默认)

并且官网也提示说:如果在数据目录驻留在不区分大小写的文件系统

(例如Windows或macOS)上的系统上运行MySQL,

则不应将lower_case_table_names设置为0。

我自己在我的window10环境尝试设置lower_case_table_names为0的时候,

MySQL的服务怎么也启动不能,启动服务报错,因为windows系统对大小写不敏感


🐴 1.2 Linux环境大小写规则


之前看过阿里巴巴Java开发手册,在MySql建表规约里有看到:

【强制】表名、字段名必须使用小写字母或数字 ,

禁止出现数字开头,禁止两个下划线中间只 出现数字。

数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。

说明: MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。

因此,数据库名、 表名、字段名,都不允许出现任何大写字母,避免节外生枝。

image.png

正例: aliyun _ admin , rdc _ config , level 3_ name

反例: AliyunAdmin , rdcConfig , level 3 name


Linux 下数据库名、表名、列名、别名大小写规则是这样的

1、 数据库名与表名是严格区分大小写

2、 表的别名是严格区分大小写

3、 列名均是忽略大小写的

4、 变量不区分大小写

5、 索引、关键字、函数名、存储过程和事件的名字不区分字母的大小写

6、 触发器的名字要区分字母的大小写


🌈 在介绍lower_case_table_names的时候,顺便也说一下lower_case_file_system。

其实,在 MySQL 中,有一个只读的系统变量lower_case_file_system

OFF表示文件名区分大小写,

ON表示它们不区分大小写

因为此变量是只读的,所以无法修改


⛳️ 2.修改大小写导致的隐患


本次隐患是针对是在Linux环境的哈

修改lower_case_table_names导致的常见不良隐患:

如果在lower_case_table_names=0时,创建了含有大写字母的库表,

改为lower_case_table_names=1后,则会无法被查到

image.png

🌈系统设置lower_case_table_names=0


CREATE TABLE Student (

id int(11) unsigned NOT NULL AUTO_INCREMENT,

name varchar(25) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


mysql> show tables;

image.png

再设置lower_case_table_names=1,让系统取消大小写敏感

Linux下修改my.cnf配置文件,需要重启服务

✨ ✨ ✨ ✨ ✨ ✨ ✨ ✨ ✨ ✨ ✨

只需要在就需要在[mysqld]下面添加一行配置,

即 lower_case_table_names=1

其中 0:区分大小写,1:不区分大小写

然后重启mysql服务即可!

image.png

执行查询,不管表名是大写还是小写,都提示表不存在。

ERROR 1146 (42S02): Table ‘mes_db.student’ doesn’t exist

解决方法:

如果要将默认的lower_case_tables_name为0设置成1 ,再将已经存在的库表名转换为小写即可

注意以下要点:

针对仅表名存在大写字母的情况

①ower_case_tables_name=0时,执行rename table成小写。

②设置lower_case_tables_name=1,重启生效。

针对库名存在大写字母的情况:

①lower_case_tables_name=0时,使用mysqldump导出,并删除老的数据库。

②设置lower_case_tables_name=1,重启生效。

③导入数据至实例,此时包含大写字母的库名已转换为小写。


⛳️ 3.window环境大小写不敏感处理


##要让 mysql查询区分大小写,可以:

select * from table_name where binary a like ‘a%’

select * from table_name where binary a like ‘A%’

##也可以在建表时,加以标识

##建表时加

create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin;

create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs;

*_bin: 表示的是 binary case sensitive collation,也就是说是区分大小写的

*_cs: case sensitive collation,区分大小写

*_ci: case insensitive collation,不区分大小写


⛳️ 4.总结教训


有了踩坑的经验,对开头说的阿里Mysql规约理解更加深入了。

操作系统不同导致大小写敏感不一致。我们在开发时,应该按大小写敏感的原则去开发,

这样可以使开发的程序兼容不同的操作系统。

因此,建议在开发测试环境下把lower_case_table_names的值设为0

便于在开发中就严格控制代码大小写敏感,提高代码的兼容和严谨。



相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
SQL 关系型数据库 MySQL
使用CTAS 把mysql 表同步数据 到hologres ,Flink有什么参数可以使hologres 的字段都小写吗?
使用CTAS 把mysql 表同步数据 到hologres ,Flink有什么参数可以使hologres 的字段都小写吗?
345 0
|
12天前
|
关系型数据库 MySQL Unix
MySQL配置不区分大小写的方法
结论 通过适当配置 lower_case_table_names参数以及在数据定义和查询中选择合适的校对规则,可以灵活地控制MySQL中的大小写敏感性,以适应不同的应用场景和需求。这样的设置既可以增加数据库的兼容性,又可以在必要时利用大小写敏感性进行精确的数据处理。需要注意的是,修改 lower_case_table_names参数后,最好在数据库初始化时进行,以避免现有表名的大小写问题。
33 3
|
2月前
|
存储 关系型数据库 MySQL
"Linux环境下MySQL数据库名及表名大小写敏感性设置详解:从配置到影响,确保数据库操作的准确与高效"
【8月更文挑战第9天】在Linux环境中,MySQL数据库名及表名的大小写敏感性是一项重要配置。默认情况下,MySQL在Linux上区分大小写,但这可通过配置文件 `/etc/my.cnf` 中的 `lower_case_table_names` 参数调整。该参数设为0时,名称存储时保持原样,查询时不区分大小写;设为1则全部转换为小写。通过编辑配置文件并重启MySQL服务,可根据需求灵活控制名称的大小写敏感性,确保数据一致性和应用兼容性。
74 3
|
5月前
|
SQL 关系型数据库 MySQL
mysql 大小写、sql_mode
mysql 大小写、sql_mode
|
10月前
|
关系型数据库 MySQL 容器
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
445 0
docker-compose安装mysql并指定忽略表名大小写以及挂载配置文件
|
5月前
|
存储 关系型数据库 MySQL
docker安装mysql8忽略大小写
docker安装mysql8忽略大小写
599 0
|
5月前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
Ubuntu 20.04 + mysql8 安装以及配置大小写不敏感
410 0
|
5月前
|
Ubuntu 关系型数据库 MySQL
解决方案:MySQL数据表明明存在,但是就是报错表不存在,原来是需要配置 MySQL 忽略表名大小写!
解决方案:MySQL数据表明明存在,但是就是报错表不存在,原来是需要配置 MySQL 忽略表名大小写!
296 0
|
5月前
|
SQL 关系型数据库 MySQL
mysql的大小写对性能的影响问题
mysql的大小写对性能的影响问题
101 0
|
5月前
|
关系型数据库 MySQL 数据库
解决mysql导入新数据库大小写问题(Table 'zup.Domain_System' doesn't exist)
解决mysql导入新数据库大小写问题(Table 'zup.Domain_System' doesn't exist)
66 0
下一篇
无影云桌面