LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ryouzi

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

Last updated at Posted at 2023-05-09

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

DXプロジェクト、開発プロジェクト、Rails開発などでお困りごとがありましたら弊社HPからご相談をいただけますと幸いです。
以下のような問題に対応することが可能です。

  • プロジェクトでRailsエンジニアが足りなくて困っている
  • Railsのバージョンアップをしたいがノウハウ・リソースが足りなくて困っている
  • オフショア開発をしているが、要件の齟齬やコード品質が悪いので改善したい

また、Railsエンジニアも募集しておりますので、興味がありましたら弊社HPからご連絡いただけますと幸いです。

前提

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

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

参考文献

圧倒的感謝！

3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?