MySQL ERROR 1366: Unicodeや絵文字挿入時のIncorrect string valueを修正する

問題の概要

絵文字、中国語、アラビア語、または一部の日本語漢字を含む行をINSERTしようとすると、MySQLが次のエラーで処理を止めます：

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

\xF0\x9F\x98\x80 というバイト列は😀のUTF-8エンコードです。4バイトのUTF-8文字はすべて同じエラーを引き起こします。

根本原因

落とし穴はここにあります：MySQLのutf8文字セットは本物のUTF-8ではありません。3バイトのシーケンス（基本多言語面）しかサポートしていないため、絵文字や多くの稀なCJK文字はその範囲外となり、一切受け付けられません。実際の解決策はutf8mb4です。これはMySQL 5.5.3以降で利用可能な、真の4バイトUTF-8サポートです。

この不一致はサーバーデフォルト、データベース、テーブル、個々のカラムという4つのレベルで存在する可能性があります。通常のutf8に設定されているすべてのレベルを修正する必要があります。

応急処置 — 対象カラムを変更する

問題が1つのカラムだけの場合は、直接変更します：

ALTER TABLE posts
  MODIFY COLUMN content TEXT
  CHARACTER SET utf8mb4
  COLLATION utf8mb4_unicode_ci;

INSERTを再試行してください。成功すれば当面の問題は解消されますが、以下の恒久的な修正セクションも必ずお読みください。他のカラムも最終的に同じ問題に直面します。

恒久的な修正

ステップ1 — 現在の文字セット設定を確認する

-- サーバーデフォルト
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- データベースレベル
SHOW CREATE DATABASE your_database_name;

-- テーブルレベル
SHOW CREATE TABLE posts;

utf8mb4ではなくutf8と表示されている箇所はすべて変更が必要です。

ステップ2 — MySQLサーバーの設定を更新する

/etc/mysql/mysql.conf.d/mysqld.cnf（旧環境では/etc/my.cnf）を編集します：

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

次にMySQLを再起動します：

sudo systemctl restart mysql

ステップ3 — データベースを変換する

ALTER DATABASE your_database_name
  CHARACTER SET utf8mb4
  COLLATION utf8mb4_unicode_ci;

ステップ4 — すべてのテーブルとカラムを一括変換する

CONVERT TOを使うと、テーブル内のすべてのテキストカラムを一度に変換できます：

ALTER TABLE posts
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

ALTER TABLE users
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- すべてのテーブルに対して繰り返す

テーブルが20以上ある場合は、MySQLにステートメントを自動生成させましょう：

SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

出力をコピーしてクライアントに貼り付け、一括実行します。

ステップ5 — アプリケーションの接続文字列を修正する

データベースは変換済みなのにまだエラーが出る場合は、クライアント接続がutf8でネゴシエーションしている可能性があります。接続時に文字セットを明示的に指定してください。

PHP (PDO)：

$pdo = new PDO(
  'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
  $user, $pass
);

PHP (MySQLi)：

$conn = new mysqli('localhost', $user, $pass, 'mydb');
$conn->set_charset('utf8mb4');

Python (mysql-connector)：

cnx = mysql.connector.connect(
  host='localhost', database='mydb',
  user='user', password='pass',
  charset='utf8mb4'
)

Node.js (mysql2)：

const pool = mysql.createPool({
  host: 'localhost',
  database: 'mydb',
  charset: 'utf8mb4'
});

SQLAlchemy (Python)：

engine = create_engine(
  'mysql+pymysql://user:pass@localhost/mydb?charset=utf8mb4'
)

ステップ6 — innodb_file_formatの問題に対処する（MySQL 5.5〜5.7のみ）

MySQL 5.xでは、utf8mb4と長いインデックスキーの組み合わせで767バイトのインデックスプレフィックス制限に引っかかることがあります。変換直後にERROR 1071: Specified key was too longが発生した場合は、my.cnfに以下の設定を追加してください：

[mysqld]
innodb_file_format    = Barracuda
innodb_file_per_table = 1
innodb_large_prefix   = 1

MySQL 8.0ではこれらの設定がデフォルトで有効になっています。追加作業は不要です。

修正の確認

すべて正しく適用されたかどうかを確認する3つの簡単なチェック：

-- 1. サーバーの文字セットを確認
SHOW VARIABLES LIKE 'character_set_server'; -- utf8mb4 であること

-- 2. 特定のカラムを確認
SHOW FULL COLUMNS FROM posts WHERE Field = 'content';
-- Collation カラムが utf8mb4_unicode_ci であること

-- 3. 絵文字のINSERTを試す
INSERT INTO posts (content) VALUES ('Hello 😀🎉');
SELECT content FROM posts ORDER BY id DESC LIMIT 1;

絵文字が正しく返ってきたら完了です。

補足ヒント

データパイプラインでエンコーディングのバグを追跡するのは、まるで探偵作業のようです。エラーログに\xF0\x9F\x98\x80...のような切り詰められた16進数しか表示されない場合、機密情報をアップロードせずに生バイトを調べる手段が必要になります。私はtoolcraft.app/en/tools/developer/base64-encoderのBase64エンコーダーを使っています。問題の文字列を貼り付けると、そのバイト表現が確認でき、4バイトシーケンスが含まれているかどうかを判断できます。\xF0プレフィックスは常に4バイトのコードポイントを意味します。推測を素早く排除できる便利な方法です。

utf8mb4_unicode_ci と utf8mb4_general_ci の違い

どちらの照合順序も絵文字を問題なく保存できます。違いはソート時に現れます：

utf8mb4_unicode_ci — Unicodeのソート標準に準拠。多言語コンテンツに適しています。デフォルトとして使用することを推奨します。
utf8mb4_general_ci — 旧式のハードウェアではわずかに高速ですが、ソートの精度が低下します。新規プロジェクトでは使用しないことをお勧めします。
utf8mb4_0900_ai_ci — MySQL 8.0以降のデフォルト。最も精度の高いオプションです。MySQL 8を使用している場合はこちらを選んでも問題ありません。

まとめ

MySQLのutf8文字セットは4バイト文字（絵文字、稀なCJK文字、U+FFFFを超えるすべての文字）を拒否します。utf8mb4に切り替えてください。
変更はサーバー設定、データベース、テーブル/カラム、アプリケーション接続文字列の4つのレベルすべてに適用してください。
CONVERT TO CHARACTER SET utf8mb4を使えば、1つのコマンドでテーブル内のすべてのカラムを変換できます。
MySQL 8.0はデフォルトでutf8mb4を使用します。新規インストールでこのエラーが発生するのは、スキーマが明示的にutf8で作成された場合がほとんどです。