あいまい検索を実装する際に安易に飛びつくとやけどする気がするのでまとめた。
環境
今回はMySQL5.6のドキュメントをベースにしているためそちらの前提で記載しています。
そもそもMySQLのcollationsとは
日本語で表現すると「照合順序」という言葉になり。MySQLのヘルプには以下のように書かれている。
照合順序とは、文字セット内の文字を比較するためのルールを集めたものです。（中略）また実際には、ほとんどの照合順序は、大文字と小文字の区別だけでなく、アクセント符号 (「アクセント符号」とはドイツ語の「Ö」に見られるような文字に付けられた符号です) を区別するかどうかに関するルールや、複数文字のマッピングのルール (2 つのドイツ語の照合順序の一方における「Ö」 = 「OE」というルールなど) など多くのルールを含んでいます。 - MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.1 一般の文字セットおよび照合順序
ざっくりとして意味としては並び替えをするルールであり「文字の並び順」と「どの文字を同一とみなすか」を決めるルールの集まり。
Unicodeにおける照合順序
基本は文字コードと照合順序はセットなので、文字コードごとに照合順序は存在する。Unicode(utf8/utf8mb)の場合はバリエーションが色々あるが、今回取り上げるのは以下のもの
- utf8_bin
- utf8_general_ci（デフォルト）
- utf8_unicode_ci
*_bin の命名規則は 文字の比較は、文字バイナリコード値に従って行われます。 と定義されており、文字のバイナリコードが合うものを同列とし、そのバイナリコードの順番で並べる。
それと比較して、*_ci の命名規則は 大文字と小文字を区別しない照合順序を示します。と定義されており、「a」と「A」を同列で扱うような挙動をする。
そのなかでも general_ci と unicode_ci は挙動が違う。ヘルプでは以下のように記載されている。
Unicode 文字セットの場合、xxx_general_ci 照合順序を使用して実行する演算は、xxx_unicode_ci 照合順序のものよりも高速です。たとえば、utf8_general_ci 照合順序の比較は、utf8_unicode_ci の比較よりも高速ですが、精度は少し低くなります。この理由は、utf8_unicode_ci では、ある文字がほかの文字の組み合わせに等しいものと見なされる拡張形式などのマッピングをサポートしているためです。たとえば、ドイツ語とほかのいくつかの言語では、「ß」は「ss」と同じです。utf8_unicode_ci は、短縮形式と無視可能な文字もサポートします。utf8_general_ci は、拡張形式、短縮形式、無視可能な文字をサポートしない従来の照合順序です。文字間で 1 対 1 の比較しかできません。 - MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14.1 Unicode 文字セット
上記のように unicode_ci は同一視される言語を同じ単語とするようにみなしているが、general_ci のほうは単純な大文字と小文字を同列にみなす程度のサポートしかしていないというもの
5.5のものだが、同一視される文字の一覧を見ると、「8」と「⑧」や「ル」と「ﾙ」同一のものとみなされるなどが例に出されている。
unicode_ciによって起きるははパパ問題
ここまでの話を切り取ると「よしなに同一視しても問題ないものは unicode_ci のほうがよさそう」というように見えるが、実際はそんなに都合のいいものではなく、日本語的にはあんまり同一視してほしくないものも同一視する。
たとえば以下のようなもの
- 「は」と「パ」
- 「⺝」と「⽉」
- 「⻯」と「⿓」
物によっては上記のようなものを同一視しても問題ないケースがあるかもしれないが、「は」などに関してはあとあと問題になるケースが多いと思われる。（これを「はは=パパ」問題と呼称したりする）
では general_ci ではどうか、というと今度は寿司ビール問題が発生する。
general_ciにすると起きる寿司ビール問題
寿司ビール問題というのは俗称で、🍣と🍺が同一の文字とみなされてしまう問題。
事象の原因としてはかみぽさんの記事に和訳があるのでそれを引用させてもらうと以下の通り
・BMP文字で general collation (xxx_general_ci) の場合、コードポイントを使う。
・SMP文字(絵文字とか)の場合、0xfffd REPLACEMENT CHARACTER と同じ weight になる- MySQL と寿司ビール問題 - かみぽわーる
ということで絵文字はすべて同一とみなされてしまう。（なお、REPLACEMENT CHARACTERに関してはこちらを参照のこと）
そのため昨今の絵文字が日常的に使われる場面では _bin の照合順序にするほうが良いケースが多い
余談：MySQL8では
MySQL8系ではこの問題をカバーした utf8mb4_ja_0900_as_cs が登場している。こちらに関してはははパパ問題も寿司ビール問題も解決しているし、aとAを同一のものと解釈もする。詳しくは下記ブログ参照のこと。
日々の覚書: MySQL 8.0.1でutf8mb4_ja_0900_as_csが導入された
