Rails/MySQL surprise of the day: WHERE clauses on VARCHARs are not case sensitive by default

Ran into a totally from-left field bug today, and was surprised to find that WHERE clauses against VARCHAR fields are totally case insensitive. So if you have two rows in table FOO that have values "Bar" and "bar", and you run SELECT * FROM Foo WHERE FIELD = 'Bar' -- you will actually get BOTH 'Bar' and 'bar' rows returned.

Now there are ways in which you can set a particular column to always be treated in a case sensitive manner.

But the simple/braindead fix to make your query case sensitive is to use this instead: SELECT * FROM FOO WHERE FIELD LIKE BINARY 'Bar'  -- running that will cause case sensitive behavior to occur and only one row to be returned.

And knowing is half the battle.