Running a case sensitive query in on a case insensitive table
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: 0mysql [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)
Loading ...
ouvrir
Try this simpler way:
mysql> select * from t1 where a = ‘Darren’ collate latin1_bin;
+——–+
| a |
+——–+
| Darren |
+——–+
1 row in set (0.12 sec)
or you can convert the left side instead:
mysql> select * from t1 where binary a = ‘Darren’
-> ;
+——–+
| a |
+——–+
| Darren |
+——–+
1 row in set (0.00 sec)
Converting the left side is expensive.
Add a bunch more random rows…
mysql> select count(*) from t1;
+———-+
| count(*) |
+———-+
| 393219 |
+———-+
add an index on `a`
mysql> alter table t1 add index (a);
mysql> select * from t1 where a = binary ‘darren’;
+——–+
| a |
+——–+
| darren |
+——–+
1 row in set (0.00 sec)
mysql> select * from t1 where binary a = ‘darren’;
+——–+
| a |
+——–+
| darren |
+——–+
1 row in set (0.21 sec)
If you enable profiling and check this second query you will see it is spending the majority of the time in “Sending data” state while it reads the whole index and converts it to binary.
mysql> explain select * from t1 where binary a = ‘darren’;
+—-+————-+——-+——-+—————+——+———+——+——–+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——+———+——+——–+————————–+
| 1 | SIMPLE | t1 | index | NULL | a | 23 | NULL | 393219 | Using where; Using index |
+—-+————-+——-+——-+—————+——+———+——+——–+————————–+
1 row in set (0.00 sec)
mysql> explain select * from t1 where a = binary ‘darren’;
+—-+————-+——-+——-+—————+——+———+——+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——+———+——+——+————————–+
| 1 | SIMPLE | t1 | range | a | a | 23 | NULL | 3 | Using where; Using index |
+—-+————-+——-+——-+—————+——+———+——+——+————————–+
1 row in set (0.00 sec)
and creating a temporary table and scanning it isn’t expensive..
If you want a hybrid that can use an index:
select * from t1 where a = ‘darren’ and binary a = ‘Darren’