Feb 11, 2009

MySQL Isn't Very Sensitive

When writing SQL conditions in MySQL, using an equal sign is case insensitive. For example:

Will return results where thread_id actually equals 2a and 2A. To make this case sensitive, you can use LIKE BINARY:

But what happens when you want to use IN? For example:

This would return where thread_id actually equals 2a, 2A, 2b, 2B, 2c and 2C. This is no good because I need it to be case sensitive. You could do something like:

SELECT * FROM comments 
  WHERE thread_id LIKE BINARY '2A' 
  OR thread_id LIKE BINARY '2B' 
  OR thread_id LIKE BINARY '2C';

But this is a problem when you want dynamically built this query using an ActiveRecord find method like:

I haven’t found a solution yet, but I hope to soon.