使MySQL查询区分大小写

最新推荐文章于 2025-04-21 15:33:44 发布

yaogao000

最新推荐文章于 2025-04-21 15:33:44 发布

阅读量594

点赞数

本文介绍了在MySQL中实现查询结果区分大小写的方法，包括设置表或行的collation为binary或casesensitive，以及在查询时指定collation。通过实例演示了如何在不同collation下查询结果的差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使MySQL查询区分大小写

转自：http://www.itlearner.com/article/4748

我们在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)