Mysql大小寫敏感

標籤：

Author：Skate
Time:2015/03/11

Mysql大小寫敏感

字串大小寫敏感和Mysql的資料庫的名字、表名字、欄位名字、還有欄位值有關。

1.和資料庫名字、表名字、預存程序和觸發器有關
Mysql中控制資料庫名和表名的大小寫敏感是由參數lower_case_table_names控制，
為0時；
表示區分大小寫，使用CREATE TABLE或CREATE DATABASE語句指定的大小寫字母在硬碟上儲存表名和資料庫名。
名稱比較對大小寫敏感。在大小寫不敏感的作業系統如windows或Mac OS x上我們不能將該參數設為0，如果在
大小寫不敏感的檔案系統上將--lowercase-table-names強制設為0，並且使用不同的大小寫訪問MyISAM表名，
可能會導致索引破壞。

為1時；
表示將名字轉化為小寫後儲存，名稱比較對大小寫不敏感。MySQL將所有表名轉換為小寫在儲存和尋找表上。
該行為也適合資料庫名和表的別名。該值為Windows的預設值。

為2時；
表名和資料庫名在硬碟上使用CREATE TABLE或CREATE DATABASE語句指定的大小寫字母進行儲存，但MySQL將它們
轉換為小寫在尋找表上。名稱比較對大小寫不敏感，即按照大小寫來儲存，按照小寫來比較。注釋：只在對大小
寫不敏感的檔案系統上使用! innodb表名用小寫儲存。

如果你使用innodb表，為了避免避免大小寫敏感問題，可以把lower_case_table_names=1

把lower_case_table_names從0改變為1
在你把lower_case_table_names設定為1時，在restart你的mysqld之前，請把資料庫名和表名更改為小寫
 mysql> RENAME TABLE T1 TO t1;

2.和欄位名字有關
欄位名是不區分大小寫

3.和欄位值有關
欄位值的大小寫由Mysql的collate來控制。提到collate，就不得不說字元集。字元集是一套符號和編碼，collate
是在字元集內用於比較字元的一套規則，比如定義‘A‘<‘B‘這樣的關係的規則。不同的字元集有多種校對規則，一般
而言，collate以其相關的字元集名開始，通常包括一個語言名，並且以_ci（大小寫不敏感）、_cs（大小寫敏感）
或_bin（二元）結束 。

比如 utf8字元集：
utf8_general_ci,表示不區分大小寫，這個是utf8字元集預設的collate；
utf8_general_cs表示區分大小寫，
utf8_bin表示二進位比較，同樣也區分大小寫。

查看資料庫的字元集相關資訊
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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

查看資料庫collation相關資訊
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.01 sec)

test是預設建立的資料庫
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

testx是指定字元集和collation的資料庫
mysql> create database testx default character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)

mysql> show create database testx;
+----------+---------------------------------------------------------------------------------+
| Database | Create Database                                                                 |
+----------+---------------------------------------------------------------------------------+
| testx    | CREATE DATABASE `testx` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

case2是預設建立的表
mysql> show create table case2;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| case2 | CREATE TABLE `case2` (
  `a` int(11) DEFAULT NULL,
  `B` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

case4是指定字元集和collate的表
mysql> create table case4(a int(11),B varchar(10)) default character set utf8 collate utf8_bin;;
Query OK, 0 rows affected (0.12 sec)

mysql> show create table case4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                             |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| case4 | CREATE TABLE `case4` (
  `a` int(11) DEFAULT NULL,
  `B` varchar(10) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我們可以在建立資料庫或表時指定是否大小寫敏感，如果沒有指定的話，通過語句級的collate和binary也可以實現

mysql> select * from case1;
+------+------+
| a    | B    |
+------+------+
|    1 | AAA  |
|    2 | bbb  |
|    4 | AAA  |
|    3 | BBB  |
+------+------+
4 rows in set (0.00 sec)

mysql> select * from case1 where B like ‘%b%‘;
+------+------+
| a    | B    |
+------+------+
|    2 | bbb  |
|    3 | BBB  |
+------+------+
2 rows in set (0.00 sec)

指定Collation
mysql> select * from case1 where B like ‘%b%‘ collate utf8_bin;
+------+------+
| a    | B    |
+------+------+
|    2 | bbb  |
+------+------+
1 row in set (0.01 sec)

通過binary把字串轉化為二進位比較，由於大小寫字元的二進位肯定不同，因此也是區分大小的一種方式
mysql> select * from case1 where binary B like ‘%b%‘;
+------+------+
| a    | B    |
+------+------+
|    2 | bbb  |
+------+------+
1 row in set (0.00 sec)

mysql>

最後要說明一點的是Collation與索引儲存的關係。因為Collation是用於字串之間比較，而索引是基於比較有序排列的，
因此Collation會影響記錄的索引順序

參考：
http://dev.mysql.com/doc/refman/5.0/en/charset-collation-names.html
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Mysql大小寫敏感