目录
一、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服务无法启动的问题。