MySQL Select query case-sensitive three methods (1/3)

1, one way is to set the table or row collation, so that it is binary or case sensitive. In the MySQL tutorial, 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
###########

home 1 2 3 last