taoCMS是基于php+sqlite/mysql的国内最小(100Kb左右)的功能完善的CMS管理系统

使MySQL查询区分大小写的实现方法

2014-09-26
被坑了…记录下。。。
我们在MySQL中使用SELECT语句查询时,可不可以使查询区分大小写?今天从网络上找到了方法,现总结如下。
1、一种方法是可以设置表或行的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 
########### 

2、另外一种方法 

########### 
# 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 
########### 


3、还有一种方法就是在查询时指定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)

类别:未分组 | 阅读:59103 | 评论:0 | 标签:mysql

想收藏或者和大家分享这篇好文章→

“使MySQL查询区分大小写的实现方法”共有0条留言

发表评论

姓名:

邮箱:

网址:

验证码:

公告

taoCMS发布taoCMS2.5Beta5(最后更新14年05月25日),请大家速速升级,欢迎大家试用和提出您宝贵的意见建议。

捐助与联系

☟请使用新浪微博联系我☟

☟在github上follow我☟

标签云

友情链接