使MySQL查询区分大小写的实现方法
程序员文章站
2023-12-13 08:50:28
1、一种方法是可以设置表或行的collation,使其为binary或case sensitive。在mysql中,对于column collate其约定的命名方法如下:...
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)
*_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)