Rails の database.yml で collation を指定する

MySQL

定期的に引っかかるので備忘録として。検証には Rails 4.0.8 及び Mysql 5.6.10 を使用しました。

Rails から MySQL を使う際、何も考えずにデフォルトのまま設定していくと、大体の場合小文字 a と大文字 A が区別されないデータベースが作成されます。具体的にサンプルを示すと

SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+

このように 'a' = 'A' が真となってしまいます。主にメールアドレスなどの一貫性を保証したい時に問題になります。

こうした問題を解消するために使われるのが collation の指定です。バージョン 5.1 の記事ですが、公式ドキュメントはこちら。

http://dev.mysql.com/doc/refman/5.1/ja/charset-unicode-sets.html

詳しい解説は省きますが、今回は utf8_bin を collation に指定できれば問題が解決します。

新規作成する場合

config/database.yml に記述を追加します。

development:
  adapter: mysql2
  encoding: utf8
  collation: utf8_bin
  reconnect: false
  database: some_project_dev
  username: username
  password: password
  host: localhost

後は普段のように rake db:create からスタートすれば問題ありません。

既存のテーブルを何とかする場合

既存のデータベースについて設定を変更するには MySQL のクエリを直接発行する必要があります。

ALTER DATABASE dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

しかし、この設定が適用されるのは上記クエリの受付後に作られたテーブル等のみで、既存のデータについてはどうしても区別ができないようです。そのような場合、明示的に BINARY オペレータを用いる事で比較を行います。

SELECT BINARY 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         0 |
+-----------+

ただし、この方法はパフォーマンス的にあまりオススメされないらしいので、必要なのであれば極力最初から大文字小文字（あるいはその他の何か）が区別可能なデータベースを作成するようにした方がいいでしょう。