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

[株式会社TECH LUCK](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)という会社で代表兼エンジニアをしている齊藤です。

DXプロジェクト、開発プロジェクト、Rails開発などでお困りごとがありましたら[弊社HP](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)からご相談をいただけますと幸いです。
以下のような問題に対応することが可能です。
- プロジェクトでRailsエンジニアが足りなくて困っている
- Railsのバージョンアップをしたいがノウハウ・リソースが足りなくて困っている
- オフショア開発をしているが、要件の齟齬やコード品質が悪いので改善したい

また、Railsエンジニアも募集しておりますので、興味がありましたら[弊社HP](https://techluck.co.jp/?utm_source=qiita&utm_medium=article&utm_campaign=myarticle)からご連絡いただけますと幸いです。

# 前提
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アプリケーションを作成した当初は、以下の設定でした。

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

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

```sql:照合順序（collation）
SHOW VARIABLES lIKE 'col%';

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


```sql:文字コード（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       | /~~~/   |
+--------------------------+---------+
```

```sql:テーブルごとの照合順序（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`の内容を以下のように変更しました。

```yaml: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テーブルが作成去ることになります。

```bash:ターミナル
bundle exec rails g model pront
rails db:migrate
```

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

```sql:照合順序（collation）
SHOW VARIABLES lIKE 'col%';

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


```sql:文字コード（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       | /~~~/   |
+--------------------------+---------+
```

```sql:テーブルごとの照合順序（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`を変更します。

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

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

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

```sql
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 = 'データベース名';
```

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

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


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

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

```sql
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`を以下のように変更します。

```yaml: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テーブルを作成して変更が適用されているのか確認します。


```bash:ターミナル
bundle exec rails g model practice
rails db:migrate
```

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

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

# 参考文献
圧倒的感謝！

https://qiita.com/nanashisan_prog/items/361e70f963fb54c53c08

https://qiita.com/hiroism/items/bee5d9dab7b8796c057c

https://zenn.dev/rinteq/articles/9769a1cd2cc60c