這篇文章主要介紹了MySQL中查詢的有關(guān)英文字母大小寫問題的分析,作者從多個(gè)角度進(jìn)行了實(shí)驗(yàn),需要的朋友可以參考下

mysql數(shù)據(jù)庫在做查詢時(shí)候，有時(shí)候是英文字母大小寫敏感的，有時(shí)候又不是的，主要是由mysql的字符校驗(yàn)規(guī)則的設(shè)置決定的，通常默認(rèn)是不支持的大小寫字母敏感的。

1. 什么是字符集和校驗(yàn)規(guī)則？

字符集是一套符號(hào)和編碼。校對(duì)規(guī)則是在字符集內(nèi)用于比較字符的一套規(guī)則。任何一個(gè)給定的字符集至少有一個(gè)校對(duì)規(guī)則，它可能有幾個(gè)校對(duì)規(guī)則。要想列出一個(gè)字符集的校對(duì)規(guī)則，使用SHOW COLLATION語句。

校對(duì)規(guī)則一般有這些特征：

兩個(gè)不同的字符集不能有相同的校對(duì)規(guī)則。

每個(gè)字符集有一個(gè)默認(rèn)校對(duì)規(guī)則。例如，utf8默認(rèn)校對(duì)規(guī)則是utf8_general_ci。

存在校對(duì)規(guī)則命名約定：它們以其相關(guān)的字符集名開始，通常包括一個(gè)語言名，并且以_ci(大小寫不敏感)、_cs(大小寫敏感)或_bin(二元)結(jié)束。

2. 不同級(jí)別的字符集和校驗(yàn)規(guī)則可控制大小寫敏感

MySQL5.1在同一臺(tái)服務(wù)器、同一個(gè)數(shù)據(jù)庫或甚至在同一個(gè)表中使用不同字符集或校對(duì)規(guī)則來混合定義字符串。字符集和校對(duì)規(guī)則有4個(gè)級(jí)別的默認(rèn)設(shè)置：服務(wù)器級(jí)、數(shù)據(jù)庫級(jí)、表級(jí)和連接級(jí)。

2.1服務(wù)器級(jí)

MySQL按照如下方法確定服務(wù)器字符集和服務(wù)器校對(duì)規(guī)則：

(1)修改配置文件/etc/my.cnf

在[mysqld]下添加：collation_server = utf8_bin

重啟實(shí)例

更改服務(wù)器級(jí)的校驗(yàn)規(guī)則(collation_server )后，數(shù)據(jù)庫校驗(yàn)規(guī)則(collation_collation)默認(rèn)會(huì)繼承服務(wù)器級(jí)的。

注意：

這個(gè)只適用于在重新啟動(dòng)之后, 新建的庫,已存在的庫不受影響.

同樣的, 即使庫的校驗(yàn)規(guī)則改了,已經(jīng)存在的表不受修改影響;

同理與已經(jīng)存在的列...

1. mysql> create database yutest0; 
2. Query OK, 1 row affected (0.00 sec) 
3. mysql> use yutest0; 
4. Database changed 
5. mysql> create table t1 (name varchar(10)); 
6. Query OK, 0 rows affected (0.01 sec) 
7.  
8. mysql> insert into t1 values('AAA'); 
9. Query OK, 1 row affected (0.00 sec) 
10. mysql> insert into t1 values('aaa'); 
11. Query OK, 1 row affected (0.01 sec) 
12.  
13. mysql> select * from t1; 
14. +------+ 
15. | name | 
16. +------+ 
17. | AAA | 
18. | aaa | 
19. +------+ 
20. 2 rows in set (0.00 sec) 
21.  
22. mysql> select * from t1 where name='aaa'; 
23. +------+ 
24. | name | 
25. +------+ 
26. | aaa | 
27. +------+ 
28. 1 row in set (0.00 sec) 

可以看出，在服務(wù)器級(jí)進(jìn)行相應(yīng)的校對(duì)規(guī)則設(shè)置，查詢大小寫敏感。

(2)當(dāng)服務(wù)器啟動(dòng)時(shí)根據(jù)有效的選項(xiàng)設(shè)置

當(dāng)啟動(dòng)mysqld時(shí)，根據(jù)使用的初始選項(xiàng)設(shè)置來確定服務(wù)器字符集和校對(duì)規(guī)則。

shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

2.2數(shù)據(jù)庫級(jí)

MySQL這樣選擇數(shù)據(jù)庫字符集和數(shù)據(jù)庫校對(duì)規(guī)則：

如果指定了character set X和collate Y，那么采用字符集X和校對(duì)規(guī)則Y。

如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認(rèn)校對(duì)規(guī)則。

否則，采用服務(wù)器字符集和服務(wù)器校對(duì)規(guī)則。

(1)修改配置文件/etc/my.cnf

進(jìn)行了兩組測(cè)試：

1) 在[mysqld]下添加：

1. collation_server = utf8_bin 
2.  
3. collation_database = utf8_bin 

2) 在[mysqld]下添加：

1. collation_database = utf8_bin 

重啟實(shí)例，兩組都不能正常啟動(dòng)，錯(cuò)誤信息如下：

可見，my.cnf配置文件中不支持設(shè)置collation_database 變量。

(2)創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置數(shù)據(jù)庫校驗(yàn)規(guī)則

1. mysql> create database yutest default character set utf8 collate utf8_bin; 
2. Query OK, 1 row affected (0.00 sec) 
3. mysql> show variables like 'collation_%'; 
4. +----------------------+-----------------+ 
5. | Variable_name | Value | 
6. +----------------------+-----------------+ 
7. | collation_connection | utf8_general_ci | 
8. | collation_database | utf8_bin | 
9. | collation_server | utf8_general_ci | 
10. +----------------------+-----------------+ 
11. 3 rows in set (0.00 sec) 
12. mysql> select * from t1; 
13. +------+ 
14. | name | 
15. +------+ 
16. | ABC | 
17. | abc | 
18. +------+ 
19. 2 rows in set (0.00 sec) 
20.  
21. mysql> select * from t1 where name='abc'; 
22. +------+ 
23. | name | 
24. +------+ 
25. | abc | 
26. +------+ 
27. 1 row in set (0.01 sec) 

可以看出，在數(shù)據(jù)庫級(jí)進(jìn)行相應(yīng)的校對(duì)規(guī)則設(shè)置，查詢大小寫敏感。

2.3表級(jí)

MySQL按照下面的方式選擇表字符集和校對(duì)規(guī)則：

如果指定了character set X和collate Y，那么采用character set X和collate Y。

如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認(rèn)校對(duì)規(guī)則。

否則，采用數(shù)據(jù)庫字符集和服務(wù)器校對(duì)規(guī)則。

在創(chuàng)建表時(shí)設(shè)置表級(jí)校驗(yàn)規(guī)則：

1. mysql> create database yutest2; 
2. Query OK, 1 row affected (0.01 sec) 
3. mysql> use yutest2; 
4. Database changed 
5.  
6. mysql> create table t1(name varchar(10)) 
7. -> default character set utf8 collate utf8_bin; 
8. Query OK, 0 rows affected (0.01 sec) 
9.  
10. mysql> insert into t1 values('ABC'); 
11. Query OK, 1 row affected (0.00 sec) 
12. mysql> insert into t1 values('abc'); 
13. Query OK, 1 row affected (0.00 sec) 
14.  
15. mysql> show variables like 'collation_%'; 
16. +----------------------+-----------------+ 
17. | Variable_name | Value | 
18. +----------------------+-----------------+ 
19. | collation_connection | utf8_general_ci | 
20. | collation_database | utf8_general_ci | 
21. | collation_server | utf8_general_ci | 
22. +----------------------+-----------------+ 
23. 3 rows in set (0.00 sec) 
24.  
25. mysql> select * from t1; 
26. +------+ 
27. | name | 
28. +------+ 
29. | ABC | 
30. | abc | 
31. +------+ 
32. 2 rows in set (0.00 sec) 
33.  
34. mysql> select * from t1 where name='abc'; 
35. +------+ 
36. | name | 
37. +------+ 
38. | abc | 
39. +------+ 
40. 1 row in set (0.00 sec) 

可以看出，在表級(jí)進(jìn)行相應(yīng)的校對(duì)規(guī)則設(shè)置，查詢大小寫敏感。

2.4 連接級(jí)

考慮什么是一個(gè)“連接”：它是連接服務(wù)器時(shí)所作的事情。客戶端發(fā)送SQL語句，例如查詢，通過連接發(fā)送到服務(wù)器。服務(wù)器通過連接發(fā)送響應(yīng)給客戶端，例如結(jié)果集。對(duì)于客戶端連接，這樣會(huì)導(dǎo)致一些關(guān)于連接的字符集和校對(duì)規(guī)則的問題，這些問題均能夠通過系統(tǒng)變量來解決：

1. mysql> show variables like 'character%'; 
2. +--------------------------+----------------------------+ 
3. | Variable_name | Value | 
4. +--------------------------+----------------------------+ 
5. | character_set_client | utf8 | 
6. | character_set_connection | utf8 | 
7. | character_set_database | utf8 | 
8. | character_set_filesystem | binary | 
9. | character_set_results | utf8 | 
10. | character_set_server | utf8 | 
11. | character_set_system | utf8 | 
12. | character_sets_dir | /usr/share/mysql/charsets/ | 
13. +--------------------------+----------------------------+ 
14. 8 rows in set (0.00 sec) 

當(dāng)查詢離開客戶端后，在查詢中使用哪種字符集?

服務(wù)器使用character_set_client變量作為客戶端發(fā)送的查詢中使用的字符集。

服務(wù)器接收到查詢后應(yīng)該轉(zhuǎn)換為哪種字符集?

轉(zhuǎn)換時(shí)，服務(wù)器使用character_set_connection和collation_connection系統(tǒng)變量。它將客戶端發(fā)送的查詢從character_set_client系統(tǒng)變量轉(zhuǎn)換到character_set_connection。

服務(wù)器發(fā)送結(jié)果集或返回錯(cuò)誤信息到客戶端之前應(yīng)該轉(zhuǎn)換為哪種字符集?

character_set_results變量指示服務(wù)器返回查詢結(jié)果到客戶端使用的字符集。包括結(jié)果數(shù)據(jù)，例如列值和結(jié)果元數(shù)據(jù)(如列名)。

3. 創(chuàng)建數(shù)據(jù)庫表時(shí)大小寫不敏感，仍然有方法在查詢時(shí)區(qū)分大小寫

3.1 在SQL語句中使用collate

使用collate子句，能夠?yàn)橐粋€(gè)比較覆蓋任何默認(rèn)校對(duì)規(guī)則。collate可以用于多種SQL語句中，比如where，having，group by，order by，as，聚合函數(shù)。

1. mysql> select * from t1 where name collate utf8_bin = 'ABC'; 
2. +------+ 
3. | name | 
4. +------+ 
5. | ABC | 
6. +------+ 
7. 1 row in set (0.00 sec) 
8.  
9. mysql> select * from t1 where name = 'ABC'; 
10. +------+ 
11. | name | 
12. +------+ 
13. | ABC | 
14. | Abc | 
15. | abc | 
16. +------+ 
17. 3 rows in set (0.00 sec) 
18.  
19. mysql> select * from t1; 
20. +------+ 
21. | name | 
22. +------+ 
23. | ABC | 
24. | Abc | 
25. | abc | 
26. +------+ 
27. 3 rows in set (0.00 sec) 

3.2 binary操作符

binary操作符是collate子句的一個(gè)速記符。binary 'x'等價(jià)與'x' collate y，這里y是字符集'x'二元校對(duì)規(guī)則的名字。每一個(gè)字符集有一個(gè)二元校對(duì)規(guī)則。例如，latin1字符集的二元校對(duì)規(guī)則是latin1_bin，因此，如果列a是字符集latin1，以下兩個(gè)語句有相同效果：

1. select * from t1 order by binary a; 
2.  
3. select * from t1 order by a collate latin1_bin; 
4.  
5. mysql> select * from t1 where binary name = 'ABC'; 
6. +------+ 
7. | name | 
8. +------+ 
9. | ABC | 
10. +------+ 
11. 1 row in set (0.00 sec) 
12. mysql> 
13. mysql> select * from t1 where name = 'ABC'; 
14. +------+ 
15. | name | 
16. +------+ 
17. | ABC | 
18. | Abc | 
19. | abc | 
20. +------+ 
21. 3 rows in set (0.00 sec)