MySQLのPK・UNIQUEのフィールド長制限に立ち向かう

なんとかして制限を超えないといけない時ってありますよね。

MySQLで、InnoDBでPK・Uniqueをはるカラムには、フィールド長の制限があります。

俗に言う767byte問題です。

MySQLの767byte問題

プリフィックスは最高で1000バイトの長さまで可能です。(InnoDB テーブルは767バイト)非バイナリ　データ　タイプ(CHAR、VARCHAR、TEXT)では CREATE INDEX ステートメントのプリフィックス長は文字数で解釈される一方、プリフィックス　リミットはバイトで計算されるという事を覚えておいて下さい。マルチバイトの文字セットを利用するカラムのプリフィックス長を指定する時にはこれを考慮に入れておいて下さい。

http://dev.mysql.com/doc/refman/5.1/ja/create-index.html

まず、この制限受けると使用できるフィールド長は以下になります。

UTF8の場合・・・・767 ÷ 3 ＝255byte
UTF8MB4の場合・・767 ÷ 4 ＝191byte

具体的にエラー内容を確認。

mysql> create table f2(f varchar(192) unique) engine=innodb character set utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> create table f2(f varchar(191) unique) engine=innodb character set utf8mb4;
Query OK, 0 rows affected (0.04 sec)

ではこの制限にどう対応するか。

１，テーブルの文字コードをシングルバイトにする

mysql> create table f3(f varchar(767) unique) engine=innodb character set ascii;
Query OK, 0 rows affected (0.03 sec)

この方法だと、テーブル全体の文字コードが固定されるので、使いにくいです。

２，カラムの文字コードをシングルバイトにする

mysql> create table f4(f varchar(767) character set ascii unique) engine=innodb character set utf8mb4;
Query OK, 0 rows affected (0.04 sec)

この方法だと、特定カラムに限定して文字コードを指定できます。
しかし、この設定ができるER図作成ツールは少ないのが難点です。
ERMasterならできますが、ER図を見ても文字コード指定が表示されないので、
うっかり文字コード指定を削除してしまったり、認識違いがあったり、少々危険ではあります。
ER Master

３，カラムを分割する

mysql> create table f5(f1 varchar(191), f2 varchar(191), f3 varchar(191), f4 varchar(191), ) engine=innodb character set utf8mb4;
Query OK, 0 rows affected (0.04 sec)

この方法だと、カラム自体を分割するので単純明快です。
管理は多少面倒になりますが、２のようにER図作成ツールでも特殊な設定をする必要はありません。

総評

１は多分無いですね。現実的なのは２か３だと思います。
２だとフィールド長は767byteまで使えます。３はアプリ側が面倒ですが、日本語も普通に使用できます。
３の場合は分割しすぎるとこうなる↓ので注意。キーの合計フィールド長が3072byteを超えるとこうなります。
（下の例では767 × 5 ＝ 3835byte）

mysql> create table f6(f1 varchar(191), f2 varchar(191), f3 varchar(191), f4 varchar(191), f5 varchar(191), primary key(f1, f2, f3, f4, f5)) engine=innodb character set utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

InnoDB の内部的な最大キー長は 3500 バイトですが、MySQL 自体はそれを 3072 バイトに制限しています

http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-restrictions.html

---

varbinary型を使えば767byteで更に日本語も入れられますが、取り扱いが少々面倒だと思うのでやめた方がいいかと思います。

mysql> create table f7 (f1 varbinary(767) unique) engine=innodb character set utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into f7 values('ほげほげ');
Query OK, 1 row affected (0.00 sec)

mysql> select * from f7;
 +--------------+
 | f1           |
 +--------------+
 | ほげほげ     |
 +--------------+
1 row in set (0.00 sec)

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド