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

184次閱讀

共計(jì) 3561 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

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

MYSQL 對(duì)大小寫(xiě)敏感

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

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

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

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

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

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

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

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

MySQLSyntaxErrorException: Table kytu.tb_sutyHo doesn t exist

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

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

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

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

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

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

于是就利用搜索引擎，發(fā)現(xiàn) Mysql 中控制數(shù)據(jù)庫(kù)名和表名的大小寫(xiě)敏感由參數(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ù)還沒(méi)有感覺(jué)，不知道具體是什么意思。

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

lowercasefile_system

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

lowercasetable_names

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

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

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

2 – 大小寫(xiě)不敏感（OS X 默認(rèn)）創(chuàng)建的庫(kù)表將原樣保存在磁盤(pán)上。但 SQL 語(yǔ)句將庫(kù)表名轉(zhuǎn)換成小寫(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 上，默認(rèn)值為 1。在 macOS 上，默認(rèn)值為 2。在 Linux 上不支持值 2; 服務(wù)器強(qiáng)制該值為 0。

并且官網(wǎng)也提示說(shuō)：如果在數(shù)據(jù)目錄駐留在不區(qū)分大小寫(xiě)的文件系統(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ì)大小寫(xiě)不敏感，見(jiàn)下圖：

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

02 注意事項(xiàng)

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

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

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)存在的庫(kù)表名轉(zhuǎn)換為小寫(xiě)。

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

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

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

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

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

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

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

以上是“如何解決 MySQL 大小寫(xiě)敏感導(dǎo)致的問(wèn)題”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完