如何解决MySQL大小写敏感导致的问题

89次阅读
没有评论

共计 3561 个字符,预计需要花费 9 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下如何解决 MySQL 大小写敏感导致的问题,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!

MYSQL 对大小写敏感

见字如面,见标题知内容。你有遇到过因为 MYSQL 对大小写敏感而被坑的体验吗?

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

【强制】表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只 出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。

说明:MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、表名、字段名,都不允许出现任何大写字母,避免节外生枝。

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

如果没有真正遇到过类似的问题,有时候干巴巴的看这些规约体会不深,理解起来似懂非懂,并且也只是死记硬背而已。

01 一个表字母大小故事

最近自己在鼓捣一个项目玩玩,在自己本机上开发和测试过程中一直没有问题,但是部署到 Linux 服务器上后,发现有报错,日志信息大概是:

MySQLSyntaxErrorException: Table kytu.tb_sutyHo doesn t exist

出现了问题,有点郁闷,本地开发好好的,怎么部署服务器就不行了。有鬼……不过莫慌。看着错误提示很明显,不就是 tb_sutyHo 表不存在吗!

①于是我不慌不忙打开 nv(navicat),查看这个表在不在,一看还真在,数据库中显示的 tb_sutyho,不过 h 是小写;

②查看代码发现代码中还真把表名写成 tb_sutyHo , 就一个 h 写成大写 H 了。

问题找到了,原来是不小心写 SQL 的时候没有写对表名,改一下表名就搞定了,功能也一切正常了。一般情况下故事到这里也就应该结束了?问题找到了,也修复了,万事大吉了,稍后就可以吃鸡了。

对于不会玩吃鸡的我,到这里并没有结束,找到问题和解决问题的确很重要,但是找到问题出现的根源更重要,这样就能在下次规避此类问题,作为一个程序员不要两次掉入一个坑里。

我在想这个问题,本地 Window 环境怎么就一直没有出现这个报错提示呢?非要等我部署服务器才出现,这到底是什么问题?(如果你对 Mysql 大小敏感很了解,以下内容可以跳过….)

于是就利用搜索引擎,发现 Mysql 中控制数据库名和表名的大小写敏感由参数 lower_case_table_names 控制。

在本机 Window 环境查看如下:

mysql  show variables like  %case% 
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 1 |
+------------------------+-------+

在 Linux 服务器查看如下:

mysql  show variables like  %case% 
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+

从上面的结果已经可以看出不同了,然而对这两个参数还没有感觉,不知道具体是什么意思。

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

lowercasefile_system

此变量描述数据目录所在的文件系统上文件名的区分大小写。OFF 表示文件名区分大小写,ON 表示它们不区分大小写。此变量是只读的,因为它反映了文件系统属性并设置它对文件系统没有影响。

lowercasetable_names

该参数为静态,可设置为 0、1、2。

0 – 大小写敏感。(Unix,Linux 默认)创建的库表将原样保存在磁盘上。如 create database TeSt; 将会创建一个 TeSt 的目录,create table AbCCC …将会原样生成 AbCCC.frm。SQL 语句也会原样解析。

1 – 大小写不敏感。(Windows 默认)创建的库表时,MySQL 将所有的库表名转换成小写存储在磁盘上。SQL 语句同样会将库表名转换成小写。如需要查询以前创建的 Testtable(生成 Testtable.frm 文件),即便执行 select * from Testtable,也会被转换成 select * from testtable,致使报错表不存在。

2 – 大小写不敏感(OS X 默认)创建的库表将原样保存在磁盘上。但 SQL 语句将库表名转换成小写。

On Windows the default value is 1. On macOS, the default value is 2. On Linux, a value of 2 is not supported; the server forces the value to 0 instead.

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

并且官网也提示说:如果在数据目录驻留在不区分大小写的文件系统(例如 Windows 或 macOS)上的系统上运行 MySQL,则不应将 lowercasetable_names 设置为 0。

我自己在我的 window10 环境尝试设置 lower_case_table_names 为 0 的时候,MySQL 的服务怎么也启动不能,启动服务报错。windows 系统对大小写不敏感,见下图:

注:如果要修改 lower_case_table_names 这个值,windows 下修改 my.ini,Linux 下修改 my.cnf 配置文件,需要重启服务, 具体操作可以自行上网找资料。

02 注意事项

修改 lowercasetable_names 导致的常见不良隐患:如果在 lower_case_table_names= 0 时,创建了含有大写字母的库表,改为 lower_case_table_names= 1 后,则会无法被查到。

首先设置 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;
show tables;
+----------------+
| Tables_in_aflyun |
+----------------+
| Student |
+----------------+

再设置 lower_case_table_names=1, 执行查询,不管表名是大写还是小写,都提示表不存在。

mysql  select * from Student;
1146 - Table  aflyun.Student  doesn t exist
mysql  select * from student;
1146 - Table  aflyun.student  doesn t exist

解决方法:如果要将默认的 lower_case_tables_name 为 0 设置成 1,需先将已经存在的库表名转换为小写。

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

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

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

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

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

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

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

以上是“如何解决 MySQL 大小写敏感导致的问题”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-04发表,共计3561字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)