MySQL - Discussion MySQL Case Sensitivity in String Searches

在使用MySQL建立table時，假設您所建立的欄位皆為String，且只定義型態為varchar

NOT NULL(且皆為primary key)，那麼表示您針對這些欄位在search時是不考慮字串的大小

寫的！因此延伸出一個問題，那就是若您在新增資料時，有可能會因為大小寫不一樣的字

串(如：abc , ABc視為相同)而出錯！

假設這個table有兩個欄位，一個是NAME、另一個是AUTHOR，定義如下：

CREATE TABLE  `book` (
`NAME` varchar(30) NOT NULL,
`AUTHOR` varchar(16) NOT NULL,
PRIMARY KEY (`NAME`,`AUTHOR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

此時，當您新增兩筆資料

INSERT INTO book (NAME, AUTHOR) VALUES ('jQuery', 'Ben');
INSERT INTO book (NAME, AUTHOR) VALUES ('jquery', 'Ben'); //error

當下database會告知Duplicate entry 'jquery-Ben' for key 'PRIMARY'！

由於table columns的設定為case insensitivity，因此不考慮大小寫的問題

不過INSERT IGNORE INTO book (NAME, AUTHOR) VALUES ('jquery', 'Ben');

在加入IGNORE指令後，雖然不會再出錯，但該筆資料也將石沈大海地被略過!!

假設第二筆資料也是必須被考慮的，那麼我們可以在定義table的欄位時，除了SET

CHARACTER為latin1，還有have the latin1_general_cs or latin1_bin collation

修改如下：

`NAME` varchar(30) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,

或MySQL GUI操作 => NAME Flags直接打勾 and Apply Changes

如此，就可以新增資料

而當我們在搜尋時，由於NAME欄位已經有考慮到case sensitivity，因此預設會比較到

binary的層面，表示將jQuery或jquery轉換為binary比較之。

MySQL Document

comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.

SELECT * FROM book b where NAME = 'jQuery'; //Result => 'jQuery', 'Ben'

jQuery => 6A 51 75 65| 72 79

jquery =>  6A 71 75 65| 72 79

兩者在16進位之下有些許差異，因此是不相等

若您的table沒有設定欄位考慮大小寫，可以在搜尋時在column前面加上binary，如此一來

就會轉換為binary來比較！

SELECT * FROM book b where binary NAME = 'jQuery';

參考文獻：MySQL官網

題外話：

關於字母大小寫的資料，這個例子舉的不太好，但小弟工作的地方Gene data就有出現

如THYA、thyA同時新增進table的問題，兩筆都剛好搭配同一個Drug Name，所以就出錯囉