latin1 / utf8mb3 テーブルを utf8mb4 に変換する

コマンド

-- Columns that are not utf8mb4
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND CHARACTER_SET_NAME <> 'utf8mb4'
  AND TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Generate conversion DDL for tables whose default charset is not utf8mb4
SELECT CONCAT('ALTER TABLE `', t.TABLE_SCHEMA, '`.`', t.TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;') AS ddl
FROM information_schema.TABLES t
JOIN information_schema.COLLATIONS c ON c.COLLATION_NAME = t.TABLE_COLLATION
WHERE c.CHARACTER_SET_NAME <> 'utf8mb4' AND t.TABLE_TYPE = 'BASE TABLE'
  AND t.TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema');

-- Schema default and a single table
ALTER DATABASE legacy CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE legacy.cust CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=COPY, LOCK=SHARED;

概要

utf8mb4 でない列とテーブルを特定し、変換 DDL を生成する。CONVERT TO はテーブルのデフォルトとすべての文字列を変更し、絵文字 / 4 バイト文字を使用可能にする。

使用する場面

5.x から移行したスキーマ、'Incorrect string value' (1366) エラーの発生時、照合順序の異なる列同士の結合でインデックスが使えない場合に。

注意

CONVERT TO は COPY でテーブルを書き換え、その間書き込みをブロックする (INPLACE はサポートされない)。大きなテーブルでは同じ --alter を pt-online-schema-change / gh-ost で実行する。utf8mb4 は 1 文字あたり 4 バイトを使用するため、インデックス付きの VARCHAR(255) は 1020 バイトになる。上限は DYNAMIC / COMPRESSED 行フォーマットで 3072 バイト、COMPACT / REDUNDANT で 767 バイトであり、これを超えるとエラー 1071 となる。VARCHAR / TEXT 列は、同じ文字容量を維持するために必要に応じてより大きな型に昇格される (TEXT → MEDIUMTEXT)。照合順序の変更はソートと比較に影響する: utf8mb4_0900_ai_ci はアクセントと大文字小文字を区別しないため、以前は異なっていた値が一意インデックスで衝突する可能性がある (1062)。結合されるテーブルはまとめて変換する。クライアント接続も utf8mb4 を使用する必要がある。