学而实习之 不亦乐乎

MySQL大小写敏感的解决方案

2023-03-01 21:40:24

一、MySQL 的大小写敏感问题

在工作中,大家可能遇到过在本机开发的程序运行一切正常,发布到服务器后就出现表名找不到的问题,其实极有可能是 MySQL 设置了大小写敏感造成的。MySQL在Windows下不区分大小写,但在Linux下默认是区分大小写的,如果你稍不注意,就会出现上述问题。
概括一下就是:
(1) lower_case_table_names = 0(Unix默认)
表名存储为给定的大小和比较是区分大小写的。

(2) lower_case_table_names = 1(Windows默认)
表名存储在磁盘是小写的,但是比较的时候是不区分大小写。

(3) lower_case_table_names = 2(macOS默认)
表名存储为给定的大小写,但是比较的时候是小写的。

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

这样一来,数据库名、表名、字段名,都不允许出现任何大写字母,避免引起不必要的麻烦。

二、MySQL的大小写敏感的控制参数

mysql大小写敏感配置相关的两个参数,lower_case_file_system和lower_case_table_names。如下语句查看当前mysql的大小写敏感配置:

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 大小写敏感。

三、解决方案

如果你只是在一个操作系统上使用MySQL,不用关注参数值的不同带来的差异,但如果你需要在不同大小写敏感的平台之间迁移数据,就得关注这个参数了。比如说,在 Unix 下可以创建两张表,一个叫 TBL,一个叫 tbl,但是在Windows,他们指的就是同一张表。

因此为了避免这种迁移过程中大小写敏感的问题,有两个选择:
1. 在所有的系统中,都设置 lower_case_table_names 为 1,唯一的缺点,就是当时用show tables或者show databases,不会看到原始大小写。
2. Unix中设置 lower_case_table_names 是 0,Windows 中设置 lower_case_table_names 是 2。缺点就是需要确保在Windows中引用正确大小写的数据库和表名。

Unix中如果将lower_case_table_names改为1,在重启进程前,需要将旧的数据库和表名改成小写(如果不改,会导致已存在的大写对象不能使用),可以使用rename,

RENAME TABLE T1 TO t1;

如果有很多数据库需要改,则可以导出,设置参数,再导入,

1. 导出数据库,

mysqldump --databases db1 > db1.sql
mysqldump --databases db2 > db2.sql
...

2. drop database删除数据库。
3. stop server -> 设置 lower_case_table_names -> restart server。
4. 重新加载数据库,

mysql < db1.sql
mysql < db2.sql
...

大小写敏感的问题看着很小,但若不注意,尤其存在跨平台的需求,就会更加复杂。其实一定程度上,这就牵扯到了设计开发规范的问题,如果要求MySQL的对象设计和代码开发,都统一使用小写,lower_case_table_names 是多少就不那么重要了。