mysql查询时区分大小写的方法

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%'    

综上：区分大小写有两种基本方法：

1、collate latin1_bin

2、binary