【MySQL】文字コード・照合順序（utf8mb4）でハマらないために

MySQL
2022.07.02

こんにちは、かつコーチです。

前回はMySQLのアーキテクチャ全体像を解説しました。

今回は、実務でつまずく人が非常に多い「文字コード」と「照合順序」の話です。

絵文字が保存できない、日本語が「?」になる、といったトラブルの多くはここが原因です。

文字コードと照合順序とは？

文字セット（Character Set）の役割

文字セット（Character Set）とは、文字をどんなルールでバイト列に変換して保存するかを決める仕組みです。

MySQLではデータベース・テーブル・列のそれぞれの単位で、異なる文字セットを設定できます。

MySQL 5.7以前のデフォルト文字セットはutf8でしたが、このutf8は実は最大3バイトまでしか扱えない、不完全な実装でした。

照合順序（Collation）の役割

照合順序（Collation）とは、文字の大文字・小文字の区別や、並び替え・比較のルールを決める仕組みです。

同じ文字セットでも、照合順序が違うとORDER BYの並び順や、WHEREでの文字列比較の結果が変わることがあります。

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

このコマンドで、現在のサーバー・データベース・接続の文字コード設定を一覧で確認できます。

utf8とutf8mb4の違い

絵文字が保存できない原因

❌ Before：utf8のまま文字セットを設定する

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255)
) DEFAULT CHARACTER SET utf8;

このテーブルに絵文字（😀など、4バイト必要な文字）を保存しようとすると、以下のようなエラーが発生します。

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80...' for column 'title' at row 1

私が過去に担当したブログシステムで、ユーザーが投稿タイトルに絵文字を使った瞬間にこのエラーが発生し、原因調査に半日を費やした経験があります。

原因は、テーブル作成時に文字セットをutf8のまま放置していたことでした。

MySQLのutf8は最大3バイトしか扱えず、絵文字を含む一部の文字（4バイト必要なもの）を保存できない仕様だったのです。

utf8mb4で解決する

✅ After：utf8mb4を明示的に指定する

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs;

utf8mb4は、最大4バイトまで扱える正しいUTF-8実装で、絵文字を含むすべての文字を正しく保存できます。

MySQL 8.0からはデフォルトの文字セットがutf8mb4に変更されているため、新規プロジェクトであればこの問題自体に遭遇しにくくなっています。

ただし古いバージョンから移行してきたシステムや、テンプレートを流用して作られたテーブルでは、今でもutf8が紛れ込んでいることがあるため、必ず確認する習慣をつけておきましょう。

日本語照合順序の選び方

主な選択肢と違い

MySQL 8.0のutf8mb4には、複数の照合順序が用意されています。

照合順序特徴おすすめの用途
utf8mb4_general_ci簡易的な比較ルール、大文字小文字を区別しない厳密な日本語ソートが不要な場合
utf8mb4_unicode_ciUnicode標準に近い比較ルール汎用的な多言語対応
utf8mb4_0900_ai_ciMySQL 8.0のデフォルト、アクセント・大文字小文字を区別しない特別な要件がない場合の標準
utf8mb4_ja_0900_as_cs日本語の五十音順に近い並びに対応、大文字小文字を区別する日本語の五十音順ソートが必要な場合

日本語の会員名簿を五十音順に表示したいなど、日本語特有の並び替えが必要な場合はutf8mb4_ja_0900_as_csのような日本語向け照合順序を検討する価値があります。

一方で「特にこだわりがない」場合は、MySQL 8.0のデフォルトであるutf8mb4_0900_ai_ciのままで実務上困ることはほとんどありません。

つまずきやすいポイント：接続時の文字コード不一致

アプリ側とDB側で設定がズレるケース

テーブルの文字セットを正しくutf8mb4にしていても、アプリケーションからの接続時に文字コードが指定されていないと、文字化けが発生することがあります。

// ❌ Before：接続時の文字コード指定がない
$pdo = new PDO('mysql:host=localhost;dbname=myapp', $user, $pass);

// ✅ After：接続文字列にcharset=utf8mb4を明示する
$pdo = new PDO('mysql:host=localhost;dbname=myapp;charset=utf8mb4', $user, $pass);

私が実務で経験した文字化けトラブルの多くは、テーブル定義側ではなく、こうしたアプリケーション側の接続設定漏れが原因でした。

「テーブルの文字コードは合っているのに文字化けする」という場合は、まず接続時の設定を疑うのが定石です。

応用：既存テーブルの文字コードを変換する

ALTER TABLEでの変換

既存のutf8テーブルをutf8mb4に変換する場合は、以下のようにALTER TABLEを使います。

ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ただしこの変換は、テーブルのサイズによっては時間がかかり、実行中はロックが発生する点に注意が必要です。

本番環境で実行する際は、必ずメンテナンス時間を確保するか、影響範囲の小さいタイミングを選んで実行しましょう。

まとめ

この記事のポイント

  • utf8は最大3バイトしか扱えず、絵文字などの4バイト文字でエラーになる
  • utf8mb4を使えば4バイト文字も正しく保存でき、MySQL 8.0ではデフォルトになっている
  • 日本語の五十音順ソートが必要な場合はutf8mb4_ja_0900_as_csを検討する
  • 文字化けはテーブル定義だけでなく、アプリ側の接続設定も原因になりうる

次に読むべき記事

アーキテクチャ編はここまでです。

次はMySQL固有のデータ型に絞って、ENUMやSET、TEXT系の使い分けを解説します。

→ 次の記事：MySQL固有のデータ型（ENUM・SET・TEXT系の使い分け）

タグ: MySQL, 中級者向け, アーキテクチャ

タイトルとURLをコピーしました