【MySQL】collation（照合順序）について

MySQL5.7→8.0へのバージョンアップ対応をした際、これまで使用していた`utf8mb3`が非推奨になったため、`utf8mb4`に文字コードを変更しました。

文字コードの変更に伴い、デフォルトのcollationも以下のとおり変わっています。
utf8mb3：`utf8mb3_general_ci`
utf8mb4：`utf8mb4_0900_ai_ci`

この２つのcollationは何が違うのか？
せっかくなので、調べたことを備忘録的にまとめてみました。

# 目次
- [そもそもcollationって？](#そもそもcollationって？)
- [各collationの特徴](#各collationの特徴)
- [実際に検証してみる](#実際に検証してみる)
- [まとめ](#まとめ)
# そもそもcollationって？
collationとは、比較対象とする2つの文字を**区別するのか・しないのか**を決めるルールのことです。

例えば、`Apple`と`apple`は大文字・小文字を区別するcollationであれば別の文字列として扱われますが、区別しないcollationであれば同じ文字列として扱われます。

この他、ひらがな・カタカナを区別するのか・しないのかを判定してくれるようなcollationもあります。

以下のように、特定の文字を区別するのか・しないのかで結果が変わりうるSQLはcollationの影響を受けます。

（例）
- `WHERE`によるデータの絞り込み
- `ORDER BY`によるソート順
- `GROUP BY`によるグループ化
- `SUBSTRING`による置換対象の抽出

# 各collationの特徴
MySQLでは、以下の命名規則によりcollation名が決まっています。

そのため、collation名を確認するだけでどのような文字を区別しているのかが一目でわかります。

なお、使用できるcollationは次のとおりです。

<details><summary>collation一覧を開く</summary>

```sql
mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
| utf8mb4_bg_0900_ai_ci      | utf8mb4 | 318 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bg_0900_as_cs      | utf8mb4 | 319 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_bs_0900_ai_ci      | utf8mb4 | 316 |         | Yes      |       0 | NO PAD        |
| utf8mb4_bs_0900_as_cs      | utf8mb4 | 317 |         | Yes      |       0 | NO PAD        |
| utf8mb4_croatian_ci        | utf8mb4 | 245 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_cs_0900_ai_ci      | utf8mb4 | 266 |         | Yes      |       0 | NO PAD        |
| utf8mb4_cs_0900_as_cs      | utf8mb4 | 289 |         | Yes      |       0 | NO PAD        |
| utf8mb4_czech_ci           | utf8mb4 | 234 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_danish_ci          | utf8mb4 | 235 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_da_0900_ai_ci      | utf8mb4 | 267 |         | Yes      |       0 | NO PAD        |
| utf8mb4_da_0900_as_cs      | utf8mb4 | 290 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | utf8mb4 | 256 |         | Yes      |       0 | NO PAD        |
| utf8mb4_de_pb_0900_as_cs   | utf8mb4 | 279 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_ai_ci      | utf8mb4 | 273 |         | Yes      |       0 | NO PAD        |
| utf8mb4_eo_0900_as_cs      | utf8mb4 | 296 |         | Yes      |       0 | NO PAD        |
| utf8mb4_esperanto_ci       | utf8mb4 | 241 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_estonian_ci        | utf8mb4 | 230 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_es_0900_ai_ci      | utf8mb4 | 263 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_0900_as_cs      | utf8mb4 | 286 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 |         | Yes      |       0 | NO PAD        |
| utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_ai_ci      | utf8mb4 | 262 |         | Yes      |       0 | NO PAD        |
| utf8mb4_et_0900_as_cs      | utf8mb4 | 285 |         | Yes      |       0 | NO PAD        |
| utf8mb4_general_ci         | utf8mb4 |  45 |         | Yes      |       1 | PAD SPACE     |
| utf8mb4_german2_ci         | utf8mb4 | 244 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_gl_0900_ai_ci      | utf8mb4 | 320 |         | Yes      |       0 | NO PAD        |
| utf8mb4_gl_0900_as_cs      | utf8mb4 | 321 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_ai_ci      | utf8mb4 | 275 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hr_0900_as_cs      | utf8mb4 | 298 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hungarian_ci       | utf8mb4 | 242 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_hu_0900_ai_ci      | utf8mb4 | 274 |         | Yes      |       0 | NO PAD        |
| utf8mb4_hu_0900_as_cs      | utf8mb4 | 297 |         | Yes      |       0 | NO PAD        |
| utf8mb4_icelandic_ci       | utf8mb4 | 225 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_is_0900_ai_ci      | utf8mb4 | 257 |         | Yes      |       0 | NO PAD        |
| utf8mb4_is_0900_as_cs      | utf8mb4 | 280 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs      | utf8mb4 | 303 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ja_0900_as_cs_ks   | utf8mb4 | 304 |         | Yes      |      24 | NO PAD        |
| utf8mb4_latvian_ci         | utf8mb4 | 226 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_la_0900_ai_ci      | utf8mb4 | 271 |         | Yes      |       0 | NO PAD        |
| utf8mb4_la_0900_as_cs      | utf8mb4 | 294 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lithuanian_ci      | utf8mb4 | 236 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_lt_0900_ai_ci      | utf8mb4 | 268 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lt_0900_as_cs      | utf8mb4 | 291 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_ai_ci      | utf8mb4 | 258 |         | Yes      |       0 | NO PAD        |
| utf8mb4_lv_0900_as_cs      | utf8mb4 | 281 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4 | 322 |         | Yes      |       0 | NO PAD        |
| utf8mb4_mn_cyrl_0900_as_cs | utf8mb4 | 323 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_ai_ci      | utf8mb4 | 310 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nb_0900_as_cs      | utf8mb4 | 311 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_ai_ci      | utf8mb4 | 312 |         | Yes      |       0 | NO PAD        |
| utf8mb4_nn_0900_as_cs      | utf8mb4 | 313 |         | Yes      |       0 | NO PAD        |
| utf8mb4_persian_ci         | utf8mb4 | 240 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_pl_0900_ai_ci      | utf8mb4 | 261 |         | Yes      |       0 | NO PAD        |
| utf8mb4_pl_0900_as_cs      | utf8mb4 | 284 |         | Yes      |       0 | NO PAD        |
| utf8mb4_polish_ci          | utf8mb4 | 229 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_romanian_ci        | utf8mb4 | 227 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_roman_ci           | utf8mb4 | 239 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_ro_0900_ai_ci      | utf8mb4 | 259 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ro_0900_as_cs      | utf8mb4 | 282 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_ai_ci      | utf8mb4 | 306 |         | Yes      |       0 | NO PAD        |
| utf8mb4_ru_0900_as_cs      | utf8mb4 | 307 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sinhala_ci         | utf8mb4 | 243 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sk_0900_ai_ci      | utf8mb4 | 269 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sk_0900_as_cs      | utf8mb4 | 292 |         | Yes      |       0 | NO PAD        |
| utf8mb4_slovak_ci          | utf8mb4 | 237 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_slovenian_ci       | utf8mb4 | 228 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sl_0900_ai_ci      | utf8mb4 | 260 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sl_0900_as_cs      | utf8mb4 | 283 |         | Yes      |       0 | NO PAD        |
| utf8mb4_spanish2_ci        | utf8mb4 | 238 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_spanish_ci         | utf8mb4 | 231 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_sr_latn_0900_ai_ci | utf8mb4 | 314 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sr_latn_0900_as_cs | utf8mb4 | 315 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_ai_ci      | utf8mb4 | 264 |         | Yes      |       0 | NO PAD        |
| utf8mb4_sv_0900_as_cs      | utf8mb4 | 287 |         | Yes      |       0 | NO PAD        |
| utf8mb4_swedish_ci         | utf8mb4 | 232 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_tr_0900_ai_ci      | utf8mb4 | 265 |         | Yes      |       0 | NO PAD        |
| utf8mb4_tr_0900_as_cs      | utf8mb4 | 288 |         | Yes      |       0 | NO PAD        |
| utf8mb4_turkish_ci         | utf8mb4 | 233 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_520_ci     | utf8mb4 | 246 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_unicode_ci         | utf8mb4 | 224 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vietnamese_ci      | utf8mb4 | 247 |         | Yes      |       8 | PAD SPACE     |
| utf8mb4_vi_0900_ai_ci      | utf8mb4 | 277 |         | Yes      |       0 | NO PAD        |
| utf8mb4_vi_0900_as_cs      | utf8mb4 | 300 |         | Yes      |       0 | NO PAD        |
| utf8mb4_zh_0900_as_cs      | utf8mb4 | 308 |         | Yes      |       0 | NO PAD        |
+----------------------------+---------+-----+---------+----------+---------+---------------+
```
</details>

## `_general`と`_0900`
collationの前半部分（`_general`と`_0900`）についてです。

`_general`は、Unicode文字セットの一般的な照合順序とされています。

（何が一般的なのかはよくわからない。。。）

一方で、`_0900`というのはUnicode 照合アルゴリズム (UCA) のバージョンであり、その他に`_520`などもあります。

`_0900`は、以前のバージョンに基づく照合よりも高速とされているので、特に理由がなければUCAの中では`_0900`を使えばよいかと思います。

では、`_general`と`_0900`の違いはなんでしょうか？

リファレンスマニュアルによると次のような特徴があります。

- 速度：`_general` ＞ `_0900`
- 精度：`_general` ＜ `_0900`

※リファレンスマニュアルでは、`_0900`は`_unicode`で括られています

これは`_0900`は`_general`を拡張したマッピングである（＝`_general`より`_0900`の方が区別する文字の種類が多い）ためです。
区別する文字が多い分だけ、時間がかかる代わりに精度が上がるというのは、感覚的にもしっくりきます。
（出典：[MySQL 8.0 リファレンスマニュアル 10.10.1 Unicode 文字セット](https://dev.mysql.com/doc/refman/8.0/ja/charset-unicode-sets.html)**）**

## サフィックス
| サフィックス | 意味 |
| --- | --- |
| `_ai` | アクセントを区別しない |
| `_as` | アクセントを区別する |
| `_ci` | 大文字・小文字を区別しない |
| `_cs` | 大文字・小文字を区別する |
| `_ks` | カナを区別する |
| `_bin` | バイナリ |

（出典：[MySQL 8.0 リファレンスマニュアル 10.3.1 照合の命名規則](https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-names.html)**）**

### アクセント（`_ai`、`_as`）
collation名に`_ai`、`_as`が含まれていない場合は、後述の`_ci`、`_cs`の設定に応じて適用されることになります。（`_ci`の時は`_ai`、`_cs`の時は`_as`が適用される。）

「アルファベット、日本語（ひらがな、カタカナ、漢字）にアクセントなんて関係ねーよ」と思っていましたが、日本語の清音・濁音・半濁音（`は`・`ば`・`ぱ`）はアクセントに該当していました。

### 大文字・小文字（`_ci`、`_cs`）
「`A`」と「`a`」、「`あ`」と「`ぁ`」のような、大文字・小文字を区別するか否かです。
上記のような、いわゆる大文字・小文字だけでなく、全角と半角も該当するようです。

### カナ（`_ks`）
これはそのまんま。ひらがな・カタカナを区別するか否かです。
今回は取り上げていませんが、言語固有の`_unicode`もあり日本語であれば`_ja`というサフィックスが付きます。
ちなみに`_ks`を指定できるcollationは`utf8mb4_ja_0900_as_cs_ks`のみため、`_ja`と必ずセットで使うことになります。

### バイナリ（`_bin`）
文字ごとのバイト値で区別をします。
要するに全ての文字を区別してくれます。

# 実際に検証してみる

個人的に気になった点をいくつか検証してみます。

ローカル環境を汚さずにサクッと検証するために、Dockerを使用します。
と、言いたかったのですが、実際には、日本語入力に詰まりサクッと検証できませんでした。笑

Dockerを使ってMySQLで日本語を入力する方法は以下にまとめていますので、よければこちらもご覧ください。

https://qiita.com/subun33/items/d0dd2202f14279e19312


:::note info
以下の検証において、comparison_resultが
　0：比較した文字が一致していない
　1：比較した文字が一致している
となります。
:::

## 日本語のアクセント
清音・濁音・半濁音が区別されるのかを「は」「ば」「ぱ」で確認していみます。
比較するcollationは`utf8mb4_0900_ai_ci`と`utf8mb4_0900_as_ci`です。
```sql
mysql> SELECT 'は' = 'ば' COLLATE utf8mb4_0900_ai_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'は' = 'ぱ' COLLATE utf8mb4_0900_ai_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'は' = 'ば' COLLATE utf8mb4_0900_as_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'は' = 'ぱ' COLLATE utf8mb4_0900_as_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)
```

「`は`」と「`ぱ`」「`ば`」が`_ai`では一致している（＝区別されない）、`_as`では一致していません（＝区別される）。
これで、日本語の清音・濁音・半濁音がアクセントに該当していることが確認できました。

## 全角・半角は大文字・小文字？
全角・半角が区別されるのかを「`ア`」「`ｱ`」と「`Ａ`」「`A`」で確認していみます。
比較するcollationは`utf8mb4_0900_as_ci`と`utf8mb4_0900_as_cs`です。

```sql
mysql> SELECT 'ア' = 'ｱ' COLLATE utf8mb4_0900_as_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'ア' = 'ｱ' COLLATE utf8mb4_0900_as_cs AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Ａ' = 'A' COLLATE utf8mb4_0900_as_ci AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'Ａ' = 'A' COLLATE utf8mb4_0900_as_cs AS comparison_result;
+-------------------+
| comparison_result |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)
```

「`ア`」と「`ｱ`」、「`Ａ`」と「`A`」が`_ci`では一致している（＝区別されない）、`_cs`では一致していません（＝区別される）。
これで、全角・半角が大文字・小文字に該当していることが確認できました。

# まとめ

今回は私の調べた範囲でcollationについてまとめてみました。

調べてみて感じたのは次の３点です。
- collationには命名規則があるので「collation名を見ただけで、どの文字を区別するか一目でわかる」と思いきや、公式リファレンスを見てもよくわからない。自分で検証してみるのが大事。
- 奥が深すぎる。必要に迫られなければ、これ以上踏み込んでも時間の浪費なりそう。
- `_bin`で全てを区別して、アプリ側で自分たちの仕様に合わせて実装するのがよいかも。

あれこれ調べた結果、collationに頼った実装は仕様がわかりにくくなるので、したくないなと思いました。笑