1. kamipo

    Posted

    kamipo
Changes in title
+ActiveRecordでデフォルトの照合順序を変更する
Changes in tags
+Rails
3.2.8
+MySQL
5.6.8
Changes in body
Source | HTML | Preview
@@ -0,0 +1,105 @@
+
+MySQLには文字列の照合順序(collation)というのがあって、MySQL側でのcharset utf8のときのデフォルトの照合順序はutf8_general_ciです。
+
+ActiveRecord::Migrationでは明示的に照合順序を指定しない場合、charset utf8で照合順序utf8_unicode_ciのデータベースを作成しますが、これは少なくとも日本語圏では多くの人が期待する挙動ではないと思われるので注意が必要です。
+
+たとえば、以下のようなファミリーテーブルをrake db:migrateすると
+
+```ruby:db/migrate/20121112110702_create_families.rb
+# coding: utf-8
+class CreateFamilies < ActiveRecord::Migration
+ def change
+ create_table :families do |t|
+ t.string :name
+ t.string :relationship
+ end
+ # add_index :families, :relationship, unique: true
+ Family.create(name: 'ユイ', relationship: '本人')
+ Family.create(name: 'キリト', relationship: 'パパ')
+ Family.create(name: 'アスナ', relationship: 'ハハ')
+ end
+end
+```
+
+utf8_unicode_ciなテーブルが作成されます。
+
+```sql
+CREATE TABLE `families` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
+ `relationship` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+```
+
+```sql
+SELECT * FROM families;
++----+-----------+--------------+
+| id | name | relationship |
++----+-----------+--------------+
+| 1 | ユイ | 本人 |
+| 2 | キリト | パパ |
+| 3 | アスナ | ハハ |
++----+-----------+--------------+
+```
+
+ここで'パパ'を検索すると、utf8_unicode_ciでは'ハ'と'パ'は等価と扱われるので'ハハ'も検索に引っかかります。この挙動を期待するケースはあまり多くないのではないでしょうか。
+
+```sql
+SELECT * FROM families WHERE relationship = 'パパ';
++----+-----------+--------------+
+| id | name | relationship |
++----+-----------+--------------+
+| 2 | キリト | パパ |
+| 3 | アスナ | ハハ |
++----+-----------+--------------+
+```
+
+また、relationshipカラムにUNIQUEインデックスを張ってあると'パパ'と'ハハ'がUNIQUE制約を満たせないので以下のようにエラーになります。
+
+```
+Mysql2::Error: Duplicate entry 'ハハ' for key 'relationship': INSERT INTO `families` (`name`, `relationship`) VALUES ('アスナ', 'ハハ')
+```
+
+ということで、照合順序utf8_general_ciなデータベースを作成したいわけですが、これはコンフィグで`charset`と`collation`を指定するだけで簡単にいけます。
+
+```yaml:config/database.yml
+development:
+ adapter: mysql2
+ encoding: utf8
+ charset: utf8
+ collation: utf8_general_ci
+ reconnect: false
+ database: sao_development
+```
+
+これでrake db:migrateすると
+
+```sql
+CREATE TABLE `families` (
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(255) DEFAULT NULL,
+ `relationship` varchar(255) DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `relationship` (`relationship`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
+```
+
+MySQL側のデフォルトの照合順序(utf8_general_ci)で作成されて
+
+```sql
+SELECT * FROM families WHERE relationship = 'パパ';
++----+-----------+--------------+
+| id | name | relationship |
++----+-----------+--------------+
+| 2 | キリト | パパ |
++----+-----------+--------------+
+```
+
+ちゃんと'パパ'だけが検索に引っかかりました!
+
+これで家族におばあちゃんが増えても安心ですね!!
+
+### 参考
+- [mysqlのcollateを使って大文字-小文字や全角-半角を無視した検索 - end0tknrのkipple](http://d.hatena.ne.jp/end0tknr/20100613/1276427626)
+- [MySQL 5.5 の unicode collation で同一視される文字 - @tmtms のメモ](http://tmtms.hatenablog.com/entry/20110416/mysql_unicode_collation)