How to make MySQL Query case sensitive _ MySQL

How to make MySQL Query case sensitive: bitsCN.com 1. you can set collation for a table or row to binary or case sensitive. In MySQL, the naming conventions for Column Collate are as follows:

* _ Bin: binary case sensitive collation, that is, case sensitive.
* _ Cs: case sensitive collation, case sensitive
* _ Ci: case insensitive collation, case insensitive


###########
# 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 '), ('froog'), ('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. another method

###########
# 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 '), ('froog'), ('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. you can specify collation during the query.

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 '), ('froog'), ('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) bitsCN.com