MySQL中查詢的有關英文字母大小寫問題的分析_Mysql

mysql資料庫在做查詢時候，有時候是英文字母大小寫敏感的，有時候又不是的，主要是由mysql的字元校正規則的設定決定的，通常預設是不支援的大小寫字母敏感的。

 1. 什麼是字元集和校正規則？

字元集是一套符號和編碼。校對規則是在字元集內用於比較字元的一套規則。任何一個給定的字元集至少有一個校對規則，它可能有幾個校對規則。要想列出一個字元集的校對規則，使用SHOW COLLATION語句。

校對規則一般有這些特徵：

    兩個不同的字元集不能有相同的校對規則。
    每個字元集有一個預設校對規則。例如，utf8預設校對規則是utf8_general_ci。
    存在校對規則命名規範：它們以其相關的字元集名開始，通常包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結束。

2. 不同層級的字元集和校正規則可控制大小寫敏感

MySQL5.1在同一台伺服器、同一個資料庫或甚至在同一個表中使用不同字元集或校對規則來混合定義字串。字元集和校對規則有4個層級的預設設定：伺服器級、資料庫級、表級和串連級。

2.1伺服器級

MySQL按照如下方法確定伺服器字元集和伺服器校對規則：

(1)修改設定檔/etc/my.cnf

在[mysqld]下添加：collation_server = utf8_bin

重啟執行個體

更改伺服器級的校正規則（collation_server ）後，資料庫校正規則（collation_collation）預設會繼承伺服器級的。

注意：

這個只適用於在重新啟動之後, 建立的庫,已存在的庫不受影響.

同樣的, 即使庫的校正規則改了,已經存在的表不受修改影響;

同理與已經存在的列...

mysql> create database yutest0;Query OK, 1 row affected (0.00 sec)mysql> use yutest0;Database changedmysql> create table t1 (name varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values('AAA');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values('aaa');Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------+| name |+------+| AAA || aaa |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='aaa';+------+| name |+------+| aaa |+------+1 row in set (0.00 sec)

可以看出，在伺服器級進行相應的校對規則設定，查詢大小寫敏感。

(2)當伺服器啟動時根據有效選項設定

當啟動mysqld時，根據使用的初始選項設定來確定伺服器字元集和校對規則。

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

2.2資料庫級

MySQL這樣選擇資料庫字元集和資料庫校對規則：

    如果指定了character set X和collate Y，那麼採用字元集X和校對規則Y。
    如果指定了character set X而沒有指定collate Y，那麼採用character set X和character set X的預設校對規則。
    否則，採用伺服器字元集和伺服器校對規則。

(1)修改設定檔/etc/my.cnf

進行了兩組測試：

1) 在[mysqld]下添加：

collation_server = utf8_bincollation_database = utf8_bin

2) 在[mysqld]下添加：

collation_database = utf8_bin

重啟執行個體，兩組都不能正常啟動，錯誤資訊如下：

可見，my.cnf設定檔中不支援設定collation_database 變數。

（2）建立資料庫時設定資料庫校正規則

mysql> create database yutest default character set utf8 collate utf8_bin;Query OK, 1 row affected (0.00 sec)mysql> show variables like 'collation_%';+----------------------+-----------------+| Variable_name    | Value      |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database  | utf8_bin    || collation_server   | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || abc |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='abc';+------+| name |+------+| abc |+------+1 row in set (0.01 sec)

可以看出，在資料庫級進行相應的校對規則設定，查詢大小寫敏感。

2.3表級

MySQL按照下面的方式選擇表字元集和校對規則：

    如果指定了character set X和collate Y，那麼採用character set X和collate Y。
    如果指定了character set X而沒有指定collate Y，那麼採用character set X和character set X的預設校對規則。
    否則，採用資料庫字元集和伺服器校對規則。

在建立表時設定表級校正規則：

mysql> create database yutest2;Query OK, 1 row affected (0.01 sec)mysql> use yutest2;Database changedmysql> create table t1(name varchar(10))  -> default character set utf8 collate utf8_bin;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values('ABC');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values('abc');Query OK, 1 row affected (0.00 sec)mysql> show variables like 'collation_%';+----------------------+-----------------+| Variable_name    | Value      |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database  | utf8_general_ci || collation_server   | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || abc |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='abc';+------+| name |+------+| abc |+------+1 row in set (0.00 sec)

可以看出，在表級進行相應的校對規則設定，查詢大小寫敏感。

2.4 串連級

考慮什麼是一個“串連”：它是串連伺服器時所作的事情。用戶端發送SQL語句，例如查詢，通過串連發送到伺服器。伺服器通過串連發送響應給用戶端，例如結果集。對於用戶端串連，這樣會導致一些關於串連的字元集和校對規則的問題，這些問題均能夠通過系統變數來解決：

mysql> show variables like 'character%';+--------------------------+----------------------------+| Variable_name      | Value           |+--------------------------+----------------------------+| character_set_client   | utf8            || character_set_connection | utf8            || character_set_database  | utf8            || character_set_filesystem | binary           || character_set_results  | utf8            || character_set_server   | utf8            || character_set_system   | utf8            || character_sets_dir    | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

    當查詢離開用戶端後，在查詢中使用哪種字元集？

伺服器使用character_set_client變數作為用戶端發送的查詢中使用的字元集。

    伺服器接收到查詢後應該轉換為哪種字元集？

轉換時，伺服器使用character_set_connection和collation_connection系統變數。它將用戶端發送的查詢從character_set_client系統變數轉換到character_set_connection。

    伺服器發送結果集或返回錯誤資訊到用戶端之前應該轉換為哪種字元集？

character_set_results變數指示伺服器返回查詢結果到用戶端使用的字元集。包括結果資料，例如列值和結果中繼資料（如列名）。

3. 建立資料庫表時大小寫不敏感，仍然有方法在查詢時區分大小寫

3.1 在SQL語句中使用collate

使用collate子句，能夠為一個比較覆蓋任何預設校對規則。collate可以用於多種SQL語句中，比如where，having，group by，order by，as，彙總函式。

mysql> select * from t1 where name collate utf8_bin = 'ABC';+------+| name |+------+| ABC |+------+1 row in set (0.00 sec)mysql> select * from t1 where name = 'ABC';+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)

3.2 binary操作符

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

select * from t1 order by binary a;select * from t1 order by a collate latin1_bin;mysql> select * from t1 where binary name = 'ABC';+------+| name |+------+| ABC |+------+1 row in set (0.00 sec)mysql>mysql> select * from t1 where name = 'ABC';+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)