MySQL 5.7 → MySQL 8.0 移行でハマったポイントまとめ

Last updated at 2025-12-06Posted at 2025-12-04

はじめに

MySQL 5.7 から 8.0 への移行は、一見「ダンプ取って流すだけ」で終わりそうに見えるが、実際には 文字コードと照合順序（collation）まわりでかなり苦労した。
特に、長年運用してきたシステムほど内部に“歴史的負債”が蓄積されており、そこを丁寧に解消しないと MySQL 8.0 で大量エラーが発生する。

この記事では、実際に遭遇した問題と、最終的に採用した移行方針をまとめる。

1. 文字コードの仕様変更で比較が厳密になった

MySQL 8.0 では文字コードまわりが整理され、
utf8（実体は utf8mb3）と utf8mb4 の差分がより明確になった。

その結果、5.7 のゆるい比較で問題が出なかったクエリでも、
8.0 では以下のようなエラーが簡単に発生する。

Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE)

“混在していたけど動いていたものが、8.0では動かない”という典型例。

さらに厄介なのは、API によってこのエラーが出たり出なかったりすること。
内部で比較処理が走るパスが異なるため、
「特定の機能だけ突然落ちる」という形で表面化し、気づくのが遅れやすい。

2. テーブルごとに charset / collation がバラバラ

長年運用してきた DB では、テーブル設計者や世代によって charset が混在していた。

utf8（= utf8mb3）
utf8mb4
COLLATE=utf8_unicode_ci
COLLATE=utf8_general_ci
COLLATE=utf8_bin
デフォルトのまま（実質 utf8mb3）

さらに揃えづらかったのがこのケース：

ID varchar(45) COLLATE utf8_bin NOT NULL

カラム単位で COLLATE が直書きされているため、
sed で単純置換すると壊れる or 置換しきれない。

3. sed で一括変換しても精度が低い

最初はダンプファイルを sed で一括変換して対応しようとした。
しかし実際には、
フォーマットが微妙に違うパターンが大量に存在し、完璧に処理するのはほぼ不可能。

使用した sed は以下のようなもの：

sed \
  -e 's/CHARSET=utf8 /CHARSET=utf8mb4 /g' \
  -e 's/CHARSET=utf8;/CHARSET=utf8mb4;/g' \
  -e 's/COLLATE=utf8_general_ci/COLLATE=utf8mb4_0900_ai_ci/g' \
  -e 's/COLLATE=utf8_unicode_ci/COLLATE=utf8mb4_0900_ai_ci/g' \
  -e 's/COLLATE=utf8_bin/COLLATE=utf8mb4_0900_ai_ci/g' \
  -e 's/COLLATE utf8_general_ci/COLLATE utf8mb4_0900_ai_ci/g' \
  -e 's/COLLATE utf8_unicode_ci/COLLATE utf8mb4_0900_ai_ci/g' \
  -e 's/COLLATE utf8_bin/COLLATE utf8mb4_0900_ai_ci/g' \
  "$RAW_DUMP" > "$UTF8MB4_DUMP"

しかし、以下の理由で実用に耐えなかった：

書き方のゆらぎに対応しきれない
カラム単位の COLLATE を壊す可能性
UNICODE vs BIN の意図を無視してしまう
後からどこが間違ったかデバッグしにくい

結果として、sed 工程は捨てた。

4. 最終的に採用した移行方針

結論：

アプリ側のテーブルDDL（マスタ）を修正し直し、移行時はデータのみダンプして取り込む。

テーブル定義は Git 管理されている “マスタ” を修正する

charset はすべて utf8mb4
collation は基本 utf8mb4_0900_ai_ci
カラム単位で意図のない COLLATE 指定を削除
MySQL 8.0 推奨の書き方に揃える（例：INT の幅指定廃止、TIMESTAMP DEFAULT 明示）

※ utf8mb4_0900_ai_ci を採用した理由
→ 8.0 のデフォルトであり、Unicode 9.0 ベースで検索精度が高く、将来性もあるため。

データは 5.7 から “データのみ” ダンプする

mysqldump -u root -p \
  --default-character-set=utf8mb4 \
  --no-create-info \
  --replace \
  --routines --triggers --events \
  karte > data_only.sql

8.0 側はマスタDDLで空DBを構築してからデータ投入する

余計な charset / collation が混入しない
MySQL 8.0 の仕様に完全に沿ったテーブルを用意できる
デバッグも容易

5. sql_mode=only_full_group_by による GROUP BY エラー

MySQL 8.0 (および最近の 5.7) ではデフォルトで sql_mode=only_full_group_by が有効になっています。
これにより、以前は許容されていた「GROUP BY 句に含まれない非集約カラムを SELECT するクエリ」がエラーになります。

発生するエラー:

java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '...' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因:
GROUP BY で指定していないカラムを SELECT 句で指定しており、かつそのカラムが集約関数（SUM, MAX 等）で囲まれていないため、どの行の値を返すべきか不定となるためです。

解決策:

SQL を正しく書き直す（推奨）

SELECT で取得する非集約カラムをすべて GROUP BY に追加する。
または、代表値でよい場合は MAX() や MIN() で集約する。

-- 修正前（エラー）
SELECT t.ID, t.CATEGORY_ID, t.STATUS, t.ITEM_NAME, t.CREATED_AT, m.USER_NAME
FROM ...
GROUP BY t.CATEGORY_ID, t.STATUS;

-- 修正後（OK）：SELECT句に含まれる非集約カラムをすべてGROUP BYに追加
SELECT t.ID, t.CATEGORY_ID, t.STATUS, t.ITEM_NAME, t.CREATED_AT, m.USER_NAME
FROM ...
GROUP BY
  t.ID,
  t.CATEGORY_ID,
  t.STATUS,
  t.ITEM_NAME,
  t.CREATED_AT,
  m.USER_NAME;

only_full_group_by を無効化する（暫定対応）
- 既存の SQL を修正するのが困難な場合の回避策です。
- my.cnf やセッション設定で sql_mode から ONLY_FULL_GROUP_BY を除外します。
```
SET SESSION sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');
```

今回は、将来的な保守性を考慮し、可能な限り SQL を修正する方針 を採用しました。

まとめ：sed に頼らず、DDL を正常化するのが最速だった

MySQL 移行で一番つまずいたのは、
長年の運用で混在してしまった charset / collation をどう統一するか だった。

経験的には、

ダンプを sed で書き換える → 破壊率が高く非推奨
DDL を正しく保守し、データだけ移す → 安定・確実

という結論に行き着いた。

特に、API 単位で挙動が変わり、
“ある操作だけ突然落ちる” という症状は移行作業の混乱を大きくする。
だからこそ、移行前に DDL の再整備 をしておく価値は高かった。

同じように苦労している人の参考になれば幸いです。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up