使MySQL查询区分大小写的实现方法
2022-11-12 09:24:04
内容摘要
这篇文章主要为大家详细介绍了使MySQL查询区分大小写的实现方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！1、一种方法是可以设置表
文章正文
这篇文章主要为大家详细介绍了使MySQL查询区分大小写的实现方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
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: 0mysql> 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: 0mysql> 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: 0mysql> 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查询区分大小写的实现方法的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
