MySQLの照合順序の設定をそろそろちゃんと整理しておく

この記事はアイスタイル [Advent Calendar 2023](https://qiita.com/advent-calendar/2023/istyle) 21日目の記事です。

みなさんおはようございます、こんにちは、こんばんはアイスタイルでDBREをやっている
@iwasakikです。
現在アイスタイルでは、オンプレミスで稼働しているレガシーシステムのクラウド化を行う開発基盤Rebornというプロジェクトが進行中です。
その中で私はオンプレデータベースのクラウド移行を担当しております。

特に、オンプレMySQLをAmazon Aurora MySQLへ移行する対応が多かったので、データベース移行時に気にしてほしいMySQLの照合順序に関する記事を書くことにしました。
あと、照合順序周りの設定についてよくドキュメントを見返すことが多いので、自分の中でもちゃんと整理しておこうと思い、記事のテーマにさせていただきました。

これまでにもMySQLに関する記事をいくつか書いているので、よかったら見てください！

https://techblog.istyle.co.jp/archives/6530

https://techblog.istyle.co.jp/archives/9097

https://qiita.com/iwasakik/items/ea7b3983cc1ed9abee2b

# 照合順序とは
リレーショナルデータベース内で文字列の比較や並べ替えを行う際のルールを照合順序と呼びます。
大文字と小文字、半角と全角、日本語であればひらがなとカタカナなど、文字列の比較やソートを行う際にこれらをどう区別するかのルールを要件に応じて設定できるというものです。

mysqlで文字セットutf8mb4の照合順序を表す場合、下記のようなルールになります。

 <table>
    <tr>
      <td>utf8mb4</td>
      <td>文字コード</td>
    </tr>
 </table>

\+

 <table>
    <tr>
      <td>bin</td>
      <td>コードのまま</td>
    </tr>
    <tr>
      <td>general</td>
      <td>MySQL独自規則</td>
    </tr>
    <tr>
      <td>unicode</td>
      <td>Unicode 4.0.0</td>
    </tr>
    <tr>
      <td>unicode_0520</td>
      <td>Unicode 5.2.0</td>
    </tr>
    <tr>
      <td>0900</td>
      <td>Unicode 9.0.0</td>
    </tr>
    <tr>
      <td>ja_0900</td>
      <td>Unicode 9.0.0 + 日本語</td>
    </tr>
 </table>

\+

 <table>
    <tr>
      <td>ai</td>
      <td>Accent Insensitive</td>
      <td>アクセント違いを区別しない</td>
    </tr>
    <tr>
      <td>as</td>
      <td>Accent Sensitive</td>
      <td>アクセント違いを区別する</td>
    </tr>
    <tr>
      <td>ci</td>
      <td>Case Insensitive</td>
      <td>大文字小文字を区別しない</td>
    </tr>
    <tr>
      <td>cs</td>
      <td>Case Sensitive</td>
      <td>大文字小文字は区別する</td>
    </tr>
    <tr>
      <td>ks</td>
      <td>Kana Sensitive</td>
      <td>カタカナひらがなを区別する</td>
    </tr>
 </table>

※Insensitive：鈍感  , Sensitive：敏感

# MySQLの照合順序の設定レベルを整理
MySQLの照合順序はサーバーレベル、データベースレベル、テーブルレベル、カラムレベル、文字列リテラルレベルで設定が可能です。
それぞれの設定方法について簡潔説明すると下記の通りとなります。


| 設定レベル | どうやって"明示的に"設定するか |
| --- | --- |
| サーバーレベル | collation_server システム変数に指定 |
| データベースレベル　　| CREATE DATABASE および ALTER DATABASE ステートメントのオプションとして指定 |
| テーブルレベル | CREATE TABLE および ALTER TABLE ステートメントのオプションとして指定 | 
| カラムレベル | CREATE TABLE および ALTER TABLE のカラム定義構文のオプションとして指定 | 
| 文字リテラルレベル | collation_connection システム変数に指定 |

# 文字セットとデフォルト照合順序について
アルファベットやひらがなやカタカナ、漢字などのあらゆる文字列データは文字とそれに対応する数字（エンコーディング）を持っています。
例えば、「A」という文字データは「A」という記号と「0001」という数字（エンコーディング）の組み合わせでできているというような形です（あくまで表現上の例です）。
この記号とエンコーディングの組み合わせをまとめたルールブックを文字セットと呼びます。
（3行で「文字セット」を表現してみましたが、詳細はもっと奥が深いですw）

![WS000549.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/893161/d1132224-1e72-ac07-f1ea-1e339a188941.jpeg)



MySQLでは、さまざまな言語や地域に対応するため複数の文字セットを持っています。

そして、文字セットにはデフォルトの照合順序というものが存在します。
照合順序を明示的に設定しなくても、文字セットのデフォルトの照合順序が設定されるようになっているという具合です。

たとえば、サーバレベルで`utf8mb4`という文字セットを設定した場合、照合順序を明示的に設定しなくても照合順序は`utf8mb4_0900_ai_ci`になります
また、サーバレベルで`latin1`という文字セットを設定した場合、照合順序を明示的に設定しなくても照合順序は`latin1_swedish_ci`になります

各文字セットのデフォルト照合順序は`INFORMATION_SCHEMA COLLATIONS`テーブルおよび`SHOW COLLATION`ステートメントで調べることができ、`SHOW COLLATION`ステートメントを利用した場合は下記のようにDefaultカラムがYesとなっているものがデフォルト照合順序となります。

### 文字セットutf8mb4のデフォルト照合順序

```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        |
```

→DefaultカラムがYesとなっている`utf8mb4_0900_ai_ci`がデフォルト照合順序

### 文字セットlatin1のデフォルト照合順序
```sql:
mysql> SHOW COLLATION WHERE Charset = 'latin1';
+-------------------+---------+----+---------+----------+---------+---------------+
| Collation         | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-------------------+---------+----+---------+----------+---------+---------------+
| latin1_bin        | latin1  | 47 |         | Yes      |       1 | PAD SPACE     |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 | PAD SPACE     |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 | PAD SPACE     |
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 | PAD SPACE     |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 | PAD SPACE     |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 | PAD SPACE     |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 | PAD SPACE     |
```

→DefaultカラムがYesとなっている`latin1_swedish_ci`がデフォルト照合順序

# 照合順序の影響力と設定時の注意点について
サーバーレベル、データベースレベル、テーブルレベル、カラムレベルの照合順序は設定レベルのスコープが詳細になるほど強い影響力を持ちます。

つまり、下記のようにより右に設定されたレベルの照合順序が左のパラメータをオーバーライドするような形になります。

### 照合順序の影響力の強さ

:::note info
**サーバーレベル** ＜ **データベースレベル** ＜ **テーブルレベル** ＜ **カラムレベル**
:::

ちなみにmysqlの公式ドキュメントには、データベースレベルで照合順序が設定されていない場合しかサーバーレベルの設定は利用されず、それ以外の使い道が無いため「これらにほかの用途はありません。」と明記されていました。


> サーバー文字セットおよび照合順序は、データベース文字セットおよび照合順序が CREATE DATABASE > ステートメントで指定されていない場合にデフォルト値として使用されます。 これらにほかの用途はありません。
参考: [MySQL 8.0 リファレンスマニュアル - 10.3.2 サーバー文字セットおよび照合順序](https://dev.mysql.com/doc/refman/8.0/ja/charset-server.html)


ただ、ここで注意しなければならない事があります。
サーバーレベルもしくはデータベースレベルの文字セットを`utf8mb4`に設定して、テーブルレベル、カラムレベルの照合順序を明示的に指定せずにテーブルを作成すると、その上位のレベルであるデータベースレベル、サーバーレベルにデフォルトの`utf8mb4_0900_ai_ci`以外の照合順序を設定したとしてもカラムレベルの照合順序は`utf8mb4_0900_ai_ci`になります。

具体的に説明すると
```sql:
mysql> SELECT @@character_set_server, @@collation_server;
+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_bin        |
+------------------------+--------------------+
1 row in set (0.00 sec)
```
↑サーバーレベルの文字セット：`utf8mb4`、照合順序：`utf8mb4_bin`の設定のMySQLサーバーにおいて、
```sql:
mysql> CREATE DATABASE `sakila` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.01 sec)
```
↑データベースレベルの文字セット：`utf8mb4`、照合順序：`utf8mb4_bin`の設定でデータベースを作成し、

```sql:
mysql> use sakila
mysql> CREATE TABLE actor (
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
↑テーブルレベルの文字セット：`utf8mb4`の設定で、照合順序を明示的に指定せず（COLLATE句を書かずに）テーブルを作成すると、
```sql:
mysql> select table_name, column_name, collation_name from information_schema.columns  where table_schema="sakila" and collation_name is not null;
+------------+-------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME     |
+------------+-------------+--------------------+
| actor      | first_name  | utf8mb4_0900_ai_ci |
| actor      | last_name   | utf8mb4_0900_ai_ci |
+------------+-------------+--------------------+
2 rows in set (0.01 sec)
```
↑文字列カラムの照合順序はデータベースレベルの照合順序を引き継がず、`utf8mb4`のデフォルト照合順序である`utf8mb4_0900_ai_ci`になりました。
```sql:
mysql> use sakila
mysql> CREATE TABLE actor2 (
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```
↑テーブルレベルまで明示的に照合順序を`utf8mb4_bin`に設定すると、
```sql:
mysql> select table_name, column_name, collation_name from information_schema.columns  where table_schema="sakila" and collation_name is not null;
+------------+-------------+----------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+------------+-------------+----------------+
| actor2     | first_name  | utf8mb4_bin    |
| actor2     | last_name   | utf8mb4_bin    |
+------------+-------------+----------------+
2 rows in set (0.01 sec)
```
↑文字列カラムの照合順序はutf8mb4_binになります。
```sql:
mysql> use sakila
mysql> CREATE TABLE actor3 (
  first_name VARCHAR(45) COLLATE utf8mb4_bin NOT NULL,
  last_name VARCHAR(45) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
↑もしくはテーブルレベルで照合順序を設定せず、カラムレベルで照合順序を`utf8mb4_bin`に設定した場合でも、
```sql:
mysql> select table_name, column_name, collation_name from information_schema.columns  where table_schema="sakila" and collation_name is not null;
+------------+-------------+----------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+------------+-------------+----------------+
| actor3     | first_name  | utf8mb4_bin    |
| actor3     | last_name   | utf8mb4_bin    |
+------------+-------------+----------------+
2 rows in set (0.00 sec)
```
↑文字列カラムの照合順序は`utf8mb4_bin`になります。

つまり何が言いたいかといいますと、MySQLバージョン8.0系では文字セットutf8mb4の設定においてはサーバーレベル、データベースレベルでいかにデフォルト照合順序である`utf8mb4_0900_ai_ci`以外の照合順序を設定したとしても、
テーブルレベル、カラムレベルで照合順序を明示的に設定しないと、カラムレベルの照合順序は文字セット`utf8mb4`のデフォルト照合順序である`utf8mb4_0900_ai_ci`になってしまうという事です。

# default_collation_for_utf8mb4について
上記のような設定になるのは、MySQL 8.0.11で追加された`default_collation_for_utf8mb4`というmysqlのシステム変数が原因です。

MySQL 5.7 の`utf8mb4`のデフォルトの照合順序は`utf8mb4_general_ci`ですが、MySQL 8.0 では `utf8mb4_0900_ai_ci`に変わりました。
`default_collation_for_utf8mb4`は`utf8mb4_0900_ai_ci`もしくは`utf8mb4_general_ci`を設定でき、MySQL5.7からMySQL8.0へレプリケーションするような状況においてもレプリカがソースのデータを正しく処理する事ができるよう、MySQL8.0においてもデフォルト照合順序を`utf8mb4_general_ci`に設定できるためにあるシステム変数です。

MySQL8.0の公式ドキュメントによると`default_collation_for_utf8mb4`システム変数に設定された値は下記の場合に利用されます。

> デフォルトの utf8mb4 照合は、次のステートメントで使用されます:
> 
> ・SHOW COLLATION および SHOW CHARACTER SET。
> ・COLLATION 句のない CHARACTER SET utf8mb4 句を持つ CREATE TABLE および ALTER TABLE(テーブルの文字セットまたはカラムの文字セット用)。
> ・COLLATION 句のない CHARACTER SET utf8mb4 句を持つ CREATE DATABASE および ALTER DATABASE。
> ・COLLATE 句のない_utf8mb4'some text'形式の文字列リテラルを含むステートメント。
参考: [MySQL 8.0 リファレンスマニュアル - サーバーシステム変数（default_collation_for_utf8mb4）](https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_default_collation_for_utf8mb4)


2番目と3番目の・に着目しましょう。
つまり、COLLATION句を明示的に指定せずに、CREATE TABLEやCREATE DATABASEでテーブルやデータベースを作成した場合、照合順序はより上位レベルの設定値を引き継ぐのではなく、`default_collation_for_utf8mb4`システム変数に設定された照合順序が設定されるということになります。

# クラウド移行で困った事
実際にこの照合順序の設定がクラウド移行においてどう影響したかを紹介します。
これまでアイスタイルのrebornプロジェクトにおいて、DBREチームは数十台のオンプレミスMySQLを`Amazon Aurora Version 3（MySQLバージョン8.0.X互換）`に移行してきました。
オンプレMySQLには様々なバージョンがあり、その多くはMySQL5.7系のバージョンが利用されていました。

そして、MySQL5.7系のオンプレMySQLを`Amazon Aurora Version 3`に移行する際に困ったことが起きました。

基本的にmysqlどうしのデータ移行を行う際、mysqldumpを利用してデータ移行を行います。
MySQL5.7のオンプレサーバー上で移行元データベースをmysqldumpで出力し、`Amazon Aurora Version 3`へリストアするという流れになります。

コマンド的にはこんな感じです（必要に応じてオプションなどは変更しています）
```sql:
# mysqldump
$ mysqldump -u <ユーザー名> -p --hex-blob --single-transaction --routines=0 --triggers=0 --events=0 --set-gtid-purged=off --databases <データベース名> > <出力先ディレクトリ>/<dumpファイル名>.sql

# リストア
$ mysql -h <移行先Auroraライターエンドポイント> -u <ユーザー名> -p < <出力先ディレクトリ>/<dumpファイル名>.sql
```

MySQL5.7で文字セット`utf8mb4`を利用し、照合順序をサーバーレベル、データベースレベル、テーブルレベル、カラムレベルのどのレベルにも設定していないデータベースを`Amazon Aurora Version 3`に移行する際に問題が起きました。

dumpの内容はサンプルですがMySQL5.7のmysqldumpで下記のように照合順序を明示的に記載しないテーブル定義として出力されたdumpファイルを`Amazon Aurora Version 3`へリストアしました。
```sql:
-- MySQL dump 10.13  Distrib 5.7.44, for Linux (x86_64)
～
<略>
～
--
-- Current Database: `sakila`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `sakila`;

--
-- Table structure for table `actor`
--
～
<略>
～
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
～
<略>
～
-- Dump completed on 2023-12-18  7:37:15
```
リストアした結果、カラムレベルの照合順序が移行元のMySQL5.7では`utf8mb4_general_ci`だったものが移行先の`Amazon Aurora Version 3`ではすべて`utf8mb4_0900_ai_ci`になってしまっていたのです。

 - 移行元であるMySQL5.7のカラムレベル照合順序
```sql:
mysql> select table_name, column_name, collation_name from information_schema.columns  where table_schema="sakila" and collation_name is not null;
+------------+-------------+--------------------+
| table_name | column_name | collation_name     |
+------------+-------------+--------------------+
| actor      | first_name  | utf8mb4_general_ci |
| actor      | last_name   | utf8mb4_general_ci |
+------------+-------------+--------------------+
2 rows in set (0.00 sec)
```
 - 移行先であるAmazon Aurora Version 3（MySQLバージョン8.0.X互換）のカラムレベル照合順序
```sql:
mysql> select table_name, column_name, collation_name from information_schema.columns  where table_schema="sakila" and collation_name is not null;
+------------+-------------+--------------------+
| TABLE_NAME | COLUMN_NAME | COLLATION_NAME     |
+------------+-------------+--------------------+
| actor      | first_name  | utf8mb4_0900_ai_ci |
| actor      | last_name   | utf8mb4_0900_ai_ci |
+------------+-------------+--------------------+
2 rows in set (0.01 sec)
```
`utf8mb4_0900_ai_ci`への変換なんて行っていないのにMySQL5.7系からMySQL8.0系にデータ移行したら意図せず`utf8mb4_general_ci`から`utf8mb4_0900_ai_ci`へ変わってしまったのです。
原因は前述したとおり、`default_collation_for_utf8mb4`によるもので、下記に抵触してしまったためCOLLATION 句のない CHARACTER SET utf8mb4 句を持つ CREATE TABLEはMySQL8.0系互換の`Amazon Aurora Version 3`では`utf8mb4_0900_ai_ci`に変換されてしまったのです。

> デフォルトの utf8mb4 照合は、次のステートメントで使用されます:
> 
> ・COLLATION 句のない CHARACTER SET utf8mb4 句を持つ CREATE TABLE および ALTER TABLE(テーブルの文字セットまたはカラムの文字セット用)。
参考: [MySQL 8.0 リファレンスマニュアル - サーバーシステム変数（default_collation_for_utf8mb4）](https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html#sysvar_default_collation_for_utf8mb4)

# MySQL5.7のmysqldumpをAmazon Aurora Version 3（MySQLバージョン8.0.X互換）へリストアする際に照合順序を合わせるためには

ここでは、文字セット`utf8mb4`においてMySQL5.7から`Amazon Aurora Version 3（MySQLバージョン8.0.X互換）`へmysqldumpでリストアするにあたって照合順序の設定を合わせる方法をご紹介します。

まず、下記のように移行元MySQL5.7において全てのテーブルのテーブルレベルもしくはカラムレベルでデフォルト照合順序以外の照合順序（`utf8mb4_bin`など）を明示的に指定されている場合は、mysqldumpにはテーブルレベル、カラムレベルで照合順序が記載されるため、そのままリストアいただいて問題ありません。


```sql:
mysql> show create table sakila.actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) COLLATE utf8mb4_bin NOT NULL,
  `last_name` varchar(45) COLLATE utf8mb4_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
```

下記のように移行元MySQL5.7においてデフォルトの照合順序（`utf8mb4_general_ci`）を利用している場合、mysqldumpのテーブルのCREATE文に明示的に照合順序が明記されません。

```sql:
mysql> show create table sakila.actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```

mysqldumpで出力すると、CREATE TABLE文に明示的に照合順序が明記されません。
```sql:
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
この場合、mysqldumpファイルをそのまま`Amazon Aurora Version 3`へリストアするとリストア先では`default_collation_for_utf8mb4`システム変数によってテーブル、カラムレベルの照合順序は`utf8mb4_general_ci`から`utf8mb4_0900_ai_ci`に変わってしまいます。

`Amazon Aurora Version 3`へリストア後のテーブル定義が下記の通りです。
```sql:
mysql> show create table sakila.actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
```

この照合順序の変更が意図しない変更だった場合、サービスに影響が出る可能性がありますので`Amazon Aurora Version 3`でもMySQL5.7からのデータ移行でデフォルト照合順序である`utf8mb4_general_ci`を利用する方法の一例を紹介します。

### 解決策の一例：default_collation_for_utf8mb4で設定されているデフォルト照合順序変更してデータ移行する

**オンプレMySQL8.0**の場合は、下記のクエリを実行して`default_collation_for_utf8mb4`を`utf8mb4_general_ci`に設定したうえでMySQL5.7デフォルト照合順序`utf8mb4_general_ci`のmysqldumpファイルをリストアすれば、移行後も引き続き`utf8mb4_general_ci`の設定を行う事ができます。
```sql:オンプレMySQLの場合：default_collation_for_utf8mb4の変更
SET GLOBAL default_collation_for_utf8mb4=utf8mb4_general_ci;
```

```sql:リストア後のテーブル定義
mysql> show create table sakila.actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL,
  `last_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
```

**Aurora MySQL**の場合、オンプレMySQL8.0と同様にSET GLOBALでパラメータを変更しようとすると管理者ユーザーであっても権限の壁に阻まれ設定できません。

```sql:
aurora > SET GLOBAL default_collation_for_utf8mb4=utf8mb4_general_ci;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
```
Auroraだったらパラメータグループを修正すればいいと考えたいところですが、DBクラスターパラメータグループにもDBインスタンスパラメータグループにも`default_collation_for_utf8mb4`というパラメータは見当たりません。。
 - Auroraクラスターパラメータグループで確認
![WS000547.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/893161/26b9947c-101a-9f98-441c-d2bdee8e28e4.jpeg)


現状取り得る方法としては、下記のようにAuroraへmysqlログイン後にSET SESSION句で`default_collation_for_utf8mb4`を`utf8mb4_general_ci`に変更して、テーブルレベルに明示的に照合順序が明記されていないMySQL5.7のデフォルト照合順序`utf8mb4_general_ci`のmysqldumpファイルをリストアすることで、移行後も引き続き`utf8mb4_general_ci`設定を行う事ができます。
```sql:SET SESSION句でdefault_collation_for_utf8mb4をutf8mb4_general_ciに変更
aurora > SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Warning (Code 1681): Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
```
```sql:mysqldumpファイルのリストア
aurora > source <ディレクトリ名>/<mysqldumpファイル名>.sql
```

# まとめ
mysqlの照合順序は、サーバーレベル、データベースレベル、テーブルレベル、カラムレベルといった様々な設定箇所があるため、設定内容によって、どのような反映のされ方をするかちゃんと理解しておく必要があります。
さらにmysql8.0では、（設定時のWARNINGを見るといずれ利用できなくなるようですが）`default_collation_for_utf8mb4`というシステム変数が追加された事で、設定方法が一筋縄でいかなくなりました。
```sql:
Warning (Code 1681): Updating 'default_collation_for_utf8mb4' is deprecated. It will be made read-only in a future release.
```

また、この記事では**データ移行時**の照合順序の設定にフォーカスしましたが、通常のCREATE TABLEで任意の照合順序を使いたい場合も、`default_collation_for_utf8mb4`が悪影響を及ぼす場合があるので、CREATE TABLEする際は照合順序を明示的に指定しましょう。

mysqlの照合順序の設定で想定外の挙動にならないように、この記事が少しでも役に立って頂けると幸いです。
# 終わりに
今回この記事を書くにあたって改めてmysqlの公式ドキュメントの文字セット・照合順序の章を改めて読み込んだら、今まで曖昧だったと感じていた部分も整理できて勉強にもなったので良かったなと思いました。

改めて情報発信することの大切さを実感できたとともに、アドベントカレンダーという機会を設けていただけた運営の方々に感謝申し上げます。

# 参考文献

- [MySQL 8.0 リファレンスマニュアル - 10.3.9 文字セットと照合順序の割り当ての例](https://dev.mysql.com/doc/refman/8.0/ja/charset-examples.html)
- [MySQL道普請便り - 第157回 MySQLのデフォルトcollationの注意点](https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0157)