【MySQL】照合順序とは?

  • 93
    いいね
  • 1
    コメント
この記事は最終更新日から1年以上が経過しています。

照合順序(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は、mysqlのデフォだし(設定増えると面倒って意味)、パフォーマンス的にも良いし通常のカラムはこっち使う。
問題はあいまい検索するカラムだけど、あいまい検索するときとしないときがあるvs常にあいまい検索、joinの条件となるカラムがどうかなど状況によりけりだと思う。
さらに今はこうだけど将来的にどうかなど考え出したらきりないので、一旦utf8_general_ciであいまい検索したいときはsql側で対応した方が楽かなと。いちいちカラムの照合順序まで覚えてられないし、それが原因で開発中とはいえエラー出るのはストレスだし。
もとからutf8_unicode_ciなのとsqlでutf8_unicode_ciにするのだとパフォーマンスに差がありそうだけど、そこまできつきつのシステムでもないしいいやという感じ。

参考
MySQLのCollationを理解するためにまとめてみた。
MySQLの照合順序