この記事は、hacomono Advent Calender 2023の20日目の記事です。

はじめに

こんにちは、プラットフォームチーム所属のまこたすです。

この記事は主にMySQL5.7,MySQL8.0のcollation周りの挙動の違いについて書いています。AWS RDS MySQL5.7がEOLを迎える今、一番話したい内容はRails x MySQL5.7環境からRails x MySQL8.0環境へ移行する際にハマった話とそこからみる気をつけるべき観点という話題ではあるのですが、前提の話が長いので記事を２つに分けてお伝えします。今回はRailsの話は触れず、MySQLのcollation周りの話のみをします。

この記事で書くこと

MySQL5.7, MySQL8.0でのサーバー, データベース, テーブルのcollationの決まり方とSHOW CREATE (TABLE|DATABASE)の結果の違い
- 記事内においてはutf8mb4な設定がされているMySQLという前提で話します

この記事で書かないこと

character_set_*について
Rails周りの話

TL;DR

※早見表では、挙動が変わる場合と最低限の挙動を理解するために必要な組み合わせのみを載せています。

データベースのcollation決定の早見表

MySQL5.7の場合、COLLATE指定 > collation_database の優先度でcollationが決まる
MySQL8.0の場合、COLLATE指定 > utf8mb4指定の場合のみdefault_collation_for_utf8mb4 > collation_server の優先度でcollationが決まる

テーブルのcollation決定の早見表

MySQL5.7の場合、COLLATE指定 > collation_database の優先度でcollationが決まる
MySQL8.0の場合、COLLATE指定 > utf8mb4を明示指定の場合のみdefault_collation_for_utf8mb4 > collation_database の優先度でcollationが決まる

MySQLでのcollation関係の設定値について

まずサーバー, データベースのcollationを設定するための重要なパラメーターを見ていきます。

collation_server

MySQL Serverのデフォルトcollactionの値です。サーバーの起動時にオプションで渡したり、AWS RDSだとパラメーターグループで指定ができます。以下のような特徴があります。

サーバー文字セットおよび照合順序は、データベース文字セットおよび照合順序が CREATE DATABASE ステートメントで指定されていない場合にデフォルト値として使用されます。これらにほかの用途はありません。

現在のサーバー文字セットおよび照合順序は、character_set_server および collation_server システム変数の値で判別できます。これらの変数は実行時に変更できます。 ~（公式ドキュメントより引用）~

https://dev.mysql.com/doc/refman/8.0/ja/charset-server.html

collation_database

こちらはデフォルトデータベースに対するcollationの設定で、デフォルトデータベースが切り替わる度に変数に値を設定しているようです。記事内の後にでてくる検証では触れませんが、重要なパラメーターではあるので紹介しました。

デフォルトのデータベースに対する文字セットと照合順序は、character_set_database および collation_database システム変数の値から判別できます。デフォルトのデータベースが変わるたびに、サーバーはこれらの変数を設定します。デフォルトのデータベースがない場合、変数は、character_set_server および collation_server という対応するサーバーレベルのシステム変数と同値になります。 ~（公式ドキュメントより引用）~

https://dev.mysql.com/doc/refman/8.0/ja/charset-database.html

default_collation_for_utf8mb4（MySQL8のみ）

utf8mb4が指定された際に利用されるcollationの指定です。例えば、CREATE DATABASEなどのDDLで明示的に CHARACTER SET=utf8mb4のみ指定してあった場合は、この設定値が利用されます。 https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html

MySQLのversionごとの初期値

utf8mb4の場合、以下のような初期値設定になっています。

MysqlVersion	collation_server	default_collation_for_utf8mb4
MySQL5.7	utf8mb4_general_ci	パラメーター無し
MySQL8	utf8mb4_0900_as_ci	utf8mb4_0900_ai_ci

検証

ここまでcollationを決める値やMySQL versionごとの初期値を見てきました。ここからは実際の挙動を一つずつ検証していきます。

利用するdocker-compose設定

version: '3.1'

services:
  mysql57:
    image: mysql:5.7
    command: mysqld --character-set-server=utf8mb4
    ports:
      - "3306:3306"
    environment:
      MYSQL_xxx: ...

  mysql80:
    image: mysql:8.0
    command: mysqld --character-set-server=utf8mb4
    ports:
      - "3307:3306"
    environment:
      MYSQL_xxx: ...

MySQL5.7

検証version: 5.7.44

最初にMySQL5.7の検証した内容を見ていきます。

起動時のcollationの設定

標準であるutf8mb4_general_ciになっています。

mysql> show variables like "collation_%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.06 sec)

データベースを作成・collationを確認

データべースを作成しcollationを確認しました。SHOW CREATE DATABASEに表示されているDDLをみるとcollationの情報（COLLATE utf8mb4_general_ci）は含まれていません。ただし、information_schemaから確認するとcollation_serverの値で設定されているようでした。

mysql> CREATE DATABASE test_mysql57;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_mysql57;
+--------------+--------------------------------------------------------------------------+
| Database     | Create Database                                                          |
+--------------+--------------------------------------------------------------------------+
| test_mysql57 | CREATE DATABASE `test_mysql57` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+--------------+--------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema. schemata  WHERE SCHEMA_NAME = 'test_mysql57';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.02 sec)

次にcollation_serverの値とは異なるcollationを指定し作成します。結果、指定したcollationで作成されました。また今回はcollationの情報がSHOW CREATEの結果に含まれています。

mysql> CREATE DATABASE test_mysql57_2 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)

mysql>  SHOW CREATE DATABASE test_mysql57_2;
+----------------+-------------------------------------------------------------------------------------------------------+
| Database       | Create Database                                                                                       |
+----------------+-------------------------------------------------------------------------------------------------------+
| test_mysql57_2 | CREATE DATABASE `test_mysql57_2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema.schemata  WHERE SCHEMA_NAME = 'test_mysql57_2';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_unicode_ci     |
+------------------------+
1 row in set (0.00 sec)

次にCREATE文で明示的にcollation_serverの値を指定して実行しました。結果、collation・SHOW CREATEの両方が最初と同じ結果になりました。このことからMySQL5.7では、標準であるutf8mb4_general_ciと同じ場合は、collationの情報は省略する挙動になりそうと推測できます。

mysql> CREATE DATABASE test_mysql57_3 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql>  SHOW CREATE DATABASE test_mysql57_3;
+----------------+----------------------------------------------------------------------------+
| Database       | Create Database                                                            |
+----------------+----------------------------------------------------------------------------+
| test_mysql57_3 | CREATE DATABASE `test_mysql57_3` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------------+----------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema.schemata  WHERE SCHEMA_NAME = 'test_mysql57_3';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.01 sec)

一つ前の推測があっているか確認するために、MySQLのcollation_serverをutf8mb4_unicode_ciへ変更し、再起動後にテーブルを作成するとutf8mb4_unicode_ciのcollationの情報はSHOW CREATEの結果に含まれていました。推測はあってそうにみえます。

mysql> show variables like "collation_%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.07 sec)

mysql> CREATE DATABASE test_mysql57_4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_mysql57_4;
+----------------+-------------------------------------------------------------------------------------------------------+
| Database       | Create Database                                                                                       |
+----------------+-------------------------------------------------------------------------------------------------------+
| test_mysql57_4 | CREATE DATABASE `test_mysql57_4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

テーブルを作成・collationを確認

テーブルを作成する際に利用されるcollationは、データベースを作成した際に設定したcollationが利用されます。その前提で、前の手順で作成したtest_mysql57データベース（utf8mb4_general_ci）を利用してテーブル作成時に設定されるcollationについてみていきます。

まず最初にテーブルをcollation指定無しで作成します。この場合collation_databaseの値がそのまま利用されました。こちらもSHOW CREATEの結果にcollationの情報は含まれていません。

mysql> use test_mysql57;
Database changed
mysql> CREATE TABLE test_mysql57_table1 (id int(11));
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE test_mysql57_table1;
+---------------------+----------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                             |
+---------------------+----------------------------------------------------------------------------------------------------------+
| test_mysql57_table1 | CREATE TABLE `test_mysql57_table1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------------------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'test_mysql57' AND table_name = 'test_mysql57_table1';
+---------------------+--------------------+
| table_name          | table_collation    |
+---------------------+--------------------+
| test_mysql57_table1 | utf8mb4_general_ci |
+---------------------+--------------------+
1 row in set (0.01 sec)

次にcollation_databaseの値とは異なるcollation指定で実行します。結果、指定されたcollationが適用されました。またcollationの情報も結果に含まれていました。

mysql> use test_mysql57;
Database changed
mysql> CREATE TABLE test_mysql57_table2 (id int(11)) DEFAULT COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE test_mysql57_table2;
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                        |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------+
| test_mysql57_table2 | CREATE TABLE `test_mysql57_table2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'test_mysql57' AND table_name = 'test_mysql57_table2';
+---------------------+--------------------+
| table_name          | table_collation    |
+---------------------+--------------------+
| test_mysql57_table2 | utf8mb4_unicode_ci |
+---------------------+--------------------+
1 row in set (0.01 sec)

MySQL5.7はSHOW CREATEで表示される結果が変わることを除いて、かなりシンプルな挙動で動作してくれることがわかりました。

MySQL8.0

検証version: 8.0.35

次にMySQL8.0での挙動を見ていきます。先に一部結論になりますが、基本的な挙動はMySQL5.7と同じになるので重複する検証は省きます。ただしMySQL8.0の場合は、collation_serverまたはcollation_databaseの値がdefault_collation_for_utf8mb4と違う場合にMySQL5.7の場合と異なる挙動をするので、そのケースのみ検証を行っていきます。

起動時のcollationの設定

標準であるutf8mb4_0900_ai_ciで起動しています。

mysql> show variables like "collation_%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.03 sec)

mysql> show variables like "default_collation_%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
1 row in set (0.01 sec)

default_collation_for_utf8mb4をutf8mb4_general_ciへ変更

このパラメーターはSET PERSISTで設定することができ、DB再起動後も値を保持することができるパラメーターになります。

注意したい点としてはAWS RDSを利用している場合です。RDSではSET PERSISTをサポートしていません。またパラメーターグループでもdefault_collation_for_utf8mb4の変更ができないため、基本はutf8mb4_0900_ai_ciの値で固定になるかとおもいます。

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Concepts.FeatureSupport.html

mysql> SET PERSIST default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show variables like "default_collation_%";
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+
1 row in set (0.01 sec)

データベースを作成・collationを確認

まずはcollation, charset未指定パターンです。結果としてはcollation_serverの値がそのまま使われています。またSHOW CREATEの結果には標準のcollationであったとしても明示的に出力されるようになりました。 CHARACTER SET utf8mb4という記述はありますが、このケースではdefault_collation_for_utf8mb4の値が適用されないようでした。

mysql> CREATE DATABASE test_mysql8_1;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_mysql8_1;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database      | Create Database                                                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| test_mysql8_1 | CREATE DATABASE `test_mysql8_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema. schemata  WHERE SCHEMA_NAME = 'test_mysql8_1';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.01 sec)

次にCHARACTER SET utf8mb4を明示的にしてみます。結果、default_collation_for_utf8mb4の値が適用されるようになりました。このパラメーターは、charsetでutf8mb4を明示的に渡した場合のみ適用されるようです。

mysql> CREATE DATABASE test_mysql8_2 CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_mysql8_2;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database      | Create Database                                                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| test_mysql8_2 | CREATE DATABASE `test_mysql8_2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema. schemata  WHERE SCHEMA_NAME = 'test_mysql8_2';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+
1 row in set (0.01 sec)

最後にCHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ciを指定してみます。結果、明示的に渡したutf8mb4_0900_ai_ciの値が適用されました。また今回もSHOW CREATEの結果にはcollationの値がでていることから、MySQL8.0では必ず含まれるようになったのかもしれません。

mysql> CREATE DATABASE test_mysql8_3 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_mysql8_3;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Database      | Create Database                                                                                                                         |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| test_mysql8_3 | CREATE DATABASE `test_mysql8_3` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DEFAULT_COLLATION_NAME FROM  information_schema. schemata  WHERE SCHEMA_NAME = 'test_mysql8_3';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_0900_ai_ci     |
+------------------------+
1 row in set (0.01 sec)

テーブルを作成し、collationを確認

前の手順で作成したtest_mysql8_1データベース（utf8mb4_0900_ai_ci）を利用してテーブル作成時に設定されるcollationについてみていきます。 default_collation_for_utf8mb4の値は、utf8mb4_general_ciのままです。検証パターンはデータベースの時と同じでためしましたが、結果も同じになりました。

mysql> use test_mysql8_1;
Database changed

mysql> CREATE TABLE test_mysql8_1_table1 (id int(11));
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>  SHOW CREATE TABLE test_mysql8_1_table1;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                     |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
| test_mysql8_1_table1 | CREATE TABLE `test_mysql8_1_table1` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'test_mysql8_1' AND table_name = 'test_mysql8_1_table1';
+----------------------+--------------------+
| TABLE_NAME           | TABLE_COLLATION    |
+----------------------+--------------------+
| test_mysql8_1_table1 | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test_mysql8_1_table2 (id int(11)) DEFAULT CHARACTER SET utf8mb4;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>  SHOW CREATE TABLE test_mysql8_1_table2;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                     |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
| test_mysql8_1_table2 | CREATE TABLE `test_mysql8_1_table2` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'test_mysql8_1' AND table_name = 'test_mysql8_1_table2';
+----------------------+--------------------+
| TABLE_NAME           | TABLE_COLLATION    |
+----------------------+--------------------+
| test_mysql8_1_table2 | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test_mysql8_1_table3 (id int(11)) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> SHOW CREATE TABLE test_mysql8_1_table3;
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                     |
  +----------------------+----------------------------------------------------------------------------------------------------------------------------------+
  | test_mysql8_1_table3 | CREATE TABLE `test_mysql8_1_table3` (
  `id` int DEFAULT NULL
                           ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------------------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT table_name, table_collation FROM information_schema.tables WHERE table_schema = 'test_mysql8_1' AND table_name = 'test_mysql8_1_table3';
+----------------------+--------------------+
| TABLE_NAME           | TABLE_COLLATION    |
+----------------------+--------------------+
| test_mysql8_1_table3 | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
1 row in set (0.01 sec)

検証によってMySQL8.0では、COLLATE utf8mb4の指定がある場合のみdefault_collation_for_utf8mb4の値に注意して見ていく必要があることがわかりました。

検証を経ての雑感

検証結果のまとめは、冒頭のTL;DRにまとめました。以下検証を通しての雑感になります。

実際の開発/運用では、MySQL5.7（またはそれ以前）な環境においてMySQL側がもっているデフォルトの値に任せて明示的に指定しないということは往々に起こる気がしている
MySQL5.7ではutf8mb4_general_ciの場合はcollationが省略される挙動だったので、SHOW CREATEの結果に依存したなんらかの仕組みを作っていた場合には、MySQL8.0では意図しない挙動になってしまい問題が発生するなどありそう
複数のcollationの設定が混ざってしまうとSQLが期待しない結果になることが多々あるので注意が必要になる。MySQL5.x -> 8.0への移行の際には、現在のCOLLATEの指定を確認して、移行後どんな値を選択したいのかを決め影響範囲を一つずつ確認していく必要がありそう