首页 > 代码库 > MySQL查询时区分大小写

MySQL查询时区分大小写

2024-08-09 17:09:26   220人阅读

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)

 

 

要让mysql查询区分大小写，可以： 

  1. select  * from  table_name where  binary  a like  ‘a%‘   
  2. select  * from  table_name where  binary  a like  ‘A%‘   
 

注：这样查询非常慢，不建议使用

也可以在建表时，加以标识 

create  table  table_name(  

     a varchar (20) binary

) 

MySQL查询时区分大小写


联系
我们