数据库|MySQL到底大小写敏感还是不敏感? 数据库|sql|mysql

目录
一、MySQL是大小写敏感的吗
二、MySQL的大小写敏感是由参数控制的
三、MySQL大小写敏感如何设置
四、开发注意事项
五、总结

一、MySQL是大小写敏感的吗在工作中，大家可能遇到过在本机开发的程序运行一切正常，发布到服务器后就出现表名找不到的问题，或者当想要查询一条数据时，总是会多出来两条或多条，这其实是因为MySQL设置了大小写敏感造成的。MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写的，如果你稍不注意，就会出现上述问题。
由于这个原因，在阿里巴巴规约中这样要求：

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

因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免引起不必要的麻烦。
二、MySQL的大小写敏感是由参数控制的 mysql大小写敏感配置相关的两个参数，lower_case_file_system和lower_case_table_names。
查看当前mysql的大小写敏感配置，可以使用如下语句

show global variables like '%lower_case%'; +------------------------+-------+ | Variable_name| Value | +------------------------+-------+ | lower_case_file_system | ON| | lower_case_table_names | 0| +------------------------+-------+

参数说明如下：
lower_case_file_system，代表当前系统文件是否大小写敏感，只读参数，无法修改。ON 大小写不敏感，OFF 大小写敏感。

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

lower_case_table_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语句将库表名转换成小写。

文章图片

测试：
数据库：MySQL 5.7.22
参数：lower_case_table_names=0
（1）查看参数值

文章图片

（2）创建test表，并插入一条数据

文章图片

（3）查询不同大小写的表名，对比两组查询，可以知linux下，mysql默认对表名大小写敏感。

文章图片

（4）利用‘Aa’和‘aa’查询出相同的结果，说明查询时对字段内容进行了转换（大写转换成小写），造成了这一现象。

文章图片

（5）对字段‘Name'也可查询结果，表明在查询时对字段的大小写不敏感。

文章图片

综合以上几个Case可得出如下结论：
①linux默认情况下，对数据库名和表名的大小写是敏感的，因为linux系统本身对大小写就敏感。
②字段内容大小写不敏感。
③列名大小写不敏感。

文章图片

字段内容及列名大小写不敏感的解决方案
①创建表时，将字段标记为binary，二进制大小写是敏感的。

文章图片

文章图片

②在查询条件的字段前加binary，但是不建议这么做，因为会使字段索引失效。
③建表时设置校对规则（collate）。
collate规则：
*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写。
*_ci: case insensitive collation，不区分大小写。

文章图片

文章图片

④如果建表时校对规则未用*_bin，则可直接修改字段的属性。

alter table test3 modify name varchar(20) collate utf8_bin;

文章图片

只要在创建表的时候指定collate为utf8_bin，就可以实现大小写敏感，如果建表时未指定，则可修改字段的校对规则，也可以实现大小写敏感。
三、MySQL大小写敏感如何设置在Linux系统中修改my.cnf文件，在Windows下修改my.ini文件，新增或修改以下内容。

lower_case_table_names = 0 或 lower_case_table_names = 1

然后重启MySQL服务才可以生效。
四、开发注意事项

如果要将lower_case_table_names从0（敏感）修改为1（不敏感）时，必须先对旧数据表的表名进行处理，把所有数据库的表名先改为小写，最后再设置lower_case_table_names为1，否则依然会出现无法找到表名的问题。
在Windows上lower_case_table_names默认值为1（不敏感），在macOS上默认值为2（不敏感）。在Linux上不支持值2，服务器强制该值为0（敏感）。
如果在数据目录驻留在不区分大小写的文件系统（例如Windows或macOS）上，则不应将lower_case_table_names设置为0，否则将出现MySQL服务无法启动的问题。

五、总结由于操作系统不同导致大小写敏感的默认设置不一致，我们在开发时一定要注意，应该养成严格的意识，SQL语句一律采用小写字母，避免无意义的踩坑。
MySQL是日常工作中使用最对的数据库之一，所以必须要要较为深入和全面的掌握，对于高阶人员还要掌握分布式事务、各种数据库锁、传播机制等。

【数据库|MySQL到底大小写敏感还是不敏感?】欢迎访问个人博客Jackin's Blog