エラーメッセージ
utf8mb4への移行中や、VARCHAR(255)のインデックスを持つテーブルを作成する際、以下のような厄介なボトルネックに遭遇することがあります。
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
これは通常、ALTER TABLEやCREATE TABLEの操作中に発生します。古い3バイトのutf8 (utf8mb3) から、絵文字や数学記号に不可欠な完全な4バイトのutf8mb4サポートへとアップグレードする開発者にとって、非常によくある悩みの一つです。
根本原因：なぜ767バイトなのか？
このエラーの背後にある計算は単純明快です。InnoDBのAntelopeフォーマットを使用している古いバージョンのMySQLでは、インデックスキーは正確に767バイトまでに制限されていました。
異なる文字セットがそのスペースをどのように消費するか比較してみましょう。
- 従来のutf8 (utf8mb3): 1文字＝最大3バイト。
255文字 * 3バイト = 765バイト。これは制限内に収まります。 - 最新のutf8mb4: 1文字＝最大4バイト。
255文字 * 4バイト = 1020バイト。これは767バイトの制限を超えてしまうため、エンジンがインデックスを拒否します。
MySQLは常にインデックスに対して可能な限りの最大スペースを確保します。現在のデータが単純な英数字であっても、データベースは最悪のケースである4バイト文字に備えます。
解決策1：Row FormatをDYNAMICに変更する
最も効果的な解決策は、Barracudaファイルフォーマットに切り替えることです。このフォーマットは「大きなプレフィックス」をサポートしており、最大3072バイトまでのインデックスを許可します。これを利用するには、テーブルのRow FormatをDYNAMICまたはCOMPRESSEDに設定する必要があります。
単一テーブルの更新：
ALTER TABLE your_table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
ROW_FORMAT=DYNAMIC;
新規テーブルの場合は、CREATE文にフォーマットを含めます。
CREATE TABLE example (
id INT PRIMARY KEY,
slug VARCHAR(255) NOT NULL,
UNIQUE KEY (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;
解決策2：「191ルール」（クイックフィックス）
サーバーのグローバル変数を変更できない制限の厳しいシステムで作業している場合は、「191ルール」を試してください。767を4で割ると191.75になるため、VARCHAR(191)はレガシーインデックスに収まる最大サイズとなります。
ALTER TABLE users MODIFY username VARCHAR(191) CHARACTER SET utf8mb4;
これは多くの場合、レガシーアプリケーションにとって最も安全な方法です。767バイトの制限は維持しつつ、カラムを十分に縮小することで、スキーマを壊さずに4バイト文字を許可できます。
解決策3：グローバルシステム設定 (MySQL 5.6)
数十個のテーブルを管理している場合、一つずつ手動で更新するのは面倒です。代わりに、SUPER権限がある場合はサーバーのデフォルトの挙動を変更できます。以下のコマンドを実行して、グローバルに大きなプレフィックスを有効にします。
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_large_prefix = ON;
これにより大きなインデックスが可能になりますが、実際に新しい制限を活用するには、テーブルがROW_FORMAT=DYNAMICを使用している必要があることに注意してください。
検証：修正されたことを確認する方法
エラーが出なかったからといって、移行が成功したと思い込まないでください。以下の3つのチェックで、基盤となる構造を確認しましょう。
- Row Formatの確認:
SHOW TABLE STATUS LIKE 'your_table_name'\Gを実行し、Row_format: Dynamicとなっているか確認します。 - 照合順序の確認:
SHOW FULL COLUMNS FROM your_table_name;を実行し、utf8mb4_unicode_ciが表示されるか確認します。 - 文字セットのテスト: 絵文字のような4バイト文字を挿入してみます：
INSERT INTO your_table_name (column) VALUES ('🚀');
予防策とベストプラクティス
究極の解決策は、MySQL 8.0以降またはMariaDB 10.2以降にアップグレードすることです。これらのバージョンではDYNAMICがデフォルトのRow Formatであり、大きなプレフィックス設定も最初から有効になっています。モダンな環境では、768文字を超えるカラムにインデックスを貼ろうとしない限り、このエラーを見ることはほとんどありません。
複雑な移行を扱う際、データベースに到達する前にデータが途中で文字化けしてしまうことが時々あります。文字列の挙動が疑わしい場合、ToolCraftのURL Encoder/Decoderは、ブラウザ上で直接データのエンコーディングを確認できる便利なプライバシー重視のツールです。インデックスのトラブルシューティングを始める前に、文字列がクリーンであることを確認するのに役立ちます。
まとめチェックリスト：
- 最善策: MySQL 8.0にアップグレードする。
- MySQL 5.6の場合:
innodb_large_prefixを有効にし、ROW_FORMAT=DYNAMICを設定する。 - 最終手段: インデックス付きカラムのサイズを
VARCHAR(255)からVARCHAR(191)に縮小する。