image_name

こんにちは。ギフティでエンジニアをしています noda です。

以前、ユニーク制約が付与されたカラムに対して「バス」と「ハス」という値のレコードを作成しようとした際、MySQL でユニーク制約違反のエラーが発生したことがありました。この挙動は自分の意図していないもので、照合順序の考慮が漏れておりデフォルト設定のまま運用してしまっていたことが原因でした。

本記事では MySQL における照合順序の概要と、どの照合順序を選択すべきかの観点を紹介していきます。

前提

本記事は MySQL の 8.0 系で文字コードに utf8mb4 を使用する場合について言及しています。基本的な考え方は同じですが、他の環境ではここで紹介する照合順序が使用できない場合がありますので注意してください。

またここでは具体的な設定方法については紹介しません。

照合順序（collation）とは

https://dev.mysql.com/doc/refman/8.0/ja/charset-general.html

ざっくりいうと、

文字列同士を比較する際のルール

のことです。照合順序によってソート結果や以下のような文字同士の比較結果が異なります。

濁点の有無
半濁点の有無
大文字/小文字
半角/全角
仮名/捨て仮名
かな/カナ
絵文字
etc...

比較結果が等価の場合は同じ文字として扱われ、不等価の場合は異なる文字として扱われます。

照合順序による影響

以下に照合順序が結果に影響する事例を一部紹介します。

WHERE 句や LIKE 句による絞り込み

カラムに格納されている「バス」という値を「ハス」で検索した場合、濁点の有無で比較結果が等価となる照合順序ではヒットしますが、不等価となる照合順序ではヒットしません。

ユニーク制約の判定

濁点の有無で比較結果が等価となる照合順序では、「バス」と「ハス」は同じ文字列として扱われユニーク制約違反になります。

文字列同士のソート結果

比較結果が等価となる照合順序と不等価となる照合順序でソート結果が異なる場合があるのは自明ですが、不等価となる照合順序同士でもソート結果が異なる場合があります。

照合順序の種類

https://dev.mysql.com/doc/refman/8.0/ja/charset-mysql.html

文字コードごとに複数の照合順序が存在します。

MySQL の 8.0 系では、デフォルトで utf8mb4_0900_ai_ci が設定されます。

命名規則

https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html

照合順序は 3 つのブロックの構成で命名されます。

utf8mb4_0900_ai_ci を例にすると以下です。

utf8mb4：ブロック ① 文字コード
0900：ブロック ② Unicode 照合アルゴリズム (UCA) のバージョン
ai_ci：ブロック ③ 照合サフィックス

各仕様はある程度命名から判別することができます。

ブロック ① 文字コード

使用可能な文字コードを示します。

ブロック ② Unicode 照合アルゴリズム (UCA) のバージョン

MySQL は Unicode 照合順序アルゴリズム (UCA) に従って照合順序が実装されています。0900 の場合、 UCA のバージョン 9.0.0 の仕様に従い実装されていることを示します。

ちなみにこのブロックに general が指定されている照合順序は UCA 準拠ではなく、MySQL 独自定義の仕様に基づき実装されています。

ブロック ③ 照合サフィックス

アクセント、大文字/小文字、かな/カナを区別するかどうかを示します。

半角/全角の区別など、命名にあらわれない文字が存在することに注意してください。また、かな/カナの区別が命名に現れるのは後述の日本語固有の照合順序のみです。

サフィックス	意味
ai	アクセントを区別しない
as	アクセントを区別する
ci	大文字/小文字を区別しない
cs	大文字/小文字を区別する
ks	かな/カナを区別する
bin	全て区別する

※ i は Insensitive（区別しない）の略、s は Sensitive（区別する）の略

言語固有の照合順序

一部の国の言語に対しては個別に調整された照合順序が存在します。

utf8mb4 の場合、日本語用に調整された utf8mb4_ja_0900_as_cs と utf8mb4_ja_0900_as_cs_ks が用意されています。

どの照合順序を選択すべきか

基本的な概念の説明をしたところで、どの照合順序を選択すべきかの観点を挙げていきます。（特に日本語を考慮した場合）必ずしも全ての要件が満たせる照合順序が存在するとは限らないということは念頭に置いておく必要があります。

要件上区別したい文字はどれか

プロダクトの要件によって区別したい文字は異なるため、要件にあった照合順序を選択する必要があります。

以下は utf8mb4 で使用可能な代表的な照合順序で、どの文字が区別されるかをまとめた表です。

照合順序	A ≠ a (大文字/小文字)	A ≠ Ａ (半角/全角)	あ ≠ ぁ (仮名/捨て仮名)	あ ≠ ア (かな/カナ)	は ≠ ば (濁点有/濁点無)	ば ≠ ぱ (半濁点有/半濁点無)	🍣 ≠ 🍺 (絵文字)
utf8mb4_0900_ai_ci	×	×	×	×	×	×	○
utf8mb4_0900_as_ci	×	×	×	×	○	○	○
utf8mb4_0900_as_cs	○	○	○	○	○	○	○
utf8mb4_ja_0900_as_cs	○	×	○	×	○	○	○
utf8mb4_general_ci	×	○	○	○	○	○	×

utf8mb4_0900_ai_ci は濁点、半濁点の有無が区別されない点が日本語を扱う上で不都合となるケースが多そうです。

utf8mb4_0900_ai_ci、utf8mb4_0900_as_ci、utf8mb4_general_ci は大文字/小文字が区別されません。大文字/小文字が混在するトークン等の値を扱う必要がある場合は適さないと言えるでしょう。

また先述の通り utf8mb4_general_ci は UCA 準拠ではない点にも注意が必要です。これによる影響の一つに絵文字が区別されない点があります（巷ではこの問題を寿司ビール問題¹というらしいです）。

ソート結果が期待通りか

照合順序によってソート結果にも影響があるため齟齬がないか確認しておきましょう。

検証のために、utf8mb4 で使用可能な代表的な照合順序で、以下のようなレコードを持つテーブルを作成しソート結果を比較しました。

-- utf8mb4_0900_ai_ci の場合

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(255) NOT NULL
) COLLATE utf8mb4_0900_ai_ci;

INSERT INTO example (value) VALUES
('apple'), ('Apple'), ('banana'), ('cafe'), ('あい'), ('アイ'), ('あいう'), ('いう'), ('ぱ'), ('ば'), ('は'), ('山田'), ('鈴木'), ('東京'), ('大阪'), ('雨'), ('川');

SELECT * FROM example ORDER BY value;

結果は以下のとおりです。

照合順序	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
utf8mb4_0900_ai_ci	apple	Apple	banana	cafe	あい	アイ	あいう	いう	ぱ	は	ば	大阪	山田	川	東京	鈴木	雨
utf8mb4_0900_as_ci	apple	Apple	banana	cafe	あい	アイ	あいう	いう	は	ば	ぱ	大阪	山田	川	東京	鈴木	雨
utf8mb4_0900_as_cs	apple	Apple	banana	cafe	あい	アイ	あいう	いう	は	ば	ぱ	大阪	山田	川	東京	鈴木	雨
utf8mb4_ja_0900_as_cs	apple	Apple	banana	cafe	あい	アイ	あいう	いう	は	ば	ぱ	雨	山田	川	大阪	東京	鈴木
utf8mb4_general_ci	apple	Apple	banana	cafe	あい	あいう	いう	は	ぱ	ば	アイ	大阪	山田	川	東京	鈴木	雨

注目したいのは utf8mb4_ja_0900_as_cs です。

日本語用に調整されているといったものの、単語の一般的な読みでソートされているわけではないことがわかります。（今回の例では、山田【やまだ】よりも川【かわ】の方が上位になることを期待しましたが、山【一般的な音読みでサン】と川【一般的な音読みでセン】を比較した結果、山田が上位になったものと思われます。またひらがなと漢字同士を読みでソートするといったことはしてくれません。）

実行速度を重視するか

公式ドキュメントでは照合順序によって実行速度に差があることが明記されています。

UCA 9.0.0 以上に基づく照合は、9.0.0 より前の UCA バージョンに基づく照合より高速です。

実行速度が重要な要件となる場合は考慮が必要かもしれません。

検証のために、utf8mb4 で使用可能な代表的な照合順序で、任意の文字列をもつレコード 100 万件に対し、ソートにかかった時間を比較しました。

MySQL 8.0.36 を用いた手元の環境では utf8mb4_general_ci がやや遅く、他はあまり差がないという結果になりました。この結果は MySQL のバージョンによっても異なる可能性があるため、実行速度の考慮が必要な場合は手元の環境で確認いただくと良さそうです。

担当プロダクトでの判断

私の担当するプロダクトでは utf8mb4_0900_as_cs を選択しました。

今回要件上区別したい文字が、濁点の有無、半濁点の有無、大文字/小文字であったこと、実行速度にシビアな要件がなかった（極端に遅いといったことがなければ問題ない）ことから、utf8mb4_0900_as_cs と utf8mb4_ja_0900_as_cs が最終的な候補に上がった中、後者はソート結果が期待通りとはいえず採用を見送っています。MySQL 5.7 ではデフォルトで utf8mb4_general_ci が設定されるため、これを引き継いでいるプロダクトも多いかと思われます。しかし担当プロダクトが新規開発フェーズだったこともあり、上記に挙げた観点を考慮した結果、要件に最も近いものを選択する方針にしました。