i Cubed Systems Engineering blog

株式会社アイキューブドシステムズの製品開発メンバーが、日頃のCLOMO開発の様子などを紹介します。

【MySQL】Railsアプリの文字コードと照合順序を変更したよ【utf8 to utf8mb4】

MySQL

はじめに

サーバーサイドを担当しているMisaki-i3です。
昨年、弊社のメインプロダクトであるCLOMO MDMにおいて、4バイト文字(絵文字・一部漢字等)への対応を行いました。
正確にはデータベース(MySQL)で使用する文字コードの設定変更で、4バイト文字を含まないUTF-8から4バイト文字を含むUTF-8へのコンバージョンになります。

UTF-8は現代の文字コードの標準であり、あらゆるサービスで様々な言語の文字や絵文字が当たり前に使えるようになっています。
そんな時代に、CLOMO MDMはUTF-8を採用していたにもかかわらず、データベースに保存できるのは3バイトの文字まででした。これは、後述するMySQLに特有の事情のためです。

4バイト文字に含まれる絵文字や漢字には対応しないと決めて、対応文字種を据え置きすることも可能ではあります。
しかし、外部のAPI利用や他社サービスとのデータ連携を提供している場合には、受け取った値を正常に処理できないリスクが生じます。
これは当然望ましいものではなく、CLOMOが対応に踏み切ったのも、主にデータ連携における不便の解消のためでした。

文字コードの変更が与える影響は、扱える文字種の増減だけではありません。
文字列検索やソートの挙動が変化しますので、サービスへの適用を慎重に検討する必要があります。
文字コードの変更がどのような影響をもたらしたか。アプリケーションの文字コード変更にあたってどのような検討が必要だったか。
この記事では、今回の変更で得られた知識を共有させていただきたいと思います。

文字コードと照合順序(COLLATION)

MySQLと『4バイト文字を含まないUTF-8』

MySQLで選択できる文字コードの中にUTF-8は3つ存在します。

charset
utf8mb3 3バイトまでのUTF-8
utf8 3バイトまでのUTF-8(utf8mb3のエイリアス)
utf8mb4 4バイト文字を含むUTF-8

このうち、utf8mb4が選択できるようになったのはMySQL5.5頃のことで、それ以前では4バイト文字を含むUTF-8を利用できませんでした。

UTF-8の4バイト文字には、絵文字や漢字の異体字(『吉』の異体字である『𠮷』など)が含まれています。
utf8mb3ではそれら4バイト文字を正常に保存できません。
具体的には、4バイト文字の出現位置以降すべてが失われた状態で保存されてしまいます。

(例: 『吉は常用漢字で𠮷は異体字』→『吉は常用漢字で』)

MySQL8.0では、utf8とutf8mb3は非推奨になっています。
以下はMySQL :: MySQL 8.0 リファレンスマニュアル :: 10.9.2 utf8mb3 文字セット (3 バイトの UTF-8 Unicode エンコーディング) の記述です。

utf8mb3 文字セットは非推奨であり、将来の MySQL リリースで削除される予定です。 かわりに utf8mb4 を使用してください。 utf8 は現在 utf8mb3 のエイリアスですが、ある時点では utf8 が utf8mb4 への参照になることが予想されます。 utf8 の意味があいまいにならないように、utf8 ではなく文字セット参照に utf8mb4 を明示的に指定することを検討してください。

新しく環境を設定する場合、必ずutf8mb4を明示的に指定しましょう！

照合順序(COLLATION)とは

文字コードの変更を検討するときは、照合順序の検討も必須です。
文字コードそれぞれに照合順序の定義セットが存在しているので、あるべき挙動を求められる照合順序を探し、選択する必要があります。

利用中のMySQLに搭載されている照合順序はinformation_schemaデータベースのCOLLATIONSテーブルに存在し、コンソールからも確認できます。
SHOW COLLATIONの結果画面

(MySQL8.2.0にある照合順序の一部)

さて、照合順序とはなにかというと、文字の順番(ソート)と文字の区別(検索) を定めているものです。

次のようなデータの入ったテーブルがあるとします。

Apple  
apple  
Blue  
blue

このリストを昇順でソートした場合、どのような順番になるでしょうか？
このリストに対して『apple』という文字列で検索したとき、『Apple』はヒットするでしょうか？
これらの結果に、照合順序は影響しています。

文字の区別という観点では、アルファベットの他に、次のふたつが有名です。

  • ハハパパ問題 (ハハとパパが区別されない)
  • 🍣🍺問題 (🍣と🍺が区別されない)

次章で詳しく検証してみます。

照合順序の調査

検証のために、Aa、あぁ、🍣🍺などを絡めたリストを作成し、主な照合順序の挙動を見てみました。

検証対象

  • utf8mb4_ja_0900_as_cs
  • utf8mb4_general_ci
  • utf8mb4_unicode_ci
  • utf8mb4_unicode_520_ci
  • utf8mb4_bin

ソートの違い

違うCOLLATIONでソートした結果

どれも一癖ある印象です。
utf8mb4_ja_0900_as_csは絵文字がアルファベットより先に来ている点で目立っています。
はさみとばさしの位置関係には、はとばの区別の有無が関係していそうです。

  • アルファベットの大小
  • かなの大小
  • ひらがな・カタカナ・半角ｶﾅ
  • 濁音・半濁音 これらの違いに注目して、採用したいソートの条件を決めていきます。

文字の区別の違い

先程と同じリストを利用し、a、あ、は、🍣、の4つの文字の検索結果を検証しました。

a あ は 🍣
utf8mb4_ja_0900_as_cs a あ, ア, ｱ は, ハ, ﾊ, ﾊﾟ, ﾊﾞ 🍣
utf8mb4_general_ci a, A あ は 🍣, 🍺, 𠮷
(他の4バイト文字も)
utf8mb4_unicode_ci a, A あ, ア, ｱ, ぁ, ァ, ｧ は, ハ, ﾊ, ぱ, パ, ﾊﾟ, ば, バ, ﾊﾞ 🍣
utf8mb4_unicode_520_ci a, A あ, ア, ｱ, ぁ, ァ, ｧ は, ハ, ﾊ, ぱ, パ, ﾊﾟ, ば, バ, ﾊﾞ 🍣
utf8mb4_bin a あ は 🍣
  • a, A
  • あ, ア, ｱ, ぁ, ァ, ｧ
  • は, ハ, ﾊ, ぱ, パ, ﾊﾟ, ば, バ, ﾊﾞ
  • `🍣, 🍺, 𠮷(他の4バイト文字も)

文字の区別では、これらをどう扱ってほしいかを考えます。

文字の区別を検証した記事としては、次のページが大変参考になりました。
検証結果だけでなく検証方法も分かりやすいので、今まさに照合順序を検討中だという方はぜひご覧ください。
日本語を適切に扱うための MySQL 5.7, 8.0 の character set と collation の設定 - 自動ドアに挟まれながら

採用する照合順序の検討

CLOMO MDMの場合、文字コードutf8に対して設定されていた照合順序はutf8_general_ciでした。
だからといって、同じ系統と思われるutf8mb4_general_ciを採用すると、🍣🍺問題が発生してしまいます。

従来の照合順序の挙動が必ずしも維持されるべきとは限らないのです。
照合順序の候補を挙げたら、どれを採用するかの選定に進みます。
プロダクトにとって最適な挙動に最も近い照合順序はどれなのか、よく検討すべきです。
また、求める条件に完全に一致する照合順序が存在しない可能性を忘れないでください。

次に、検討に際して確認しておくとよいこと、考慮すべきことを紹介します。
照合順序の変更にフォーカスした技術記事は少なく、今回の文字コード変更対応においては、手探りで懸念点の探索を行いました。

従来の挙動の確認

従来の挙動を考慮しない場合でも、改修の影響範囲を明らかにするために必要です。

  • ソート
    • 文字列型でソートされているリスト
    • 並べ替え機能を提供しているリスト
  • 検索
    • 文字列型の検索ボックス
    • 文字列でデータを絞り込む内部処理
  • MySQL以外がソートや検索に影響する箇所
    • MySQL以外のDBを利用している箇所
    • DBから取得したリストをアプリ側で加工する箇所
      • Ruby、JavaScript、その他ライブラリ

「区別しない照合順序」で困ること

大文字小文字を区別しない照合順序を使う場合、何に注意が必要でしょうか。

例えば、ログイン処理に懸念が発生します。
多くのプロダクトでは、ログイン画面にアカウントIDのフィールドが存在し、ログイン処理では入力されたアカウントIDをSQLに埋め込んでアカウントを特定しているものと思います。
アカウントIDをappleで登録しているユーザーが、ログイン画面でアカウントIDのフィールドにAppleと入力した場合はどうなるでしょうか。
もちろん、appleを見つけることができます。実際と異なる文字列であるにもかかわらずログインができてしまいます。

逆に、IDを登録する際に誤ってAppleで登録してしまったが、appleと入力してもログインできるため気付かれていない、ということが起こる可能性もあります。
(パスワードのほうは一般的にアプリ側でハッシュ化されるので、そのようなことは起こらないでしょう)

一応、アカウント特定後にアプリ側で改めて大文字小文字の一致判定を行うことで回避はできそうです。

ユーザー向けの検索機能ではしばしば、アルファベットの大文字小文字を区別しないことが求められます。
それは小文字にならした検索用のデータを作成しておいたり、検索のときのみ明示的に照合順序を変更したりすることで実現すべきと考えます。

大文字小文字の区別を「する」から「しない」へ変更すると困ること

先程の説明を踏まえれば想像は容易かと思われます。

appleとAppleなど、大文字小文字だけが異なるユーザーIDが存在した場合、ログイン処理でAppleと入力された場合に正しくヒットしないということになります。

更に、ユーザーIDのカラムにはユニーク制約がついている場合がほとんどでしょうから、照合順序を変更するためにALTER TABLEをかけようとした時点で、ユニーク制約に反すると判定されてしまいます。(参考: https://qiita.com/kyntk/items/4cf1058bf909fa5492be)

大文字小文字の区別をする照合順序からしない照合順序へ変更する場合には、データの特定に文字列検索を利用している処理を洗い出し、ユニーク制約やバリデーションの設計をよく確認する必要があるということです。

照合順序以外についての考慮

文字コードが変わるといっても、言ってしまえば不具合のあった文字コード(utf8mb3)をスタンダードな文字コード(utf8mb4)にアップデートするだけのものです。
できているべきだったことができるようになるだけであり、これといって考慮事項はありませんでした。

  • 追加すべきバリデーションはあるか
    • 「3バイト文字はいいけど4バイト文字は入れたくない」場所はない
    • 連携している外部サービスが4バイト文字に対応していなければ考慮が必要
  • 他の文字コード対応の考慮
    • SJISのCSVインポートを許容している箇所
      • utf8の時点で既に非対応の文字が発生しており、そこに4バイト文字も加わるだけなので特にすることはなかった

一部カラム対応か全カラム対応か

CLOMOはテーブルもレコードも膨大なデータ量となっているため、設計初期には、全ての文字列カラムの文字コードを変更するのは難しいと言われていました。
そのため、utf8mb4に変更するテーブル・カラムを必要最低限のものに絞り、DBへの変更作業にかかる時間や本番環境への影響を最小化する方針を取ろうとしました。

「どちらもUTF-8なのだから、utf8が4バイト文字を無視してくれるだけで……」と楽観視していたものの、そんな都合の良いことにはなりませんでした。

エラー発生時の画面
(Illegal mix of collations！)

比較対象に4バイト文字が含まれるとき、utf8の照合ルールのカラムは対応できずエラーが出てしまいました。3バイト文字のみであればエラーは出ませんでした。
4バイト文字と比較が行われるSQLでは、utf8カラムの照合ルールをutf8mb4に変更する必要があります。
utf8mb4のカラムとの比較と、Railsから入力された値との比較のどちらもです。

照合ルールの変更はCOLLATEで実現できます。

hoges.fuga LIKE '%🍣%' COLLATE utf8mb4_bin

これで、emailがutf8でも、エラーは出なくなります。

さて、この対応をアプリ内のすべてのSQLにほどこすことになります。

Railsから入力された値とは、ほとんどの場合ユーザーが入力する検索窓からの入力です。
「この検索窓は4バイト文字対応しないから4バイト文字は入力しないで」なんてことは言えません。ユーザー入力の関わるSQLにはCOLLATE句の追加が必須です。
今後の機能追加や改修でもCOLLATE句の考慮を忘れてはなりません。

もちろん、これはとても現実的ではありません。私は周囲に「無理っぽい」顔を見せ続けました。

幸いなことに全カラム対応が可能となったため、4バイト対応は現実のものとなりました。
本当に助かった。

検証項目

主な観点を示します。
照合順序の調査で利用したリストを検証チームに提供しました。

  • ソート
    • ここのソートはJSが行うので影響しない
    • ここのソートは影響する
    • ここは重要でないので検証不要
    • など
  • 区別される文字の検索
    • 英字の大文字・小文字
    • ハとバ
    • など
  • CSVインポート
    • UTF-8ファイルの正常動作
    • SJISファイルの正常動作

各開発者のローカル環境対応

DBをリセットしてよい場合は、単にRailsでdb:migrate:resetをしてもらいました。マイグレーションファイルをやり直す際、Railsは設定に従ってutf8mb4でテーブルを作成してくれます。

データをリセットしたくない場合には、既存のDBに対して、SQLで設定変更を行います。

まず、DBのデフォルト設定を変更するALTER DATABASEです。

/* ALTER DATABASE */  
ALTER DATABASE `LOCALdb` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;  
ALTER DATABASE `LOCALdb_test` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

次に、テーブルごとの設定を変更するALTER TABLEを作成します。
それぞれのDB状態に対応するため、その環境に存在するテーブルを取得し、必要なだけのALTER TABLEを出力するSQLを提供しました。

/* 存在するテーブルのALTER TABLE作成くん */  
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS sql_statements  
FROM information_schema.tables  
WHERE table_schema IN ('LOCALdb','LOCALdb_test') AND table_type = 'BASE TABLE'  
ORDER BY table_name DESC;

出力されたSQLを実行して、コンバージョンは完了です。

あとがき

決して「4バイト文字を比較するSQLに地道にCOLLATE句を追加しよう」なんてしてはいけません。

運良くDBサーバーの切り替え計画が近くにあり、「切り替え先なら全カラム対応が可能かも」と分かったため、4バイト文字対応は実現できました。
それでなかったとしても、時間をかけてでも全カラム対応をやりきってもらうか、諦めるかのどちらかにするべきです。

あちこちでCOLLATE考慮しなければならない設計など、丁寧に設計書を作っていたとしても恐ろしい未来が濃く見えます。
しかし、当時の私は「でもそうしないと4バイト対応ができない……そうしないといけないなら……」と、手段の一つとして残していました。(もちろん、実行しようとする前に先輩方からストップがかかったでしょう)

与えられた課題をこなすことが自分の価値だと思ってしまいがちですが、プロダクトの将来まで考慮し、諦めるべきと判断して意見するのも開発者の責務です。

We Are Hiring!

挑戦を楽しもう！