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)