EC-CUBE 4.0系から引き継いだ utf8_general_ci のDBを、4.3の標準である utf8mb4_bin にまとめて変換するのは、MySQL 5.7でも ALTER TABLE ... CONVERT TO CHARACTER SET を全テーブルに対して実行するだけで済みます。ただし、事前に information_schema で3つの上限を計算しておくこと、変換後にMySQLが自動で広げたTEXT型にEntityの宣言を合わせること、この2点を忘れると、変換そのものは成功しても、あとで schema:update が既存データを切り詰める危険なALTERを出すようになります。
変換前にinformation_schemaで見る3つの上限
utf8は1文字最大3バイト、utf8mb4は4バイトなので、文字列列のバイト数が4/3倍になります。MySQL 5.7で超えてしまう可能性がある上限は次の3つです。
| 上限 | 値 | 見る場所 |
|---|---|---|
| 行フォーマット | Dynamic / Compressed なら索引 3072 バイト、Compact / Redundant なら 767 バイト | information_schema.tables.row_format |
| 索引の長さ | 3072 バイト（innodb_large_prefix=ON 時） |
information_schema.statistics の列を文字数×4 で合計 |
| 行の最大サイズ | 65535 バイト | information_schema.columns の varchar/char を長さ×4+2 で合計 |
索引の長さは、索引を構成する列ごとに文字数×4（sub_partがあればそれ×4）と数値型のバイト数を足せば見積もれます。
SELECT s.table_name, s.index_name,
SUM(CASE
WHEN c.data_type IN ('varchar','char') THEN COALESCE(s.sub_part, c.character_maximum_length) * 4
WHEN c.data_type LIKE '%text' THEN COALESCE(s.sub_part, 0) * 4
WHEN c.data_type = 'bigint' THEN 8
WHEN c.data_type = 'int' THEN 4
WHEN c.data_type = 'smallint' THEN 2
WHEN c.data_type = 'datetime' THEN 5
ELSE 8 END) AS mb4_key_bytes
FROM information_schema.statistics s
JOIN information_schema.columns c
ON c.table_schema = s.table_schema AND c.table_name = s.table_name AND c.column_name = s.column_name
WHERE s.table_schema = 'eccube_db'
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10;行サイズは同じ要領で character_maximum_length * 4 + 2 をテーブルごとに合計します。EC-CUBEの標準スキーマでは索引の最大は varchar(255) 単独の1020バイト前後ですが、行サイズは文字列列をカスタマイズで増やした dtb_customer や dtb_order が5万バイト台まで来るので、65535に近いテーブルは、先にvarcharをtextに変更してから変換します。「767バイト制限があるので varchar(255) に索引が作れない」という情報を見かけますが、行フォーマットがDynamicで innodb_large_prefix=ON（5.7の既定）であればこの制限は適用されません。
ALTER文はinformation_schemaから生成する
テーブル名を手で並べると漏れるので、SQLで生成します。既にutf8mb4_binのテーブルは除外します。
SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM information_schema.tables
WHERE table_schema = 'eccube_db' AND table_collation <> 'utf8mb4_bin'
ORDER BY table_name;先頭に ALTER DATABASE `eccube_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; を付けて1つのSQLファイルにし、フルダンプを取ってから流します。CONVERT TO はテーブルを作り直すので、GB級のログテーブルを含む90テーブル弱で1分半かかりました。実行中は、変換しているテーブルへの書き込みがブロックされます。
TINYTEXTが自動でTEXTになり、schema:updateが縮めようとする
ここが本題です。CONVERT TO CHARACTER SET utf8mb4 は「格納できる文字数を減らさない」ために、TEXT系の型を自動で1段広げます。TINYTEXT（255バイト）はutf8で85文字入りますが、utf8mb4では63文字になるので、MySQLはTINYTEXTをTEXTに、TEXTをMEDIUMTEXTに変えます。実測では60列以上が変わりました。
問題はDoctrine側です。Entityで type="text", length=255 と宣言していると、DoctrineはMySQLではTINYTEXTを期待するので、変換後に --dump-sql を見るとこう出ます。
ALTER TABLE dtb_product CHANGE sub_title1 sub_title1 TINYTEXT DEFAULT NULL, CHANGE sub_comment1 sub_comment1 TEXT DEFAULT NULL, ...これを --force で流すと、日本語63文字を超えるデータが入っている列で、データの切り詰めかエラーが起きます。対処は、Entityの length を変換後の実型に合わせることです。Doctrineは length をバイト数として見て、255以下ならTINYTEXT、65535以下ならTEXT、16777215以下ならMEDIUMTEXTを選ぶので、length=255 を 65535 に、length=65532 のような値を 16777215 に書き換えます。coreのEntityで type="text" に length が無い列は、TEXTとMEDIUMTEXTを区別しないので差分になりません。
_binにする前に大文字小文字の混在を数える
utf8mb4_bin は大文字と小文字を区別します。utf8_general_ci では同じ値だった会員メールアドレスや管理者IDが、変換後は別の値になり、ログインに使う = 比較が通らなくなります。変換前にこの2つを数えて、0件であることを確認してから進めます。
SELECT COUNT(*) FROM dtb_customer WHERE email <> LOWER(email);
SELECT COUNT(*) FROM dtb_member WHERE login_id <> LOWER(login_id);0件でなければ、変換前に小文字へ揃えるか utf8mb4_general_ci を選ぶかを決めます。管理画面の英字検索が大小を区別するようになりますが、4.3の新規インストールと同じ挙動です。接続側の DATABASE_CHARSET は4.3の .env.dist も utf8 のままなので、変える必要はありません。
EC-CUBEゴールドパートナー
EC-CUBEに関するお問い合わせ
[重要]現在公式にセキュリティサポートが切れていないPHPは8.1以上、MySQLは8.0以上で、対応しているEC-CUBEバージョンは4.2以上です。古いEC-CUBEを使っている方は適切なタイミングでバージョンアップをご検討ください。
