Collation…
MySQL独特の仕様、Collationがあまりしっくりこなかったので理解するためにまとめてみました。
テーブルJOINしようとして、たまにCollationちげーよ！とか言われるアレです。
ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Oh...Collation とは？
直訳すると照合。
文字コード毎の照合順序を定義します。
大雑把に言ってしまうと。
MySQLは文字コードとソート順を持っていて、ソート順の部分がCollationとよばれている。(文字コードの部分はCharacter Set)
比較するときには文字コードだけでなくてCollationが一致するかどうかを比較する(順序が合わないと比較できない)。
ので、JOINしようとするとコレーっとなる。。。
Collation の意味
Collationの命名規則は，”文字コード_言語名_比較法”
例えば cp932_japanese_ci のようなCollationの場合。
文字コード: cp932
言語名: japanese
比較法: ci
と、読める。
文字コード、言語名、比較法？
文字コード
ご存知、utf8とかcp932とかのcharacter setの事。
言語名
japaneseやthaiやgeneral、unicodeなどが入ります。
generalやunicodeはマルチリンガルの事(utf8にjapaneseはない)。
比較法
_ci、_cs、_bin のいずれか(で終わる)
_ci: 大文字と小文字が区別されない
_cs: 大文字と小文字が区別される
_bin: バイナリ
Collationの確認
MySQLで使用できるCollationを確認するには、show collationステートメントを実行する。
例: utf8のcollationを取得
mysql> show collation like 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
+--------------------------+---------+-----+---------+----------+---------+※１行目と３行目に注目。utf8_general_ciがutf8のデフォルトCollationになっている。が。Railsでは通常utf8_unicode_ciが使用される。
で。悲劇が起きる。。。
Column の Collation を確認
ColumnのCollationを確認するには、show full columns from tableステートメントを実行する。
例: itemsテーブル(項目)のCollationを確認する
mysql> show full columns from items;
+-------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| code | varchar(255) | utf8_unicode_ci | YES | UNI | NULL | | select,insert,update,references | |
| name | varchar(255) | utf8_unicode_ci | YES | | NULL | | select,insert,update,references | |
+-------------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+※intにはCollationがないですね
utf8_general_ciとutf8_unicode_ciの使い分け
unicode の方はあいまいな照合が可能です。全角、半角、大文字、小文字を無視して一致するものを検索できます。
たとえば、検索文字に‘MySQL’を指定した時と、‘ｍｙｓｑｌ’を指定した時の検索結果は同じになります。
general の方はその逆で、厳密に違いとして認識され先の例の検索結果は異なります。
どちらも項目の用途によって使い分けるのが、あるべき姿なのでは無いかと思います。
※余談ですがデフォルトで全項目unicodeを選択されるのはRailsぽいなーと思いました。id(int)はcollation関係ないですもんね。
JOIN !
で。utf8_general_ciとutf8_unicode_ciはCollationが違いますが、そもそも文字コードは同じutf8ですので、Collationを指定してやることで比較が(もちろんJOINも)できるようになります。
例: Collation を指定して JOIN する (collate句を使います)
select * from table_a a
inner join table_b b
on a.code = b.code collate utf8_general_ci -- ココで collate してるまとめ
理解できるとなかなかCollationも便利なように思えてきました。
最後のJOINのように collete句 を使う場合、table_b.code のindexって効かなくなるのかな…？
誰か詳しい方がおられましたら教えていただきたいデス。
参考:
MySQL :: MySQL 5.1 リファレンスマニュアル :: 9.2 MySQLにおけるキャラクタセットおよび照合順序
Written with StackEdit.
