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)

要让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

)

来源:http://www.cnblogs.com/pinnasky/archive/2012/09/11/2680264.html