Rails + MySQL: Case-Sensitive strings in your database

| Posted: - Last updated:
| Tagged ruby on rails
| ~200wrds (~1min)

When using Rails + MySQL, you’ll find that normal string (or varchar(255)) fields are case insensitive. This can be quite a nuisance, but it’s easy to resolve. You need to set your table to the utf8_bin collation. By using the binary variant, you’re basically enabling case sensitivity.

create_table :posts, :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin' do |t|
  t.string :title, :limit => 100

That’s all. The title field is now case sensitive.

Another question I get a lot is how to change the collation and charset for an existing column. That’s easy with the following query. Just make sure to pick the right column and data type:

ALTER TABLE posts MODIFY `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin;

My site is free of ads and trackers. I record privacy-respecting usage statistics with Fathom.

Was this post helpful to you? Why not ☕ Buy me a coffee or use Brave Browser to send a tip.