快捷導(dǎo)航

MySQL中查詢的有關(guān)英文字母大小寫問題的分析

更新時間：2015年05月07日 10:39:13 投稿：goldensun

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

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

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

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

201557101023879.png (620×262)

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

    兩個不同的字符集不能有相同的校對規(guī)則。
    每個字符集有一個默認(rèn)校對規(guī)則。例如，utf8默認(rèn)校對規(guī)則是utf8_general_ci。
    存在校對規(guī)則命名約定：它們以其相關(guān)的字符集名開始，通常包括一個語言名，并且以_ci（大小寫不敏感）、_cs（大小寫敏感）或_bin（二元）結(jié)束。

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

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

2.1服務(wù)器級

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

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

在[mysqld]下添加：collation_server = utf8_bin

重啟實例

201557101048006.png (405×125)

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

注意：

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

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

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

mysql> create database yutest0;
Query OK, 1 row affected (0.00 sec)
mysql> use yutest0;
Database changed
mysql> 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)

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

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

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

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

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

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

    如果指定了character set X和collate Y，那么采用字符集X和校對規(guī)則Y。
    如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認(rèn)校對規(guī)則。
    否則，采用服務(wù)器字符集和服務(wù)器校對規(guī)則。

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

進行了兩組測試：

1) 在[mysqld]下添加：

collation_server = utf8_bin

collation_database = utf8_bin

2) 在[mysqld]下添加：

collation_database = utf8_bin

重啟實例，兩組都不能正常啟動，錯誤信息如下：

201557101355011.png (772×63)

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

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

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)

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

2.3表級

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

    如果指定了character set X和collate Y，那么采用character set X和collate Y。
    如果指定了character set X而沒有指定collate Y，那么采用character set X和character set X的默認(rèn)校對規(guī)則。
    否則，采用數(shù)據(jù)庫字符集和服務(wù)器校對規(guī)則。

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

mysql> create database yutest2;
Query OK, 1 row affected (0.01 sec)
mysql> use yutest2;
Database changed

mysql> 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)

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

2.4 連接級

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

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)

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

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

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

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

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

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

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

3.1 在SQL語句中使用collate

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

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'二元校對規(guī)則的名字。每一個字符集有一個二元校對規(guī)則。例如，latin1字符集的二元校對規(guī)則是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)

您可能感興趣的文章: