MySQL5.7→8.0へのバージョンアップ対応をした際、これまで使用していたutf8mb3が非推奨になったため、utf8mb4に文字コードを変更しました。
文字コードの変更に伴い、デフォルトのcollationも以下のとおり変わっています。
utf8mb3：utf8mb3_general_ci
utf8mb4：utf8mb4_0900_ai_ci
この２つのcollationは何が違うのか？
せっかくなので、調べたことを備忘録的にまとめてみました。
目次
そもそもcollationって？
collationとは、比較対象とする2つの文字を区別するのか・しないのかを決めるルールのことです。
例えば、Appleとappleは大文字・小文字を区別するcollationであれば別の文字列として扱われますが、区別しないcollationであれば同じ文字列として扱われます。
この他、ひらがな・カタカナを区別するのか・しないのかを判定してくれるようなcollationもあります。
以下のように、特定の文字を区別するのか・しないのかで結果が変わりうるSQLはcollationの影響を受けます。
（例）
-
WHEREによるデータの絞り込み -
ORDER BYによるソート順 -
GROUP BYによるグループ化 -
SUBSTRINGによる置換対象の抽出
各collationの特徴
MySQLでは、以下の命名規則によりcollation名が決まっています。
そのため、collation名を確認するだけでどのような文字を区別しているのかが一目でわかります。
なお、使用できるcollationは次のとおりです。
collation一覧を開く
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 |
+----------------------------+---------+-----+---------+----------+---------+---------------+
_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 文字セット）
サフィックス
| サフィックス | 意味 |
|---|---|
_ai |
アクセントを区別しない |
_as |
アクセントを区別する |
_ci |
大文字・小文字を区別しない |
_cs |
大文字・小文字を区別する |
_ks |
カナを区別する |
_bin |
バイナリ |
（出典：MySQL 8.0 リファレンスマニュアル 10.3.1 照合の命名規則）
アクセント（_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で日本語を入力する方法は以下にまとめていますので、よければこちらもご覧ください。
以下の検証において、comparison_resultが
0：比較した文字が一致していない
1：比較した文字が一致している
となります。
日本語のアクセント
清音・濁音・半濁音が区別されるのかを「は」「ば」「ぱ」で確認していみます。
比較するcollationはutf8mb4_0900_ai_ciとutf8mb4_0900_as_ciです。
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です。
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に頼った実装は仕様がわかりにくくなるので、したくないなと思いました。笑