Cloud SQLにおけるMySQLの文字セットと照合順序の変更手順

はじめに 

株式会社メンバーズの岩谷です。
私が所属するチームでは、社内に分散したデータを整理統合・活用し、社内のDXを推進していくための基盤となる業務システムを内製で企画・開発・運用をしています。

システム開発では、データベースが欠かせない存在ではありますが、設定項目が多く、運用していくのもひと苦労です。
そんな設定項目の一つに、文字セットと照合順序が存在します。文字セットと照合順序の設定を誤ると、思わぬ不具合に繋がる可能性もあります。

今回は、その文字セットと照合順序を変更した際の対応と手順について紹介していこうと思います。

環境について

• サーバー：Cloud SQL
• RDBMS：MySQL8.0.35
• 対応前の文字セット：utf8mb3
• 対応前の照合順序：utf8mb3_general_ci
• データサイズ：約90GB

経緯

これまでデータベースの文字セットには、utf8mb3を使用してきましたが、utf8mb3では絵文字や特殊文字を使用することができません。新しいプロダクトを構築していく中で、utf8mb3では扱えない文字を登録できるようにする必要性が生じたため、早急な対応が求められました。

手順

実際に利用した手順について紹介していきます。

1. 現状を知る

現状のデータベースで、文字セットと照合順序にどのような設定がされているのか、どのテーブルとカラムを変更する必要があるのか等を事前に確認します。この確認は、インスタンスレベル、データベースレベル、テーブルレベル、カラムレベルで、以下のようなSQLを用いて行います。

インスタンスレベル

SHOW GLOBAL VARIABLES LIKE 'char%';
SHOW GLOBAL VARIABLES LIKE 'collat%';

Copy

データベースレベル

SELECT schema_name, default_character_set_name, default_collation_name
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');

Copy

テーブルレベル

SELECT T.table_name, T.table_schema, 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 NOT IN ('performance_schema', 'mysql', 'sys','information_schema');

Copy

カラムレベル

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

Copy

2. 変更後の値を決める

変更後の値については、チームの方針によって変わるかと思いますが、日本語に限った話であれば、文字セットは utf8mb4、照合順序は utf8mb4_general_ci というケースが一般的のように思えます。
私が所属するチームでは、utf8mb4とutf8mb4_general_ciに変更することとしました。

3. 本番と同等のデータで検証

本番データのサイズによっては、変更に多くの時間を要する可能性があります。そのため、本番と同等のデータで「5. 変更の実施」に示す対応を行い、どれくらいの時間が掛かるか、変更時にエラーが発生しないかなどを事前に確認しておきます。

なお、Cloud SQLでは、gcloud CLIを利用して以下のようにダンプファイルのインポート及びエクスポートが可能です。ポイントとしては、エクスポート時に offload フラグを利用している点です。 offload フラグを利用することで、自動で作成される一時的なインスタンスでインポートが実行されるため、本番インスタンスへの影響を最小限にすることができます。

エクスポート

gcloud sql export sql [インスタンス名] gs://[バケット名]/[ファイル名].sql.gz \
 --database=[データベース名] --project=[プロジェクト名] --async --offload

Copy

インポート

gcloud sql import sql [インスタンス名] gs://[バケット名]/[ファイル名].sql.gz \
 --database=[データベース名] --project=[プロジェクト名] --async

Copy

4. アプリ側の設定を修正

変更対象のデータベースを利用するアプリでは、Railsを利用していたので、config ディレクトリに存在する database.yml に以下のように、collation の設定を追加しました。また、必要に応じて、encoding（文字セット）の値も修正します。 

default: &default
  ...
  collation: utf8mb4_general_ci

Copy

設定を修正後、マイグレーションのリセットを行い、スキーマファイルに collation の設定が追記されるようにしました。

5. 変更の実施

実際に変更していきます。こちらも以下のようなコマンド及びSQLを利用して、インスタンスレベル、データベースレベル、テーブルレベル、カラムレベルで変更します。

インスタンスレベル

gcloud sql instances patch [インスタンス名] \
  --database-flags=[設定するフラグを全て記載(collation_serverやcharacter_set_server等)]

Copy

データベースレベル

ALTER DATABASE [データベース名] CHARACTER SET [適用する新しい文字セット] COLLATE [適用する新しい照合順序];

Copy

テーブルレベル

以下のSQLを実行すると、テーブル毎に更新用のSQLが生成されるので、生成されたクエリをそれぞれ実行します。

SELECT  CONCAT("ALTER TABLE ", table_schema, ".", table_name, " CHARACTER SET [適用する新しい文字セット] COLLATE [適用する新しい照合順序]; ")
AS alter_table_statements
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '[データベース名]' AND table_type = 'BASE TABLE';

Copy

カラムレベル

まず、以下のSQLを実行して、カラム毎に更新用のSQLを生成します。

SELECT
  CONCAT(
    "ALTER TABLE ",
    table_schema,
    ".",
    table_name,
    " MODIFY ",
    column_name,
    " ",
    column_type,
    " CHARACTER SET [適用する新しい文字セット] COLLATE [適用する新しい照合順序]",
    IF(is_nullable = 'NO', ' NOT NULL', ''),
    IF(column_default IS NOT NULL, CONCAT(' DEFAULT ', IF(column_default = '', "''", CONCAT("'", column_default, "'"))), ''),
    IF(column_comment IS NOT NULL AND column_comment != '', CONCAT(' COMMENT ', "'", column_comment, "'"), ''),
    ";"
  )
AS alter_table_column_statements
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = '[データベース名]'
AND data_type IN ('char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext','set','enum');

Copy

次に、以下のように SHOW CREATE TABLE 文を実行して、各テーブルの詳細な列定義（NULL制約やデフォルト値など）を出力します。
前述のSQLで生成した更新用のSQLと比較して、列定義に問題がないことを確認した上で、各SQLを実行していきます。

SHOW CREATE TABLE 文を実行して列定義を確認する意図としては、列定義に誤りがあるSQLを実行すると、それがそのまま反映されてしまう為です。例えば、NULL制約が設定されているカラムに対して、NULL制約に関する記述が消えたSQLを実行してしまうと、NULL制約が消えてしまいます。こういった事態を防ぐためにも、列定義をしっかりと確認しておく必要があります。

SHOW CREATE TABLE [テーブル名];

Copy

6. 結果の確認

「1. 現状を知る」で実行したSQLを再度実行して、意図したものになっているか、不要な変更が入っていないか等を確認します。

結果

無事変更が完了し、utf8mb3では登録できなかった文字を登録することができました。
所要時間は約10分程度と、そこまで時間は掛かりませんでした。ただし、10分と言えど、その間はテーブルがロックされるため、アプリをメンテナンス状態に移行するなど、なんらかの対処が必要でしょう。

Cloud SQLの注意点

Cloud SQLで設定できる項目についてはいくつか制限があるため、予め認識しておくことが重要です。

詳しくは、公式ドキュメントに記載があるので、そちらを参照ですが、例えば、MySQL8.0から追加された default_collation_for_utf8mb4 という項目は、Cloud SQLではサポートされていません。この影響で、文字セットに utf8mb4 を使用している場合、明示的に照合順序を指定しないと、default_collation_for_utf8mb4 のデフォルト値である utf8mb4_0900_ai_ci が設定されてしまう可能性があります。utf8mb4_0900_ai_ci は、大文字・小文字、全角・半角、ひらがな・カタカナを同じものとして扱うため、注意が必要です。

また、文字セット変更時はシャドーテーブルが作成されるため、ディスク使用量を考慮する必要があります。Cloud SQLの設定で、ストレージの自動増量を有効にしておくと良いでしょう。