この記事は Lancers（ランサーズ） Advent Calendar 2023 の20日目の記事です。
はじめに
ランサーズ株式会社の種井です。
普段はエージェントサービス関連の開発業務を主に担当しています。
突然ですが、皆さんはデータベースの照合順序についてどれくらい意識して普段の開発業務を行っていますか？
もちろん、当たり前に照合順序が果たす役割について把握した上でテーブル設計やアプリケーションの開発に取り組んでいるという方も多いかと思います。
かくいう私は、VARCHAR型のカラムに対して文字コードを設定する際に使用する機能くらいの知識しかなく、特段意識することなく新規テーブルの追加や既存テーブルへのカラム追加をこれまで行っていました。
この記事では照合順序についての理解が乏しかった自分が、普段の開発業務でハマった話をいくつかご紹介できればと思います。
環境
MySQL8.0
照合順序とは
照合順序は、文字列を比較およびソートする方法を定義した一連のルールです。 MySQL でのそれぞれの照合順序は、単一の文字セットに属しています。 すべての文字セットには少なくとも 1 つの照合順序が属し、ほとんどの文字セットのは 2 つ以上の照合順序が属しています。
MySQL 8.0 リファレンスマニュアル 10.14 文字セットへの照合順序の追加
少し分かりにくいので、以下の記事が参考になりました。
MySQLは文字コードとソート順を持っていて、ソート順の部分がCollationとよばれている。(文字コードの部分はCharacter Set)
比較するときには文字コードだけでなくてCollationが一致するかどうかを比較する(順序が合わないと比較できない)。
ので、JOINしようとするとエラーになる。
DB単位、テーブル単位、カラム単位で設定可能。
異なる照合順序同士を比較演算子で比較できない
A テーブル
| Field | Type | Collation |
|---|---|---|
| column_a | varchar(32) | utf8_general_ci |
B テーブル
| Field | Type | Collation |
|---|---|---|
| column_b | varchar(32) | utf8mb4_0900_ai_ci |
utf8_general_ciを照合順序に持つcolumn_aとutf8mb4_0900_ai_ciを照合順序に持つcolumn_bを以下のように比較するとエラーが発生する。
...
WHERE
A.column_a = B.column_b
...
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
この問題を解決するにはクエリ内で直接Collationを指定する方法とカラムの定義を変更する方法の2つがあります。
クエリ内で直接Collationを指定する
下記のように一方の照合順序をクエリ実行時のみのスコープで変更します。
...
WHERE
A.column_a COLLATE utf8mb4_0900_ai_ci = B.column_b
...
または
...
WHERE
A.column_a = B.column_b COLLATE utf8_general_ci
...
カラムの定義を変更する
SQLのALTER TABLE句でカラム自体の定義を変更する。
余程の理由がない限りこの方法を用いるのが良いかと思います。
インデックスの恩恵が得られなくなる
照合順序が異なるとインデックスが適用されなくなります。
また上述した、クエリ内で照合順序を直接指定してもMySQLのオプティマイザはインデックスの適用を行なってくれないので注意が必要です。
背景
今回のような照合順序が異なるカラム同士を比較するケースは通常であればそこまで遭遇するケースではないかと思います。
自身がこのような状況になったのは、開発しているシステムの事情が深く関係しています。
元々MySQLの5系で作成されたアプリケーションで、1年ほど前にMySQL8.0に移行したという背景がありました。
MySQLの5系では新規でテーブル作成・カラム追加を行うと照合順序はデフォルトでutf8_general_ciが適用されます。一方でMySQL8以降ではutf8mb4_0900_ai_ciがデフォルトで適用されます。
このようにMySQLのバージョンアップ前後で作成されたテーブルでは特に注意が必要です。
おわりに
今回はMySQLの照合順序について普段の開発業務でハマった実体験を元に記事を書きました。
MySQLの細かい仕様については、まだまだ自分自身も把握できていないので、専門的な内容は書くことはできなかったのですが、もし同じような事象に陥った際など、問題解決の一助になれば幸いです。
追伸
照合の強制性に関して、MySQLがどのような挙動を取るのか公式ドキュメントを読んでみるのも面白いかと思います。
Discussion