Mysql区分大小写（大小写敏感）的问题总结

MySQL默认是不区分大小写的，但是在很多情况下需要大小敏感，以下总结了多种设置MySQL大小写敏感的方法。

方法一：

修改MySql Server安装目录下的 my.ini 文件，在mysqld节下加入下面一行 set-variable=lower_case_table_names=0 （0：大小写敏感；1：大小写不敏感）最后重启一下MySql服务即可。

方法二：

一种方法是可以设置表或行的collation，使其为binary或case sensitive。在MySQL中，对于Column Collate其约定的命名方法如下： 

*_bin: 表示的是binary case sensitive collation，也就是说是区分大小写的 
*_cs: case sensitive collation，区分大小写 
*_ci: case insensitive collation，不区分大小写 


########### 
# Start binary collation example 
########### 
mysql> create table case_bin_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_bin; 
Query OK, 0 rows affected (0.02 sec) 

mysql> INSERT INTO case_bin_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 5 rows affected (0.00 sec) 
Records: 5 Duplicates: 0 Warnings: 0 

mysql> SELECT * FROM case_bin_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 

mysql> SELECT * FROM case_bin_test WHERE word LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.00 sec) 

########### 
# End 
########### 

方法三：

########### 
# Start case sensitive collation example 
########### 

mysql> create table case_cs_test (word VARCHAR(10)) CHARACTER SET latin1 COLLATE latin1_general_cs; 
Query OK, 0 rows affected (0.08 sec) 

mysql> INSERT INTO case_cs_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 5 rows affected (0.00 sec) 
Records: 5 Duplicates: 0 Warnings: 0 

mysql> SELECT * FROM case_cs_test WHERE word LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.00 sec) 

mysql> SELECT * FROM case_cs_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 

########### 
# end 
########### 


方法四：就是在查询时指定collation 

mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1; 
Query OK, 0 rows affected (0.01 sec) 

mysql> INSERT INTO case_test VALUES ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr'); 
Query OK, 7 rows affected (0.01 sec) 
Records: 7 Duplicates: 0 Warnings: 0 
mysql> SELECT * FROM case_test WHERE word LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| froogle | 
| flickr | 
| FlicKr | 
+---------+ 
6 rows in set (0.01 sec) 

mysql> SELECT * FROM case_test WHERE word LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| froogle | 
| flickr | 
| FlicKr | 
+---------+ 
6 rows in set (0.01 sec) 


mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'F%'; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.05 sec) 

mysql> SELECT * FROM case_test WHERE word COLLATE latin1_bin LIKE 'f%'; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 



mysql> SELECT * FROM case_test WHERE word LIKE 'f%' COLLATE latin1_bin; 
+---------+ 
| word | 
+---------+ 
| froogle | 
| flickr | 
+---------+ 
2 rows in set (0.00 sec) 

mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_bin; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.01 sec) 


mysql> SELECT * FROM case_test WHERE word LIKE 'F%' COLLATE latin1_general_cs; 
+---------+ 
| word | 
+---------+ 
| Frank | 
| FlicKr | 
+---------+ 
4 rows in set (0.04 sec)

 方法五：

要让mysql查询区分大小写，可以： 

1. select  * from  table_name where  binary  a like  'a%'   
2. select  * from  table_name where  binary  a like  'A%'   

 

也可以在建表时，加以标识 

create  table  table_name(  

     a varchar (20) binary

)