この記事は MySQL Advent Calendar 2019 8 日目のエントリとなります。
（空いていたので後からエントリさせて頂きました！）
発生した問題
MySQL8.0系にて初期設定時にCollation(collation_server項目)にutf8mb4_general_ciを設定。
その後のCREATE TABLE時にDefault Charsetをutf8mb4、Collationの指定無しとした。
グローバル設定であるutf8mb4_general_ciが引き継がれるかと思いきやテーブルのCollation設定を確認するとutf8mb4_0900_ai_ciになっているという事象が発生した。
原因
CREATE TABLE時にDefault Charsetのみ指定すると上位レイヤー（MySQLインスタンスやDBスキーマ）のCollation設定に関係なく指定したCharsetに対応したデフォルトCollationがテーブルに設定される。
・MySQL8.0系でCharsetutf8mb4に対応するデフォルトCollationはutf8mb4_0900_ai_ciとなる。
MySQL Ver5系
ちなみにVer5.6やVer5.7でも同仕様となりCREATE TABLE時にCharsetだけ指定するとグローバルな設定に関係なく対応したデフォルトCollationが有効化される。
・ただしVer5系でCharsetutf8mb4に対応するデフォルトCollationはVer8.0系と異なりutf8mb4_general_ciとなる。
対策
CREATE TABLE時にDefault CharsetとCollateを両方明示的に設定する。
（Default Charset、Collationともに未指定とし上位レイヤーの設定を引き継ぐ方法も有るが明示的に指定した方が誤解も無くて良さそうという判断）
テーブルに反映されるCollation設定の優先度
| 優先度 | CREATE TABLEのDefault指定条件 | 反映値 |
|---|---|---|
| 1 | Charset、Collate両方指定 | CREATE TABLEで指定したDefault Collate |
| 2 | Charsetのみ指定 | Charset指定に対応するデフォルトCollation |
| 3 | Charset、Collateともに未指定(DBスキーマにCollation設定有り) | DBスキーマのCollation |
| 4 | Charset、Collateともに未指定(DBスキーマにCollation設定無し) | MySQLインスタンスのCollation |
参考)information_schemaに設定されたデフォルトCollationの確認
・確認クエリ
select * from information_schema.collations where is_default = 'Yes'
AND character_set_name = 'utf8mb4'
・MySQL 8.0.16の結果
COLLATION_NAME,CHARACTER_SET_NAME,ID,IS_DEFAULT,IS_COMPILED,SORTLEN,PAD_ATTRIBUTE
utf8mb4_0900_ai_ci,utf8mb4,255,Yes,Yes,0,"NO PAD"
・MySQL 5.6, 5.7の結果
COLLATION_NAME,CHARACTER_SET_NAME,ID,IS_DEFAULT,IS_COMPILED,SORTLEN
utf8mb4_general_ci,utf8mb4,45,Yes,Yes,1