mysql大小寫敏感與校對規則

標籤：使用   ar   資料   問題   sp   c   ad   ef   資料庫   

大家在使用mysql過程中，可能會遇到類似一下的問題：

[email protected] 07:42:00>select * from test where c1 like ‘ab%‘; 
+-----+ 
| c1 | 
+-----+ 
| abc | 
| ABD | 
+-----+

模糊比對 ab%，結果以AB開頭的字串也出現在結果集中，大家很自然的認為是大小寫敏感的問題。那麼mysql中大小寫敏感是如何控制的；資料庫名，表名，欄位名這些字典對象以及欄位值的大小敏感是如何控制的；以及校正規則與索引的關係，這是本文要討論的內容。

     mysql中控制資料庫名和表名的大小寫敏感由參數lower_case_table_names控制，為0時表示區分大小寫，為1時，表示將名字轉化為小寫後儲存，不區分大小寫。欄位名通常都是不區分大小寫，欄位值呢？欄位值的大小寫由mysql的校對規則來控制。提到校對規則，就不得不說字元集。字元集是一套符號和編碼，校對規則是在字元集內用於比較字元的一套規則，比如定義‘A‘<‘B‘這樣的關係的規則。不同的字元集有多種校對規則，一般而言，校對規則以其相關的字元集名開始，通常包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束 。比如 utf8字元集，utf8_general_ci,表示不區分大小寫，這個是utf8字元集預設的校對規則；utf8_general_cs表示區分大小寫，utf8_bin表示二進位比較，同樣也區分大小寫。

    校對規則通過關鍵字collate指定，比如建立資料庫d1，指定字元集為utf8，校對規則為utf8_bin

CREATE DATABASE d1 DEFAULT CHARACTER SET utf8  COLLATE utf8_bin;

通過上述語句說明資料庫d1中的資料按utf8編碼，並且是對大小寫敏感的。有時候我們建庫時，沒有指定校對規則校對時字元大小寫敏感，但是我們查詢時，又需要對字元比較大小寫敏感，就比如開篇中的例子，只想要ab打頭的字串。沒關係，mysql提供了collate文法，通過指定utf8_bin校對規則即可。

[email protected] 08:19:35>select * from test where c1 like ‘ab%‘ collate utf8_bin; 
+-----+ 
| c1 | 
+-----+ 
| abc| 
+-----+

     這裡還有另外一種方法，通過binary關鍵字，將串轉為二進位進行比較，由於大小寫字元的二進位肯定不同，因此可以認為是區分大小的一種方式。

[email protected] 07:50:35>select * from test where binary c1 like ‘ab%‘; 
+-----+ 
| c1 | 
+-----+ 
| abc | 
+-----+

     最後要說明一點的是校對規則與索引儲存的關係。因為校對規則會用於字串之間比較，而索引是基於比較有序排列的，因此校對規則會影響記錄的索引順序。下面舉一個小例子說明：

 

1	建表	create table test(c1 varchar(100), primary key(c1));	create table test2(c1 varchar(100), primary key(c1)) collate utf8_bin;
2	初始化資料	insert into test(c1) values(‘abc‘); insert into test(c1) values(‘ABD‘); insert into test(c1) values(‘ZBC‘);
3	查詢	select * from test;	select * from test2;
4	返回 結果集	| abc |  | ABD |  | ZBC |	| ABD |  | ZBC |  | abc |

                                     表1

從表1可以看到test和test2返回的結果集中，記錄的相對順序是不同的，因為是全表掃描，返回的記錄體現了主鍵順序。由於test表校正規則採用預設的utf8_general_ci，大小寫不敏感，因此abc<ABC<ZBC；同理，test2採用utf8_bin，大小寫敏感，因此ABD<ZBC<abc。

mysql大小寫敏感與校對規則