Case Sensitive MySQL

Today I was asked an interesting question: "Can we make MySQL to be case sensitive for SELECT queries and can we force a column in MySQL to be always lowercase?"

My response was that yes, we can have "instruct" MySQL to be case sensitive. One way to do that is to set the collation for the table (or column) to be either binary or case sensitive as shown below.

The naming convention for collation in mysql is as follows:

*_bin: represents 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
###########

Here's another way

###########
# 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
###########

Yet another way is to specify the collation during query in case the collation cannot be specified for the entire table. Following are a few different ways of specifying this.

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)

Now regarding "forcing" a column to always be lower case.

Unfortunately, we cannot do that in MySQL 4.x, other than converting a value to lower case before inserting at the application side.

Starting with MySQL 5, however we can use triggers as the following example shows:

mysql> create table case_test (word VARCHAR(10)) CHARACTER SET latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER //
mysql> CREATE TRIGGER lowercased BEFORE INSERT ON case_test FOR EACH ROW BEGIN   SET NEW.word=LOWER(NEW.word);
END;//
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO case_test VALUES  ('Frank'),('Google'),('froogle'),('flickr'),('FlicKr');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM case_test;
+---------+
| word    |
+---------+
| frank   |
| google  |
| froogle |
| flickr  |
| flickr  |
+---------+
7 rows in set (0.00 sec)

I am interested in hearing about other approaches to achieving the same results.