MySQL 8.0 はかなり長い間利用されていますが、最近、MySQL 5.7.39 を MySQL 8.0.32 にアップグレードする計画を立てています。元のデータにはさまざまな文字セットとソート規則が使用されているため、MySQL 8.0 ではデフォルトのエンジンと文字セットが InnoDB と utf8mb4 に変更されました。そのため、この機会にデータベースで使用されている文字セットとソート規則を統一することにしました。
最初は手動で変更する予定でしたが、数百のテーブルを変更するのはかなりの作業量です。そこで、次の方法をインターネットで見つけました：
1. データベースのデフォルトの文字セットとソート規則を変更する#
-- ALTER DATABASE `<データベース名>` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SELECT
CONCAT('ALTER DATABASE `', SCHEMA_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS `SQL`
FROM
information_schema.`SCHEMATA`
WHERE
-- MySQL に付属しているデータベースを除外する。ここでは、SCHEMA_NAME IN ('データベース1', 'データベース2', 'データベース3') に変更できます。
SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (DEFAULT_CHARACTER_SET_NAME != 'utf8mb4' OR DEFAULT_COLLATION_NAME != 'utf8mb4_unicode_ci')
;
2. テーブルのストレージエンジン、デフォルトの文字セット、ソート規則を変更する#
-- ALTER TABLE `<データベース名>`.`<テーブル名>` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;
SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE = InnoDB, CHARACTER SET = utf8mb4, COLLATE = utf8mb4_unicode_ci;') AS `SQL`
FROM
information_schema.`TABLES`
WHERE
-- MySQL に付属しているデータベースを除外する。ここでは、TABLE_SCHEMA IN ('データベース1', 'データベース2', 'データベース3') に変更できます。
TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (ENGINE != 'InnoDB' OR TABLE_COLLATION != 'utf8mb4_unicode_ci')
;
3. フィールドの文字セットとソート規則を変更する#
-- ALTER TABLE `<データベース名>`.`<テーブル名>`
-- MODIFY COLUMN `<フィールド名>` <フィールドの型> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '<コメント>';
SELECT
CONCAT(
-- 指定されたデータベースとテーブル名
'ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ',
-- 指定されたフィールドとデータ型
'MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
-- 指定された文字セットと対応するソート規則
' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ',
-- NULL でない場合とデフォルト値の組み合わせ
(CASE
WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NULL THEN 'NOT NULL '
WHEN IS_NULLABLE = 'NO' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NOT NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NULL THEN 'NULL DEFAULT NULL '
WHEN IS_NULLABLE = 'YES' AND COLUMN_DEFAULT IS NOT NULL THEN CONCAT('NULL DEFAULT \'', COLUMN_DEFAULT, '\' ')
END),
-- 元のコメント情報を保持する
'COMMENT \'', COLUMN_COMMENT, '\';'
) AS `SQL`
FROM
information_schema.`COLUMNS`
WHERE
-- MySQL に付属しているデータベースを除外する。ここでは、TABLE_SCHEMA IN ('データベース1', 'データベース2', 'データベース3') に変更できます。
TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND (CHARACTER_SET_NAME != 'utf8mb4' OR COLLATION_NAME != 'utf8mb4_unicode_ci')
AND DATA_TYPE IN ('varchar', 'char', 'text')
;
注意: 本番環境のデータベースを変更する場合は、アクセスが低い時間帯に SQL 文を実行してください。外部キーが関連している場合は、一時的に SET FOREIGN_KEY_CHECKS=0; を使用して外部キー制約を無効にし、実行後に SET FOREIGN_KEY_CHECKS=1; を使用して外部キー制約を有効にしてください。