utf8mb4の照合順序について調べてみた

MySQLで文字型のカラムを作成する時に、照合順序をどうするか悩む場合があります。

今回はUTF-8での照合順序の違いを調べてみました。

MySQL8.0の文字コードutf8mb4で、日本語が絡んできそうな照合順序には以下のものがあります。

utf8mb4_binは文字をバイナリで比較します。

「a」と「A」は文字コードが違うので異なる文字として扱われます。

それに対してutf8mb4_general_ciとutf8mb4_unicode_ciはアルファベットの大文字と小文字を区別しません。

「a」と「A」は同じ文字として扱われます。

照合順序名の末尾にある「ci」は大文字小文字を区別しない（Case Insensitive）という意味です。

utf8mb4_general_ciとutf8mb4_unicode_ciでは、日本語の扱いに違いがあります。

utf8mb4_general_ciでは「あ」と「ア」は異なる文字として扱われるのですが、utf8mb4_unicode_ciでは同じ文字として扱われます。

さらにutf8mb4_unicode_ciでは「は」「ぱ」「ば」「ハ」「バ」「パ」も同じ文字として扱われます。

utf8mb4_general_ciとutf8mb4_unicode_ciには問題があり、UTF-8で4バイトの文字は全て同じ文字として扱われます。

有名な「寿司ビール問題」がそれです。

utf8mb4_unicode_520_ciではその問題が解消されました。

utf8mb4_unicode_ciとutf8mb4_unicode_520_ciの違いは、文字を照合する際のUnicodeのバージョンが4.0.0から5.2.0になったことです。

これによって4バイト文字も適切に扱えるようになりました。

以下の照合順序はUnicodeのバージョンが9.0.0に上がっています。

照合順序名の末尾に「ai」「cs」「ks」などが付いています。

それぞれ以下の意味になります。
|文字|意味 |例 |
|—-|————————————————–|——|
| ai |アクセントの違いを区別しない（Accent Insensitive）|A = Ä |
| as |アクセントの違いを区別する（Accent Sensitive） |A ≠ Ä|
| ci |大文字・小文字を区別しない（Case Insensitive） |A = a |
| cs |大文字・小文字を区別する（Case Sensitive） |A ≠ a|
| ks |ひらがな・カタカナを区別する（Kana Sensitive） |あ ≠ ア|

「ai」の場合「ぱ」と「ば」を区別しません。

また、「ci」の場合「や」と「ゃ」を区別しません。

「_ja」がつく照合順序の場合は「_ks」が付くか付かないかで、ひらがな・カタカナを区別するかしないかが分かれました。

以上のことをまとめると以下の表のようになりました。

utf8mb4_general_ci、utf8mb4_unicode_ciはUnicodeのバージョンが古く、4バイト文字の区別ができないので使用すべきではないです。

utf8mb4_unicode_520_ciもUnicodeのバージョンが古いので使用する意味がなさそうです。

日本語が入るカラムであれば、utf8mb4_ja_0900_as_cs_ksを選択するのが無難でしょうか。

名前を検索する時に「は」で検索したら「馬場」さんも検索結果に含めたい時などはutf8mb4_0900_ai_ciを使うと便利です。

今回はイコール「=」で一致するかどうかを見ましたが、LIKE検索をした場合や「>」「<」で大きさを比較した場合は違った結果が得られました。

まだまだ照合順序の奥は深そうです。

システム部ではデータベースのスペシャリストを募集しています。

我こそはと思う方がいましたら、 システム部で一緒に働きましょう。