🐡

MySQL8.0 utf8mb4 系での照合順序を比較してみた

に公開
MySQL
SQL
MySQL 8
tech

はじめに

久しぶりに記事を書いています。MySQL8.0系において照合順序って何が良いんだろうと考える機会があったので、記事にしてみました。

照合順序って何？

簡単に言うと文字セット内の文字を比較ルールの集合です。
https://dev.mysql.com/doc/refman/8.0/ja/charset-general.html_

照合順序によってWHERE句を指定した場合の取得クエリの結果が異なったり、ソートの結果が異なったりします。

また、照合順序の名称で文字コードの区別の仕方がだいたい分かります

名称 省略前の名称 意味
ai Accent Insentive アクセントを区別しない
ci Case Insentive 大文字小文字を区別しない
as Accent Sensitive アクセントを区別する
cs Case Sensitive 大文字小文字を区別する
ks Kana Sensitive ひらがなカタカナを区別する

MySQL8.0 utf8mb4系での照合順序の種類

MySQL8.0 utf8mb4系での照合順序を見たい場合はMySQL内部で以下のクエリを実行すると一覧で表示されます。

SHOW COLLATION WHERE Charset = 'utf8mb4';

私の手元だと89個存在しました。

その中で主に日本語が使われるようなプロダクトで候補に上がるのは以下の照合順序かなと思います。

照合順序名 Pad_attribute 備考
utf8mb4_0900_ai_ci No Pad デフォルトの照合順序
utf8mb4_0900_as_ci No Pad
utf8mb4_0900_as_cs No Pad
utf8mb4_0900_ja_as_cs No Pad
utf8mb4_0900_ja_as_cs_ks No Pad
utf8mb4_0900_bin No Pad バイナリで比較
utf8mb4_general_ci Pad Space

この記事では上記の照合順序を文字の区別、ソート結果、クエリ実行速度の観点で評価していこうと思います。

照合順序ごとの文字の区別

まずは、それぞれの区別の観点で日本語ではどのような文字と文字が区別されないのかを考えていきます。

アクセント

英語でいうと「a」 と 「á」のような文字が同一と区別するかです。日本語でいうと濁音、半濁音がアクセント扱いとされているようです。そのためアクセントを区別しない照合順序では「は」、「ぱ」、「ば」は同じ文字と判定されます。

大文字小文字

英語でいうと「A」と「a」のような文字が同一と区別するかです。

捨て仮名の区別

捨て仮名とは日本語における小文字のようなものです（厳密な詳細は省きます）。英語の大文字小文字とは若干扱いが異なるので別のくくりとしています。「あ」と「ぁ」などです。

ひらがなカタカナ

「あ」と「ア」のような文字を同一と区別するかです。

全角半角

「ア」と「ｱ」のような文字を同一と区別するかです。

絵文字

絵文字に関する有名な問題として寿司ビール問題があります。🍣が🍺と同じだと判定されてしまう問題です（代表的なものとしてこれがあるだけで、他の絵文字でも起き得ます）。そのため、すべての絵文字が区別できるかというのも観点の一つとして挙げられます。

検証

それぞれの照合順序のテーブルを作成し、同じデータを作成して、それぞれのテーブルに対してWHERE句を付けたSELECTクエリを発行します

例えばutf8mb4_0900_ai_ciに対してテーブルを作成し、検証してみます。

CREATE TABLE tests_0900_ai_ci (
    name VARCHAR(255) NOT NULL
) ENGINE=InnoDB, CHARSET=utf8mb4, COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO tests_0900_ai_ci (name) VALUES
('a'), ('A'), ('あ'),('ぁ'), ('ア'), ('ｱ'), ('は'), ('ぱ'), ('ば'), ('ハ'), ('パ'), ('バ'), ('嵐'), ('井戸'), ('牛'), ('🍣'), ('🍺');

mysql> SELECT * FROM tests_0900_ai_ci WHERE name = "あ";
+------+
| name |
+------+
| あ   |
| ぁ   |
| ア   |
| ｱ    |
+------+
4 rows in set (0.01 sec)

mysql> SELECT * FROM tests_0900_ai_ci WHERE name = "a";
+------+
| name |
+------+
| a    |
| A    |
+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM tests_0900_ai_ci WHERE name = "は";
+------+
| name |
+------+
| は   |
| ぱ   |
| ば   |
| ハ   |
| パ   |
| バ   |
+------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM tests_0900_ai_ci ORDER BY name ASC;
+--------+
| name   |
+--------+
| 🍣       |
| 🍺       |
| A      |
| a      |
| ぁ     |
| ア     |
| ｱ      |
| あ     |
| は     |
| ぱ     |
| ば     |
| ハ     |
| パ     |
| バ     |
| 井戸   |
| 嵐     |
| 牛     |
+--------+
17 rows in set (0.01 sec)

大文字小文字、濁音半濁音が区別されていないことがわかります。
上記のような検証を各照合順序に対して行います。

照合順序名 大文字と小文字 半角と全角 かなとカナ 通常仮名と捨て仮名 濁音や半濁音と濁音なし 🍣と🍺
utf8mb4_0900_ai_ci 区別しない 区別しない 区別しない 区別しない 区別しない 区別する
utf8mb4_0900_as_ci 区別しない 区別しない 区別しない 区別しない 区別する 区別する
utf8mb4_0900_as_cs 区別する 区別する 区別する 区別する 区別する 区別する
utf8mb4_ja_0900_as_cs 区別する 区別しない 区別する 区別しない 区別する 区別する
utf8mb4_ja_0900_as_cs_ks 区別する 区別しない 区別する 区別する 区別する 区別する
utf8mb4_0900_bin 区別する 区別する 区別する 区別する 区別する 区別する
utf8mb4_general_ci 区別しない 区別する 区別する 区別する 区別する 区別しない

実行速度についても検証したかったのですが、ちょっと長くなりそうなので別記事で。
utf8mb4_0900_binなどはバイナリで比較するので速いはずです。

どの照合順序が良いか

もちろんこれはケースバイケースではありますが、濁音半濁音が区別されないのは大抵のプロダクトで困りそうなのでなさそうです。絵文字の区別もできなくて困らないプロダクトも多いでしょうが、大きなデメリットがなければ区別できると良いでしょう。その観点から utf8mb4_0900_ai_ci, utf8mb4_general_ci などはあまり候補に入らなさそうな印象です。残りは要件次第だとは思いますが、utf8mb4_0900_as_cs と utf8mb4_0900_bin などが候補に入りそうです。五十音順などが気にならない、速度重視である場合はutf8mb4_0900_binも照合順序としての採用も検討の余地に入るかなと思います。迷ったらとりあえず utf8mb4_0900_as_cs にしておけば最悪曖昧性の表現もアプリケーション上で実現できるので良いかなと思いました。

参考文献

https://tech.giftee.co.jp/entry/2025/01/09/163909

Discussion