Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
307
Help us understand the problem. What is going on with this article?
@kazu56

【MySQL】照合順序とは?

More than 3 years have 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の照合順序

307
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
kazu56

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
307
Help us understand the problem. What is going on with this article?