Edited at

【MySQL】照合順序とは?

More than 1 year has passed since last update.

照合順序(collations )が違うためSQLのjoinで失敗。

これを機会に照合順序について調べてみる。

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='


照合順序とは?

MySQLは文字コードとソート順を持っていて、ソート順の部分がCollationとよばれている。(文字コードの部分はCharacter Set)

比較するときには文字コードだけでなくてCollationが一致するかどうかを比較する(順序が合わないと比較できない)。

ので、JOINしようとするとエラーになる。

DB単位、テーブル単位、カラム単位で設定可能。


照合順序の意味

例えば cp932_japanese_ci のようなCollationの場合。


  • 文字コード: cp932

    utf8とかcp932とかのcharacter setの事。


  • 言語名: japanese

    japaneseやthaiやgeneral、unicodeなどが入ります。

    generalやunicodeはマルチリンガルの事(utf8にjapaneseはない)。


  • 比較法: ci

    _ci、_cs、_bin のいずれか(で終わる)

    _ci: 大文字と小文字が区別されない

    _cs: 大文字と小文字が区別される

    _bin: バイナリ



utf8_general_ciとutf8_unicode_ciの違い

whereでのフィルタや、joinの際に違いが出てくる。

utf8_unicode_ciの方があいまい検索またはあいまいな一致するが、少し遅いとのことらしい。

ちなみに、mysqlのデフォルトはutf8_general_ciとなる。


utf8_general_ci

アルファベットの大文字小文字は区別しない。他は全て区別。


utf8_unicode_ci

大文字小文字/全角半角を区別しない。

「AAA」で検索すると、半角アルファベット「AAA」、全角アルファベット「AAA」、半角アルファベット小文字「aaa」、全角アルファベット小文字「aaa」がマッチする。

「あああ」で検索すると、全角ひらがな「あああ」全角カタカナ「ァァァ」半角カタカナ「アアア」全角ひらがな小文字「ぁぁぁ」全角カタカナ小文字「ァァァ」がマッチする。

「2」で検索すると、半角数字「2」、全角数字「2」がマッチする。


utf8_general_ciのカラムでutf8_unicode_ciのあいまい検索を使う方法

select * from member where namae collate utf8_unicode_ci like '%サトウ%';

参考:【MySQL】大文字小文字、全角半角区別しないでマッチする検索をしたい


照合順序が違うカラムで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 してる


utf8_general_ciとutf8_unicode_ciをどう使うか

どちらを使用するかについて自分の考え

他の方の意見についてコメントで寄せて頂ければありがたい

以下の理由のため、特段理由がない場合、utf8_general_ciを使いたい

・デフォ設定のため手間が少ない

・パフォーマンス的が良いため

また、あいまい検索を行うカラムについは、SQLで対応する

参考

MySQLのCollationを理解するためにまとめてみた。

MySQLの照合順序