Case Sensitive Comparisons in MySQL Where Clause

How can I make MySQL treat my query as case sensitive?

As with all good tools there’s more than one way to get the job done. Assuming you have access to change the structure of the table you have to think about the way you want to access the data by default. Is this something like a password that will always be case sensitive? (if it actually is a password, we should talk about not storing your passwords in plain text sometime.) Or is this something like a name that usually would be case insensitive but for whatever reason you want to make a case sensitive comparison?

Change the data to case sensitive permanently

Your first option is to alter the column to be case sensitive. Or in MySQL terms change the collation (or character set) to a case sensitive or “Binary” setting. Assuming the column is UTF8 you could do something like…

1
2
3
ALTER TABLE `MyTable` CHANGE `MyColumn`
  `MyColumn` VARCHAR( 3 ) BINARY
  CHARACTER SET utf8 COLLATE utf8_general_ci NULL;

Of course there are a variety of reasons this might be something you don’t want to make system wide. Not the least of which is the fact that a change like that would require a pretty extensive regression test in an existing system to make sure you don’t break something that was counting on the case insensitivity of that column.

One time case sensitive comparison in MySQL

The alternative is to just convert the column to binary inline. This will allow you to treat the row (or in the case of this example, the string) as case sensitive without altering the table structure.

1
2
3
4
5
6
7
8
9
10
mysql> SELECT
    ->     'a' = 'A' AS NormalCIEqual,
    ->     'a' = BINARY 'A' AS BinaryCS,
    ->     'a' = BINARY 'a' AS BinarySame;
+---------------+----------+------------+
| NormalCIEqual | BinaryCS | BinarySame |
+---------------+----------+------------+
|             1 |        0 |          1 |
+---------------+----------+------------+
1 ROW IN SET (0.00 sec)

This will also work with other types of comparisons…

1
2
3
4
5
6
7
8
9
mysql> SELECT
    ->     'a' LIKE 'A' AS NormalCILike,
    ->     'a' LIKE BINARY 'A' AS BinaryCS,
    ->     'a' LIKE BINARY 'a' AS BinarySame;

mysql> SELECT
    ->     'a' REGEXP 'A' AS NormalCIRegex,
    ->     'a' REGEXP BINARY 'A' AS BinaryCS,
    ->     'a' REGEXP BINARY 'a' AS BinarySame;
Category(s): Code Snippets, Database, MySQL, SQL
Tags: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

 

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Spam protection by WP Captcha-Free