既にデータが入っているMySQLの文字セットをutf8mb3からutf8mb4に変更
はじめに
社内で利用しているアプリケーションについて、MySQLの文字セットがutf8mb3の状態でした。
記号や絵文字について使用に制限があること、MySQL側で将来的にutf8mb3が削除されると表明されていることもあり、バージョンアップを行いました。
実際に自身が行ったアクションの内容を記録しておきたいというのと、誰かの参考になれば、と思い記事を作成しました。
変更内容
MySQLの特定データベースにおける全てのテーブルおよび、データベース全体の設定に以下の変更を行いました。
-
文字セット
utf8mb3 → utf8mb4 -
照合順序
utf8mb3_general_ci → utf8mb4_general_ci
照合順序もutf8mb4_unicode_ciやutf8mb4_general_ciがあり、区別する文字が微妙に異なります。詳細はこの記事がわかりやすかったです。
今回はutf8mb4_general_ciに変更します。
環境
- Cloud SQL(Google Cloud)
- MySQL
今回の作業には直接影響しませんが、サーバーサイドのシステムはApp Engineで実装しています。
既にリレーションを組んだテーブルがあり、データも入っている状態です。
課題
- 今回変更対象のデータベースには既にリレーションを組んでいるテーブルがあり、データが入っている状態でした。したがって、そのままテーブル単位で文字セットを変更しようとすると、外部キー制約の影響で文字セット&照合順序の変更ができませんでした。
- 既存のレコードについては、今回変更する文字セット&照合順序は互換性があるため、問題なくできました。
実際のアクション
事前にクローン環境でテストし、下記のアクションリストを作成し、本番環境で実行しました。
1. メンテナンスモード
アプリケーションサイドからデータベースへのアクセスがない状態を確保しました。
2. バックアップ
バックアップを取ります。
Cloud SQLの場合インスタンス画面で「エクスポート」を選択すれば、同一プロジェクト内のCloud Storageにバックアップを作成できます。
3. データベースおよびテーブルの文字セットを確認する
下記クエリで確認できます
-- データベースの文字セットを確認
SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = '{database_name}';
-- テーブルの文字セットを確認
SELECT table_name, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.TABLES T,
information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = '{database_name}';
4. データベース内のすべての外部キーを確認する
外部キーを確認し、記録しておきます。
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_SCHEMA = '{database_name}'
AND REFERENCED_TABLE_SCHEMA = '{database_name}'
AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY
TABLE_NAME, CONSTRAINT_NAME;
5. データベース内のすべての外部キーを一度削除する
下記クエリで、データベース内の全ての外部キーを削除するクエリを取得できるため、得られたクエリを実行します。
SELECT
CONCAT('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') AS drop_statement
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_SCHEMA = '{database_name}' AND
REFERENCED_TABLE_NAME IS NOT NULL;
6. データベース内のすべてのテーブルで、文字セットを変更する
ALTER DATABASE `{database_name}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
SET @database_name = '{database_name}';
SET SESSION group_concat_max_len = 1000000;
SELECT GROUP_CONCAT(CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') SEPARATOR ' ')
INTO @alter_table_statements
FROM information_schema.TABLES
WHERE table_schema = @database_name
AND TABLE_TYPE = 'BASE TABLE';
PREPARE stmt FROM @alter_table_statements;
SELECT stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
7. データベース内のすべてのテーブルの削除した外部キーを再作成する
4.で取得した外部キー一覧より、全ての外部キーに対して、下記クエリを実行します。
ALTER TABLE {TABLE_NAME}
ADD CONSTRAINT {CONSTRAINT_NAME}
FOREIGN KEY ({COLUMN_NAME})
REFERENCES {REFERENCED_TABLE_NAME} ({REFERENCED_COLUMN_NAME});
8. データベース内のすべての外部キーを確認し、削除前のものと一致することを確認する
4.と同じクエリです。結果が一致していればOKです！
コードを表示する
-- データベースの文字セットを確認
SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = '{database_name}';
-- テーブルの文字セットを確認
SELECT table_name, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.TABLES T,
information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = '{database_name}';
9. データベースの文字セットおよび照合順序が変わっていることを確認する
3.と同じクエリです。文字セットと照合順序が変わっていればOKです！
コードを表示する
-- データベースの文字セットを確認
SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = '{database_name}';
-- テーブルの文字セットを確認
SELECT table_name, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.TABLES T,
information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema = '{database_name}';
10.データベースが正常に作動することを確認し、アプリケーションからアクセスできるようにする
終わりに
初のデータベース全体の変更を一人で実行するということでドキドキでしたが、問題なくできたのでよかったです！！
References
Discussion