Rails でユニーク制約を行うためには、モデルに validates_uniqueness_of を設定して、スキーマでユニークインデックスを設定しておくという話を書きました(https://tmtms.hatenablog.com/entry/20120602/rails_unique)。
が、それだけでは十分ではありませんでした。また軽くハマったのでメモしておきます。
ユニーク項目の値が既存かどうかを調べるためのクエリは次のようになっていました。
SELECT 1 FROM `users` WHERE `users`.`login` = BINARY 'hoge' LIMIT 1
よく見ると BINARY というキーワードが指定されています。これは大文字小文字を区別して比較するという指定です。
ところが MySQL はデフォルトでは大文字小文字を区別しません。
モデルは大文字小文字を区別してチェックし、データベースは大文字小文字を区別しないので、大文字小文字が異なるだけの同じ文字列を指定した時にデータベースエラーになってアプリが落ちてしまいます。
最初にデータを作ります。
irb(main):001:0> User.create(login:'hoge') (0.2ms) BEGIN User Exists (0.4ms) SELECT 1 FROM `users` WHERE `users`.`login` = BINARY 'hoge' LIMIT 1 SQL (0.6ms) INSERT INTO `users` (`created_at`, `login`, `updated_at`) VALUES ('2012-06-08 13:34:56', 'hoge', '2012-06-08 13:34:56') (69.5ms) COMMIT => #<User id: 5, login: "hoge", created_at: "2012-06-08 13:34:56", updated_at: "2012-06-08 13:34:56">
大文字小文字が異なる文字列でデータを作ってみます。
irb(main):002:0> User.create(login:'HOGE') (0.4ms) BEGIN User Exists (0.6ms) SELECT 1 FROM `users` WHERE `users`.`login` = BINARY 'HOGE' LIMIT 1 SQL (0.9ms) INSERT INTO `users` (`created_at`, `login`, `updated_at`) VALUES ('2012-06-08 13:35:01', 'HOGE', '2012-06-08 13:35:01') Mysql2::Error: Duplicate entry 'HOGE' for key 'index_users_on_login': INSERT INTO `users` (`created_at`, `login`, `updated_at`) VALUES ('2012-06-08 13:35:01', 'HOGE', '2012-06-08 13:35:01') (145.4ms) ROLLBACK ActiveRecord::RecordNotUnique: Mysql2::Error: Duplicate entry 'HOGE' for key 'index_users_on_login': INSERT INTO `users` (`created_at`, `login`, `updated_at`) VALUES ('2012-06-08 13:35:01', 'HOGE', '2012-06-08 13:35:01')
SELECT でのチェックは通過してデータベースに INSERT しようとしてエラーになってしまいます。
モデルの validates_uniqueness_of のデフォルト動作は大文字小文字を区別するので、それを区別しないようにすれば期待通りに動きます。
validates_uniqueness_of :login, case_sensitive: false
irb(main):001:0> User.create(login: 'HOGE') (0.3ms) BEGIN User Exists (0.7ms) SELECT 1 FROM `users` WHERE `users`.`login` = 'HOGE' LIMIT 1 (0.2ms) ROLLBACK => #<User id: nil, login: "HOGE", created_at: nil, updated_at: nil>
SELECT から BINARY がなくなって、大文字でもちゃんとレコードが見つかったようです。
BINARY には意味がない
ところで、データベース自体が大文字小文字を区別するように設定してある場合でも、BINARY をつける意味はありません。データベースが区別してくれるんだから小細工する必要はないのです。
というか、本来はデータベースがユニーク制約つけているんだから、その前に SELECT でわざわざチェックする必要もないはずです。
これらから考えると Rails が想定しているデータベースは、
- 大文字小文字を区別できない。
- ユニーク制約を持てない。
…というものではないかと思います。まあ、あまり普通じゃないですね。
また、WHERE login = BINARY 'hoge' という条件はインデックスの使用効率が悪いです。EXPLAIN でインデックスの使われ方を見てみます。
mysql> EXPLAIN SELECT * FROM users WHERE login='hoge'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: const possible_keys: index_users_on_login key: index_users_on_login key_len: 768 ref: const rows: 1 Extra:
mysql> EXPLAIN SELECT * FROM users WHERE login=BINARY 'hoge'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: range possible_keys: index_users_on_login key: index_users_on_login key_len: 768 ref: NULL rows: 1 Extra: Using where
BINARY がない場合は const なのに BINARY を指定した場合は range になってしまっています。
ということで、データベース的に効率が良いのは、Rails の validates_uniqueness_of 制約を使わずに、データベース登録時に発生する重複エラーを拾うことなのですが、この方法だと Rails の支援がないので、アプリが自前で頑張らないといけないのがイマイチなところです。
