How to make Mysql query case-sensitive implementation _mysql

1, one way is to set the table or row collation, so that it is binary or case sensitive. In MySQL, the naming method for column collate its conventions is as follows:

*_bin: Binary case sensitive collation, which means 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) 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 is like ' f% ';
+---------+
| Word |
+---------+
| Froogle |
| Flickr |
+---------+
2 rows in Set (0.00 sec)

Mysql> SELECT * from Case_bin_test WHERE word is 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) 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 is like ' f% ';
+---------+
| Word |
+---------+
| Frank |
| FlicKr |
+---------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from Case_cs_test WHERE word is like ' f% ';
+---------+
| Word |
+---------+
| Froogle |
| Flickr |
+---------+
2 rows in Set (0.00 sec)

###########
# End
###########


3, there is another way is to specify collation in the query

Mysql> CREATE TABLE Case_test (Word VARCHAR) 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 is like ' f% ';
+---------+
| Word |
+---------+
| Frank |
| Froogle |
| Flickr |
| FlicKr |
+---------+
6 rows in Set (0.01 sec)

Mysql> SELECT * from Case_test WHERE word is 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)