株式会社TECH LUCKという会社で代表兼エンジニアをしている齊藤です。

Railsアプリケーションで稼働しているMySQLのデータベースの文字コード（characterset）と照合順序（collation）を変更する機会があったのでまとめました。
Rails内部のソースコードまで追えていないので、実行した結果から「おそらくそうなんだろうな」という推測での記事になります。

前提

Ruby：2.7.3
Ruby on Rails：6.0.0
MySQL：8.0.32

結論

MySQL内にすでにデータベースが作成されている場合には、database.ymlのencoding、charaset、collationを変更して新しいテーブルをマイグレーションして作成しても、database.ymlでの変更はデータベース側に反映されず、すでに作成されているテーブルの文字コードと照合順序の設定が引き継がれて作成されていました。

そのため、Railsアプリケーションですでに稼働しているMySQLのデータベースの文字コードや照合順序を変更するには、MySQLのデータベース内で直接クエリを実行したり、my.cnfやAWSのパラメーターグループでの設定を変更して再起動するしかなさそうです。

動作確認（すでに稼働しているRailsアプリケーションのdatabase.ymlの設定を変更したのみの場合の挙動）

database.ymlの設定を変更する前の文字コードと照合順序の確認

Railsアプリケーションを作成した当初は、以下の設定でした。

database.yml

default: &default
  adapter: mysql2
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock
  encoding: utf8

上記の設定でデータベースの作成、マイグレーションの実行をした結果、character_setとcollationは以下のようになっていました。

照合順序（collation）

SHOW VARIABLES lIKE 'col%';

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

文字コード（characterset）

SHOW VARIABLES lIKE 'chara%';

+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
| character_sets_dir       | /~~~/   |
+--------------------------+---------+

テーブルごとの照合順序（collation）

SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';

+-------------------------------+--------------------+
| TABLE_NAME                    | TABLE_COLLATION    |
+-------------------------------+--------------------+
| users                         | utf8mb3_general_ci |
| products                      | utf8mb3_general_ci |
+-------------------------------+--------------------+

database.ymlの設定を変更した後の文字コードと照合順序

上記のようにすでにデータベースの作成、マイグレーションの実行がなされている上で、database.ymlの内容を以下のように変更しました。

database.yml

default: &default
  adapter: mysql2
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock
  encoding: utf8mb4
  charset: utf8mb4
  collation: utf8mb4_general_ci

上記のように変更した上で、データベース内で新しいテーブルを作成をしたら設定値はどうなるのか確かめるため、Prontモデルの作成とマイグレーションの実行を行いました。これによって、prontsテーブルが作成去ることになります。

ターミナル

bundle exec rails g model pront
rails db:migrate

上記の操作を行った結果、charactersetとcollationは以下のようになっていました。

照合順序（collation）

SHOW VARIABLES lIKE 'col%';

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

文字コード（characterset）

SHOW VARIABLES lIKE 'chara%';

+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
| character_sets_dir       | /~~~/   |
+--------------------------+---------+

テーブルごとの照合順序（collation）

SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';

+-------------------------------+--------------------+
| TABLE_NAME                    | TABLE_COLLATION    |
+-------------------------------+--------------------+
| users                         | utf8mb3_general_ci |
| products                      | utf8mb3_general_ci |
| pronts                        | utf8mb3_general_ci |
+-------------------------------+--------------------+

結果としては、database.yamlを変更してから新たに作成したテーブルのcollationは変更されていませんでした。
そのため、すでに稼働が始まっているデータベースの設定を変更するには、MySQLのデータベース内で直接クエリを実行するか、my.cnfやAWSのパラメーターグループでの設定を変更して再起動するしかなさそうです。

すでに稼働しているRailsアプリケーションのMySQLデータベースの文字コードと照合順序の変更方法

既存データベースのcharasetとcollationの変更

以下のクエリをMySQLデータベース内で実行して、既存データベースのcharacter_setとcollationを変更します。

ALTER DATABASE データベース名 CHARACTER SET utf8mb4 collate utf8mb4_general_ci;

既存のデータベースの中にあるテーブルのcharacter_setとcollationを変更

以下のクエリを既存のMySQLデータベース内で実行して、既存のデータベースの中にあるテーブルのcharacter_setとcollationを変更するクエリを作り出します。

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS alter_sql FROM information_schema.tables WHERE table_schema = 'データベース名';

上記のクエリで出てきた結果がクエリになっているので、出力された結果をコピーして再度クエリを実行します。

ALTER TABLEのクエリをすべてのテーブルに対して実行するので、レコードが多くある場合には数十分〜数時間などの時間がかかる可能性があります。

ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE テーブル名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
~~~

以下のクエリで変更が適用されているか確認します。

SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';

+-------------------------------+--------------------+
| TABLE_NAME                    | TABLE_COLLATION    |
+-------------------------------+--------------------+
| users                         | utf8mb4_general_ci |
| pronts                        | utf8mb4_general_ci |
+-------------------------------+--------------------+

変更が適用されているのを確認した後に、Railsのdatabase.ymlを以下のように変更します。

database.ml

default: &default
  adapter: mysql2
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock
  encoding: utf8mb4
  charset: utf8mb4
  collation: utf8mb4_general_ci

これで先ほどと同じように、新たしいテーブルを作成するために、Practiceモデルを作成し、practicesテーブルを作成して変更が適用されているのか確認します。

ターミナル

bundle exec rails g model practice
rails db:migrate

SELECT TABLE_NAME,TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='データベース名';

+-------------------------------+--------------------+
| TABLE_NAME                    | TABLE_COLLATION    |
+-------------------------------+--------------------+
| users                         | utf8mb4_general_ci |
| pronts                        | utf8mb4_general_ci |
| practices                     | utf8mb4_general_ci |
+-------------------------------+--------------------+

無事、過去のテーブルと新しく作成したテーブルの照合順序が変更されていることが確認できました。
以上です。

参考文献

圧倒的感謝！

Railsで既存のMySQLのデータベースの文字コード（characterset）と照合順序（collation）を変更する方法

前提

結論