ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使MySQL查询区分大小写(转)

使MySQL查询区分大小写(转)

原创 Linux操作系统 作者:yantaicuiwei 时间:2012-05-09 15:24:51 0 删除 编辑
我们在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)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21601207/viewspace-723135/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-15

  • 博文量
    64
  • 访问量
    234989