MySQL中大小寫難題

2024-07-24 12:31:27

字體：大中小

供稿：網(wǎng)友

MySQL中大小寫問題

1.MySQL大小寫敏感規(guī)則

MySQL中，一個(gè)庫會(huì)對應(yīng)一個(gè)文件夾，庫里的表會(huì)則以文件的方式存放在文件夾內(nèi)，所以，操作系統(tǒng)對大小寫的敏感性決定了數(shù)據(jù)庫和表的大小寫敏感（MySQL有一個(gè)只讀的系統(tǒng)變量lower_case_file_system，其值反映的正是當(dāng)前文件系統(tǒng)是否區(qū)分大小寫）因此：在Windows下Mysql的數(shù)據(jù)庫和表名是大小寫不敏感的，而在大多數(shù)類型的Unix系統(tǒng)中是大小寫敏感的。

以下是MySQL詳細(xì)的大小寫區(qū)分規(guī)則：

在Linux下：

1、數(shù)據(jù)庫名與表名是嚴(yán)格區(qū)分大小寫的；
2、表的別名是嚴(yán)格區(qū)分大小寫的；
3、列名與列的別名在所有的情況下均是忽略大小寫的；
4、變量名也是嚴(yán)格區(qū)分大小寫的；

在Windows下：

全部不區(qū)分大小寫

補(bǔ)充：

1.MySQL中有一個(gè)系統(tǒng)變量：lower_case_table_names，專門用來配置是否區(qū)分據(jù)庫名與表名的大小寫。如果你的系統(tǒng)是Windows，而你又希望mysql能夠區(qū)分大小寫，那么你可以在my.ini文件中，在[mysqld]一節(jié)的最后，加入如下部分：

[plain] view plain copy
#If set to 0, table names are stored as specified and comparisons are case sensitive.
#If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.
#If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases.
lower_case_table_names=0
2.MySQL在查詢字符串時(shí)是大小寫不敏感的。如某字段值需要區(qū)分大小寫，可在定義該字段時(shí)指定BINARY屬性。

2.一種MySQL命名規(guī)范

為了避免大小寫引發(fā)的問題，一種推薦的命名規(guī)則是：在定義數(shù)據(jù)庫、表、列的時(shí)候全部采用小寫字母加下劃線的方式，不使用任何大寫字母。

3.關(guān)于Hibernate/JPA數(shù)據(jù)庫schema自動(dòng)映射的問題

使用Hibernate/JPA的工程可以自動(dòng)生成數(shù)據(jù)庫的schema,由于java有著自己的類與字段命名規(guī)則，這與mysql推薦的命名規(guī)則不太相符，于是出現(xiàn)了follow哪一種命名規(guī)范的問題。一般來說這會(huì)由團(tuán)隊(duì)和DBA協(xié)商確定，使用mysql的命名規(guī)范不會(huì)出現(xiàn)大小寫帶來的問題，對于DBA來說可能也更加友好，而使用java的命名規(guī)范則對開發(fā)人員來說無疑是更加友好的，這將省去列名的映射配置，同時(shí)也方便開發(fā)人員查看數(shù)據(jù)庫。如果你的項(xiàng)目使用了后者，你最好像上面講到的，設(shè)置一下lower_case_table_names=0。

MySQL語句中字母大小寫規(guī)則隨著語句元素的不同而變化，同時(shí)還要取決于MySQL服務(wù)器主機(jī)上的操作系統(tǒng)。

SQL關(guān)鍵字與函數(shù)名
關(guān)鍵字和函數(shù)名不區(qū)分字母的大小寫。如、abs、bin、now、version、floor等函數(shù)、SELECT、WHERE、ORDER、GROUP BY等關(guān)鍵字。
MySQL中大小寫問題

數(shù)據(jù)庫、數(shù)據(jù)表和視圖的名字
在服務(wù)器主機(jī)上，MySQL數(shù)據(jù)庫和數(shù)據(jù)表用底層文件系統(tǒng)中的目錄和文件表示。因此數(shù)據(jù)庫和數(shù)據(jù)表名字的默認(rèn)字母大小寫情況取決于服務(wù)器主機(jī)上的操作系統(tǒng)在文件名方面的規(guī)定。Windows文件名不區(qū)分字母的大小寫，所以運(yùn)行在windows主機(jī)上的MySQL服務(wù)器就不區(qū)分?jǐn)?shù)據(jù)庫和數(shù)據(jù)表名字的字母大小寫。運(yùn)行在Unix/Linux主機(jī)上的MySQL服務(wù)器區(qū)分?jǐn)?shù)據(jù)庫和數(shù)據(jù)表名字的大小寫。
MySQL中使用一個(gè)文件來表示一個(gè)試圖，所以視圖也符合上述規(guī)則。

存儲(chǔ)程序的名字
存儲(chǔ)函數(shù)、存儲(chǔ)過程、事件的名字不區(qū)分字母的大小寫。觸發(fā)器的名字要區(qū)分字母的大小寫。

數(shù)據(jù)列和索引的名字
數(shù)據(jù)列和索引的名字在MySQL環(huán)境里不區(qū)分字母的大小寫。實(shí)例如下：

MySQL的大小寫問題不是一個(gè)很大的問題，但是如果不了解的話，卻會(huì)使用戶產(chǎn)生迷惑；如下面

Sql代碼
insert into t values('A');

insert into t values('a');

當(dāng)?shù)诙l執(zhí)行的時(shí)候，如果是主鍵或者有唯一性約束的話，會(huì)發(fā)生

Sql代碼
Duplicate entry for XXX

更加迷惑的是下面的2條語句

Java代碼
insert into t values('~');

insert into t values('y');

也會(huì)發(fā)生插入錯(cuò)誤。

在查詢的時(shí)候也會(huì)出現(xiàn)這樣的問題

Java代碼
/* 查詢的結(jié)果一眼 */
select * from t where a like 'a%'
select * from t where a like 'A%'

/* 下面的查詢結(jié)果也是一樣 */
select * from t where a = 'y';
select * from t where a = ''~;

1.問題產(chǎn)生的原因

MySQL在Linux下數(shù)據(jù)庫名、表名、列名、別名大小寫規(guī)則是這樣的：

1、數(shù)據(jù)庫名與表名是嚴(yán)格區(qū)分大小寫的；
2、表的別名是嚴(yán)格區(qū)分大小寫的；
3、列名與列的別名在所有的情況下均是忽略大小寫的；
4、變量名也是嚴(yán)格區(qū)分大小寫的；

MySQL在Windows下都不區(qū)分大小寫。

MySQL在查詢字符串時(shí)是大小寫不敏感的，在編繹MySQL時(shí)一般以ISO-8859字符集作為默認(rèn)的字符集，這個(gè)字符集對大小寫不敏感,因此在比較過程中中文編碼字符大小寫轉(zhuǎn)換造成了這種現(xiàn)象。

2.解決辦法

A.表名區(qū)分大小寫

在my.conf加入：
ower_case_table_names = 0
其中 0：區(qū)分大小寫，1：不區(qū)分大小寫

B.插入查詢時(shí)區(qū)分大小寫

字段值需要設(shè)置BINARY屬性，即可區(qū)分大小寫。

設(shè)置的方法有多種：
創(chuàng)建時(shí)設(shè)置：
CREATE TABLE T(
A VARCHAR(10) BINARY
);

使用alter修改：
ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

mysql table editor中直接勾選BINARY項(xiàng)。

MySQL字符串大小寫經(jīng)常會(huì)讓我們在注冊網(wǎng)站時(shí)受困了注冊名的大小寫，出現(xiàn)這個(gè)問題的原因是：MySQL在查詢字符串時(shí)是大小寫不敏感的，在編繹MySQL時(shí)一般以ISO-8859字符集作為默認(rèn)的字符集，這個(gè)字符集對MySQL字符串大小寫不敏感,因此在比較過程中中文編碼字符大小寫轉(zhuǎn)換造成了這種MySQL字符串大小寫的問題。
方法一:

解決方法是對于包含中文的字段加上"binary"屬性，使之作為二進(jìn)制比較，例如將"name char(10)"改成"name char(10)binary"。

方法二:

如果你使用源碼編譯MySQL，可以編譯MySQL時(shí)使用--with--charset=gbk 參數(shù)，這樣MySQL就會(huì)直接支持中文查找和排序了。

方法三:

可以使用 Mysql 的 locate 函數(shù)來判斷。以上述問題為例,使用方法為:
SELECT * FROM table WHERE locate(field,'李') > 0;

方法四:
把您的Select語句改成這樣,SELECT * FROM TABLE WHERE FIELDS LIKE BINARY '%FIND%'即可!

方法五:
如果你在Windows下使用已經(jīng)編譯好的MySQL，可以修改My.ini中的字符集選項(xiàng)。
default-character-set = gb2312

如果是自己的服務(wù)器使用win系統(tǒng)，方法五是最好的方法！如果是linux系統(tǒng)，可以使用方法二！如果不是自己的機(jī)子，可以使用方法一，這樣你的用戶名是嚴(yán)格區(qū)分大小寫的！

（編輯：武林網(wǎng)）

上一篇：解決MYSQL數(shù)據(jù)從高版本導(dǎo)入低版本的難題

下一篇：MySQL Router達(dá)成MySQL的讀寫分離