mysql的collation區分大小寫設置 mysql數據庫在做查詢時候，有時候是英文字母大小寫敏感的，有時候又不是的，主要是由於mysql的字符校驗規則的設置。通常默認是不支持的大小寫字母敏感的，在主動設置mysql數據庫的collation後，可以使得數據庫滿足大小寫敏感，適合客戶的一定要求。通過下面的試驗進行理解學習..... Sql代碼 mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.25 | +-----------+ 1 row in set (0.00 sec) Sql代碼 mysql> show variables like '%character%'; +--------------------------+------------------------------------------------------- | Variable_name | Value +--------------------------+------------------------------------------------------- | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | latin1 | character_set_filesystem | binary | character_set_results | utf8 | character_set_server | latin1 | character_set_system | utf8 | character_sets_dir | D:\database\mysql\mysql-5.5.25-winx64\share\charsets\ Sql代碼 mysql> show variables like '%collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) 默認即為：collation_connection = utf8_general_ci 大小寫不敏感校驗規則； Sql代碼 mysql> show collation like '%utf8%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | 客戶端字符集：utf8, 校驗規則: utf8_general_ci, 默認為yes，即不是大小寫敏感的匹配； 而utf8_bin是區分大小寫的校驗規則； 創建表做測試，看數據效果： Sql代碼 mysql> create table T_collation(first varchar(30) character set utf8 -> collate utf8_bin,second varchar(30) character set utf8 collate -> utf8_general_ci); Query OK, 0 rows affected (0.32 sec) mysql> show create table t_collation\G; *************************** 1. row *************************** Table: t_collation Create Table: CREATE TABLE `t_collation` ( `first` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `second` varchar(30) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t_collation values('M','M'),('N','N'),('a','a'),('b','b'); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M | M | | N | N | | a | a | | b | b | +-------+--------+ 4 rows in set (0.00 sec) 比較查詢結果： Sql代碼 mysql> insert into t_collation values('m','m'),('n','n'); Query OK, 2 rows affected (0.10 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M | M | | N | N | | a | a | | b | b | | m | m | | n | n | +-------+--------+ 6 rows in set (0.00 sec) mysql> select * from t_collation where first='m'; +-------+--------+ | first | second | +-------+--------+ | m | m | +-------+--------+ 1 row in set (0.02 sec) mysql> select * from t_collation where second='m'; +-------+--------+ | first | second | +-------+--------+ | M | M | | m | m | +-------+--------+ 2 rows in set (0.00 sec) mysql> select * from t_collation where second='M'; +-------+--------+ | first | second | +-------+--------+ | M | M | | m | m | +-------+--------+ 2 rows in set (0.00 sec) mysql> select * from t_collation where first='M'; +-------+--------+ | first | second | +-------+--------+ | M | M | +-------+--------+ 1 row in set (0.00 sec) 比較各自的校驗規則，utf8_bin是區分大小寫的，而utf8_general_ci是不區分的，默認的。 還可以從排序語句中進行比較，看看測試效果的..... Sql代碼 mysql> select * from t_collation; +-------+--------+ | first | second | +-------+--------+ | M | M | | N | N | | a | a | | b | b | | m | m | | n | n | +-------+--------+ 6 rows in set (0.00 sec) mysql> select * from t_collation order by first; +-------+--------+ | first | second | +-------+--------+ | M | M | | N | N | | a | a | | b | b | | m | m | | n | n | +-------+--------+ 6 rows in set (0.00 sec) mysql> select * from t_collation order by second; +-------+--------+ | first | second | +-------+--------+ | a | a | | b | b | | M | M | | m | m | | N | N | | n | n | +-------+--------+ 6 rows in set (0.00 sec) 同樣符合校驗規則的檢查。 結論： 在MYSQL數據庫中，根據實際業務需要，適當可以調整字符集的(collation)校驗規則，修改默認的大小寫敏感問題，滿足實際需要，這本身就是數據庫的一種設置，熟悉標准、規則，適當利用為項目所用，可以針對具體的數據庫或者表或者表的列進行設置。
