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

这篇文章主要为大家详细介绍了使MySQL查询区分大小写的实现方法，具有一定的参考价值，可以用来参考一下。

感兴趣的小伙伴，下面一起跟随四海网的小编小韵来看看吧！

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)

本文来自：http://www.q1010.com/177/10543-0.html

注：关于使MySQL查询区分大小写的实现方法的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：MYSQL