如何解決MySQL大小寫(xiě)敏感導致的問(wèn)題

小編給大家分享一下如何解決大小寫(xiě)敏感導致的問(wèn)題，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

MYSQL對大小寫(xiě)敏感

見(jiàn)字如面，見(jiàn)標題知內容。你有遇到過(guò)因為MYSQL對大小寫(xiě)敏感而被坑的體驗嗎？

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

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

說(shuō)明： MySQL 在 Windows 下不區分大小寫(xiě)，但在 Linux 下默認是區分大小寫(xiě)。因此，數據庫名、 表名、字段名，都不允許出現任何大寫(xiě)字母，避免節外生枝。

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

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

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

最近自己在鼓搗一個(gè)項目玩玩，在自己本機上開(kāi)發(fā)和測試過(guò)程中一直沒(méi)有問(wèn)題，但是部署到Linux上后，發(fā)現有報錯，日志信息大概是：

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

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

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

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

問(wèn)題找到了，原來(lái)是不小心寫(xiě)SQL的時(shí)候沒(méi)有寫(xiě)對表名，改一下表名就搞定了，功能也一切正常了。一般情況下故事到這里也就應該結束了？問(wèn)題找到了，也修復了，萬(wàn)事大吉了，稍后就可以吃雞了。

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

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

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

在本機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  |
+------------------------+-------+

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

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

lowercasefile_system

此變量描述數據目錄所在的文件系統上文件名的區分大小寫(xiě)。 OFF表示文件名區分大小寫(xiě)，ON表示它們不區分大小寫(xiě)。此變量是只讀的，因為它反映了文件系統屬性并設置它對文件系統沒(méi)有影響。

lowercasetable_names

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

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

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

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

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;服務(wù)器強制該值為0。

并且官網(wǎng)也提示說(shuō)：如果在數據目錄駐留在不區分大小寫(xiě)的文件系統（例如Windows或macOS）上的系統上運行MySQL，則不應將lowercasetable_names設置為0。

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

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

02 注意事項

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

首先設置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,執行查詢(xún)，不管表名是大寫(xiě)還是小寫(xiě)，都提示表不存在。

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，需先將已經(jīng)存在的庫表名轉換為小寫(xiě)。

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

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

②、設置lower_case_tables_name=1，重啟生效。

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

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

②、設置lower_case_tables_name=1，重啟生效。

③、導入數據至實(shí)例，此時(shí)包含大寫(xiě)字母的庫名已轉換為小寫(xiě)。