2

I'd like to change an existing MySQL column from VARCHAR to VARCHAR BINARY in order to change it to be case-sensitive (see: https://stackoverflow.com/a/3396315/93995)

What's the proper migration syntax for this? I tried

change_column :my_table, :my_column, :binary, :limit => 255

but that tries to change it to a blob(255). Also complains:

Mysql2::Error: BLOB/TEXT column 'my_column' used in key specification without a key length:   ALTER TABLE `my_table` CHANGE `my_column` `my_column` blob(255) DEFAULT NULL
Community
  • 1
  • 1
George Armhold
  • 30,824
  • 50
  • 153
  • 232
  • You need to specify the 'utf8_bin' collation which isn't supported by rails migrations. Do you want to change only that column, or can all columns in the table be case sensitive? – PinnyM Jan 09 '13 at 20:08
  • I want to change only a specify column. – George Armhold Jan 09 '13 at 20:10

1 Answers1

4

To change a single column to be case sensitive, you'll need to use SQL DDL directly to change to the collation to 'utf8_bin':

def up
 execute("ALTER TABLE my_table MODIFY `my_column` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin;")
end

Note that this is still using the 'varchar' type as storage, only the collation (interpreted meaning) is defined as 'binary' for comparison purposes. Also note that you may not be able to compare columns with different collations, so make sure this is what you really want.

If you wanted to make a whole table use a specific collation, that can easily be done:

create_table :my_table, :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin' do |t|
  t.string :my_column
  t.string :my_other_column
end
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • 1
    I used this solution with the execute command. But one thing is worrying me. When I look at the schema file it says nothing about the collation for this changed column. What happens if i was about to create the database from scratch with a rake db:schema:load instead of doing the migrations. Then this change would not be applied would it? – Jepzen Aug 01 '14 at 07:03
  • @Jepzen Correct, ActiveRecord does not properly support column-level collation settings :(. Which is interesting, because there _is_ an attribute that [holds the specified collation](http://apidock.com/rails/v4.0.2/ActiveRecord/ConnectionAdapters/AbstractMysqlAdapter/Column/new/class). Note that you won't have this problem if you switch to using [the :sql dump](http://guides.rubyonrails.org/migrations.html#types-of-schema-dumps) format. – PinnyM Aug 01 '14 at 16:23