MySQL Preacher

A colleague at work asked me “how can I run a case sensitive select on a case insensitive table?” out of curiosity and for a moment I hesitated, then said, yeah why not :) ….

Below are two different approaches (one of which is quite inefficient) and if anyone has another way, better or worse, please do leave a comment with your suggested approach :).

Cheers,
Darren

Preparation


mysql [localhost] {root} (test) > create table t1(a varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('Darren');
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t1 (a) values ('DarRen');
Query OK, 1 row affected (0.00 sec)


Method 1

mysql [localhost] {root} (test) > select * from t1 where a ='darren';
+--------+
| a |
+--------+
| darren |
| Darren |
| DarRen |
+--------+
3 rows in set (0.00 sec)

mysql [localhost] {root} (test) > create temporary table t3(a varchar(20)) Engine=MyISAM , COLLATE latin1_general_cs;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} (test) > insert into t3 select * from t1;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql [localhost] {root} (test) > select * from t3 where a ='darren';
+--------+
| a |
+--------+
| darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t3 where a ='Darren';
+--------+
| a |
+--------+
| Darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t3 where a ='DarreN';
Empty set (0.00 sec)

Method 2

mysql [localhost] {root} (test) > select * from t1 where CONVERT(a USING latin1) COLLATE latin1_general_cs ='darren';
+--------+
| a |
+--------+
| darren |
+--------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > select * from t1 where CONVERT(a USING latin1) COLLATE latin1_general_cs ='Darren';
+--------+
| a |
+--------+
| Darren |
+--------+
1 row in set (0.00 sec)

MySQL case insensitive, case sensitivity, collation, select, temporary table