MySQL大小寫敏感導(dǎo)致的問題分析

MYSQL對(duì)大小寫敏感

見字如面，見標(biāo)題知內(nèi)容。你有遇到過因?yàn)镸YSQL對(duì)大小寫敏感而被坑的體驗(yàn)嗎？

之前看過阿里巴巴Java開發(fā)手冊(cè)，在MySql建表規(guī)約里有看到：

【強(qiáng)制】表名、字段名必須使用小寫字母或數(shù)字 ， 禁止出現(xiàn)數(shù)字開頭，禁止兩個(gè)下劃線中間只 出現(xiàn)數(shù)字。數(shù)據(jù)庫字段名的修改代價(jià)很大，因?yàn)闊o法進(jìn)行預(yù)發(fā)布，所以字段名稱需要慎重考慮。

說明： MySQL 在 Windows 下不區(qū)分大小寫，但在 Linux 下默認(rèn)是區(qū)分大小寫。因此，數(shù)據(jù)庫名、 表名、字段名，都不允許出現(xiàn)任何大寫字母，避免節(jié)外生枝。

正例： aliyun _ admin ， rdc _ config ， level 3_ name 反例： AliyunAdmin ， rdcConfig ， level 3 name

如果沒有真正遇到過類似的問題，有時(shí)候干巴巴的看這些規(guī)約體會(huì)不深，理解起來似懂非懂，并且也只是死記硬背而已。

01 一個(gè)表字母大小故事

最近自己在鼓搗一個(gè)項(xiàng)目玩玩，在自己本機(jī)上開發(fā)和測(cè)試過程中一直沒有問題，但是部署到Linux服務(wù)器上后，發(fā)現(xiàn)有報(bào)錯(cuò)，日志信息大概是：

MySQLSyntaxErrorException: Table 'kytu.tb_sutyHo' doesn't exist

出現(xiàn)了問題，有點(diǎn)郁悶，本地開發(fā)好好的，怎么部署服務(wù)器就不行了。有鬼……不過莫慌。看著錯(cuò)誤提示很明顯，不就是tb_sutyHo 表不存在嗎！

①于是我不慌不忙打開nv（navicat），查看這個(gè)表在不在，一看還真在，數(shù)據(jù)庫中顯示的tb_sutyho ，不過h是小寫；

②查看代碼發(fā)現(xiàn)代碼中還真把表名寫成tb_sutyHo ,就一個(gè)h寫成大寫H了。

問題找到了，原來是不小心寫SQL的時(shí)候沒有寫對(duì)表名，改一下表名就搞定了，功能也一切正常了。一般情況下故事到這里也就應(yīng)該結(jié)束了？問題找到了，也修復(fù)了，萬事大吉了，稍后就可以吃雞了。

對(duì)于不會(huì)玩吃雞的我，到這里并沒有結(jié)束，找到問題和解決問題的確很重要，但是找到問題出現(xiàn)的根源更重要，這樣就能在下次規(guī)避此類問題，作為一個(gè)程序員不要兩次掉入一個(gè)坑里。

我在想這個(gè)問題，本地Window環(huán)境怎么就一直沒有出現(xiàn)這個(gè)報(bào)錯(cuò)提示呢？非要等我部署服務(wù)器才出現(xiàn)，這到底是什么問題？（如果你對(duì)Mysql大小敏感很了解，以下內(nèi)容可以跳過….）

于是就利用搜索引擎，發(fā)現(xiàn)Mysql中控制數(shù)據(jù)庫名和表名的大小寫敏感由參數(shù)lower_case_table_names控制。

在本機(jī)Window環(huán)境查看如下：

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

在Linux服務(wù)器查看如下：

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

從上面的結(jié)果已經(jīng)可以看出不同了，然而對(duì)這兩個(gè)參數(shù)還沒有感覺，不知道具體是什么意思。

在介紹lower_case_table_names的時(shí)候，順便也說一下lower_case_file_system。

lowercasefile_system

此變量描述數(shù)據(jù)目錄所在的文件系統(tǒng)上文件名的區(qū)分大小寫。 OFF表示文件名區(qū)分大小寫，ON表示它們不區(qū)分大小寫。此變量是只讀的，因?yàn)樗从沉宋募到y(tǒng)屬性并設(shè)置它對(duì)文件系統(tǒng)沒有影響。

lowercasetable_names

該參數(shù)為靜態(tài)，可設(shè)置為0、1、2。

0 --大小寫敏感。（Unix，Linux默認(rèn)） 創(chuàng)建的庫表將原樣保存在磁盤上。如create database TeSt;將會(huì)創(chuàng)建一個(gè)TeSt的目錄，create table AbCCC …將會(huì)原樣生成AbCCC.frm。 SQL語句也會(huì)原樣解析。

1 --大小寫不敏感。（Windows默認(rèn)） 創(chuàng)建的庫表時(shí)，MySQL將所有的庫表名轉(zhuǎn)換成小寫存儲(chǔ)在磁盤上。 SQL語句同樣會(huì)將庫表名轉(zhuǎn)換成小寫。 如需要查詢以前創(chuàng)建的Testtable（生成Testtable.frm文件），即便執(zhí)行select * from Testtable，也會(huì)被轉(zhuǎn)換成select * from testtable，致使報(bào)錯(cuò)表不存在。

2 --大小寫不敏感（OS X默認(rèn)） 創(chuàng)建的庫表將原樣保存在磁盤上。 但SQL語句將庫表名轉(zhuǎn)換成小寫。

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上，默認(rèn)值為1。在macOS上，默認(rèn)值為2。在Linux上不支持值2;服務(wù)器強(qiáng)制該值為0。

并且官網(wǎng)也提示說：如果在數(shù)據(jù)目錄駐留在不區(qū)分大小寫的文件系統(tǒng)（例如Windows或macOS）上的系統(tǒng)上運(yùn)行MySQL，則不應(yīng)將lowercasetable_names設(shè)置為0。

我自己在我的window10環(huán)境嘗試設(shè)置lower_case_table_names為0的時(shí)候，MySQL的服務(wù)怎么也啟動(dòng)不能，啟動(dòng)服務(wù)報(bào)錯(cuò)。windows系統(tǒng)對(duì)大小寫不敏感，見下圖：

注： 如果要修改lower_case_table_names這個(gè)值，windows下修改my.ini ，Linux下修改my.cnf配置文件，需要重啟服務(wù),具體操作可以自行上網(wǎng)找資料。

02 注意事項(xiàng)

修改lowercasetable_names導(dǎo)致的常見不良隱患： 如果在lower_case_table_names=0時(shí)，創(chuàng)建了含有大寫字母的庫表，改為lower_case_table_names=1后，則會(huì)無法被查到。

首先設(shè)置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   |
+----------------+

再設(shè)置lower_case_table_names=1,執(zhí)行查詢，不管表名是大寫還是小寫，都提示表不存在。

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

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

解決方法：如果要將默認(rèn)的lower_case_tables_name為0設(shè)置成1，需先將已經(jīng)存在的庫表名轉(zhuǎn)換為小寫。

針對(duì)僅表名存在大寫字母的情況：

①、lower_case_tables_name=0時(shí)，執(zhí)行rename table成小寫。

②、設(shè)置lower_case_tables_name=1，重啟生效。

針對(duì)庫名存在大寫字母的情況：

①、lower_case_tables_name=0時(shí)，使用mysqldump導(dǎo)出，并刪除老的數(shù)據(jù)庫。

②、設(shè)置lower_case_tables_name=1，重啟生效。

③、導(dǎo)入數(shù)據(jù)至實(shí)例，此時(shí)包含大寫字母的庫名已轉(zhuǎn)換為小寫。

03 總結(jié)

有了踩坑的經(jīng)驗(yàn)，對(duì)開頭說的阿里Mysql規(guī)約理解更加深入了。操作系統(tǒng)不同導(dǎo)致大小寫敏感不一致。我們?cè)陂_發(fā)時(shí)，應(yīng)該按大小寫敏感的原則去開發(fā)，這樣可以使開發(fā)的程序兼容不同的操作系統(tǒng)。因此，建議在開發(fā)測(cè)試環(huán)境下把lower_case_table_names的值設(shè)為0，便于在開發(fā)中就嚴(yán)格控制代碼大小寫敏感，提高代碼的兼容和嚴(yán)謹(jǐn)。