【MySQL】照合順序(collation)とは

以前のプロジェクトで、次のようなエラーメッセージに直面しました。

err: Error 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

このエラーは、異なる照合順序（collation）を使用しているために発生したものでした。当時は、データベース全体で照合順序を指定することが多かったため、照合順序について深く考えたことがなく、正直なところ、このエラーをきっかけに初めて照合順序について理解しました。恥ずかしながら、この経験を機に照合順序の重要性を再認識したので、今回その知識を整理し、まとめてみました。

照合順序（コレーション、collation）とは？

MySQLの照合順序（コレーション、collation）は、データベースで文字列を比較・ソートする際のルールを定義する設定です。多くのエンジニアがデータベースの設計やチューニングに取り組む際、この照合順序を適切に設定することで、アプリケーションのパフォーマンスやユーザーエクスペリエンスに大きな影響を与えることができます。

具体的には、次のようなケースで照合順序が適用されます。

文字列の比較
WHERE 句での文字列比較、ORDER BY 句でのソートなど。

インデックスの作成
文字列型のカラムにインデックスを張る際に、照合順序によってインデックスの構造が異なることがあります。

デフォルトの文字セットとの関係
文字セットと照合順序はセットで使われるため、文字セットを選択する際に照合順序も考慮する必要があります。

MySQLでは、照合順序は文字セット（例: utf8mb4）とともに指定され、例えば utf8mb4_general_ci や utf8mb4_0900_ai_ci などがあります。これらの設定は、データベース全体、テーブル、カラム、またはクエリ単位で変更することができます。

デフォルトの文字セット utf8mb4 の照合順序の一覧はこのように確認できます。

照合順序のサフィックスの意味

MySQLの照合順序の名前には、いくつかのサフィックスが付いています。
以下は、主要なサフィックスと10.3.1 照合の命名規則についてまとめました。
s は Sensitive、i はInsensitive を意味します。

cs / ci　(Case-sensitive / Case-insensitive)
大文字小文字を区別する / 区別しない
「apple」と「APPLE」を区別するかしないか
また、「全角・半角」 も区別するようです。

as / ai (Accent-sencitive / Accent-insensitive)
アクセントを区別する / 区別しない
「é」と「e」を区別するかしないか

ks (kana-sensitive)
カタカナひらがなを区別する。
ks 接尾辞のない日本語照合は、カナに依存せず、カタカナ文字とひらがな文字をソート用に同等に扱います。これが指定されている utf8mb4 の照合順序はutf8mb4_ja_0900_as_cs_ks しかありません。

bin　(Binary)
文字列をバイナリレベルで比較する
全ての文字がそのバイナリ値に基づいて比較されるため、大文字小文字、アクセント、その他の違いを全て区別します。

照合順序を選択する際の考慮事項

上記の「区別するしない」はwhereやJoin、ソート順などに影響があります。照合順序を選択する際には、アプリケーションの要件やターゲットとなるユーザー層を考慮する必要があります。以下は、照合順序を選ぶ際に考慮すべきいくつかのポイントです。

パフォーマンスと正確性のバランス
大量のデータを扱う場合、シンプルな照合順序（例: utf8mb4_general_ci）を選択することで、パフォーマンスを向上させることができます。しかし、正確な比較が必要な場合には、より複雑な照合順序（例: utf8mb4_bin）を選択すべきです。

特定の言語や文化に合わせた最適化
特定の言語や地域に特化したアプリケーションでは、その言語に最適化された照合順序を選ぶことが推奨されます。

MySQLで利用される主要な照合順序

utf8mb4_general_ci
特徴: シンプルでパフォーマンスに優れる
用途: 大文字小文字やアクセントを無視する一般的な用途に適しています
制限: 多言語対応や正確な比較が必要な場合には不十分

utf8mb4_unicode_ci
特徴: Unicode 標準に基づく比較を行い、広範な言語サポートを提供
用途: 多言語対応が必要なアプリケーションに適しています
制限: utf8mb4_general_ci よりも若干パフォーマンスが劣ることがあります

utf8mb4_0900_ai_ci
特徴: Unicode 9.0 に基づき、最新の文字セットと照合順序を提供
用途: 国際化対応が必要な新しいアプリケーションに最適
制限: 比較処理が複雑なため、パフォーマンスが若干低下する可能性があります

utf8mb4_bin
特徴: バイナリレベルでの比較を行うため、大文字小文字やアクセントを完全に区別。
用途: 厳密な比較が必要な場合や、バイナリデータの比較に適しています
制限: ソートや検索が直感的でない場合があります

照合順序の設定レベル

MySQLでは、照合順序をサーバーレベル、データベースレベル、テーブルレベル、カラムレベルで設定することができます。それぞれのレベルでの設定は、データベースの管理やアプリケーションの要件に応じて柔軟に対応できるようになっています。

サーバーレベル
デフォルトでは、これらは utf8mb4 および utf8mb4_0900_ai_ci ですが、サーバーの起動時にコマンドラインまたはオプションファイルで明示的に設定し、実行時に変更できます。

データベースレベル
CREATE DATABASE および ALTER DATABASE ステートメントには、データベース文字セットおよび照合順序を指定するためのオプション句があります。
データベース作成時に CREATE DATABASE 文で CHARACTER SET と COLLATE オプションを使用して設定します。この設定は、そのデータベース内の新しいテーブルに対するデフォルトとして使用されます。

テーブルレベル
テーブルレベルでの照合順序は、特定のテーブルに対して適用されます。CREATE TABLE および ALTER TABLE ステートメントには、テーブル文字セットおよび照合順序を指定するためのオプション句があります。
CREATE TABLE 文で CHARACTER SET と COLLATE を指定することで、テーブルごとのデフォルト設定を定義します。この設定は、そのテーブル内の新しいカラムに対するデフォルトとして使用されます。

カラムレベル
カラムレベルでの照合順序は、特定のカラムに対して適用されます。CREATE TABLE や ALTER TABLE 文で、特定のカラムに COLLATE を設定することで、カラムごとの照合順序を定義できます。これにより、同じテーブル内でも異なる照合順序を持つカラムを使用することが可能です。

まとめ
MySQLの照合順序は、データベース内の文字列操作において極めて重要な役割を果たします。各レベルで適切な照合順序を選択することで、アプリケーションのパフォーマンスを最大限に引き出すことができます。特に国際化対応や多言語サポートが必要な場合には、各レベルで慎重に設定を検討することが重要だと思います。

参考サイト
MySQL 8.0 リファレンスマニュアル :: 10 文字セット、照合順序、Unicode
MySQL 8.0: ひらがなカタカナを判別する日本語用Collation
MySQLの照合順序の設定をそろそろちゃんと整理しておく #Database – Qiita
DB (MySQL) の文字コード(charset)と照合順序(collation)について