MySQL COLLATE and migration

Hello,

I've set my local dev MySQL server instance to default to utf8 and the utf8_general_ci collation as this appears to be the best defaults for my purposes. However, there are a couple of columns in a couple of tables that I would like to be case sensitive when searched. I'd like to specify this in my migration.

I'm guessing something like:

def self.up
  create_table :table_name do |t|
    t.string :field_name, :null => false, :options => 'COLLATE utf8_general_cs'
    ...
  end
end

So my questions are: is it utf8_general_cs or is it utf8_bin or something else that I need for case sensitivity?

And assuming I get the collation right, what is the syntax I need to use for the migration because I've tried both utf8_bin and utf8_general after COLLATE and the :options argument just seems to be ignored.

UPDATE: It appears that MySQL does not have utf8_general_cs and a proposed solution is to use utf8_bin instead.

I've been able to get utf8_bin set on the one column with:

def self.up
  create_table :table_name do |t|
    t.string :field_name, :null => false
    ...
  end
  execute %{ALTER TABLE table_name MODIFY field_name varchar(255) COLLATE utf8_bin NOT NULL}
end

If anyone can shed a bit more light on what is a good way to go re: MySQL and case sensitive UTF8 or provide a better way of doing this in a migration, I'd be interested to hear.

Last edited by jonny_noog (2008-10-20 06:15:58)