MySQLで "Specified key was too long" エラーが出たときの対処法

あるときMySQLでテーブル作成すると、こんなエラーが・・・

Specified key was too long; max key length is 767 bytesTable 'hoge' doesn't exist

1	Specified key was too long; max key length is 767 bytesTable 'hoge' doesn't exist

Specified key was too long

こんなエラー始めてみたので、少し困惑

でも直し方は意外と簡単でした。

そこで自分への忘備録も兼ね、
この Specified key... エラーが出る原因とか対処法をまとめたいと思います。

このページの目次

問題が発生したのはテーブルを作ったとき
"Specified key was too long" の解決策
ここまでのまとめ

問題が発生したのはテーブルを作ったとき

このエラーに遭遇したのはテーブル作成したとき

次みたいなSQLを発行して、テーブルを作ろうとしてたんです。

CREATE TABLE IF NOT EXISTS hoge (
  user_id      BIGINT NOT NULL AUTO_INCREMENT,
  app_type     VARCHAR(255) NOT NULL,
  app_id       VARCHAR(512) UNIQUE,
  PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS hoge (

user_id BIGINT NOT NULL AUTO_INCREMENT,

app_type VARCHAR(255) NOT NULL,

app_id VARCHAR(512) UNIQUE,

PRIMARY KEY(id)

);

それで問題があったのが、ハイライトした５行目

この行に問題があったのか、次みたいなエラーが出てしまいました。

Specified key was too long; max key length is 767 bytesTable 'hoge' doesn't exist

1	Specified key was too long; max key length is 767 bytesTable 'hoge' doesn't exist

簡単に意訳するなら、

「キーが長すぎ！最大で767バイトまでにしてね」

MySQLだと VARCHAR はバイト数でなく、文字単位で記録されます。

だから 512 を指定してても、長さが512バイトとは限らないわけですね。

まあこういう感じでキーが長すぎるって怒られた訳です。

この現象は VARCHAR(4096) UNIQUE みたいに、
可変長がとれるカラム値をユニークにすると発生するみたいです。

そこで色々調べてみると、意外な解決策が見つかりました。

"Specified key was too long" の解決策

まずこのエラーが出る原因について

これはさっき書いたみたいに、

カラムに UNIQUE をつけて、ユニークにしてる
その上インデックスプレフィックス長を指定してる
その長さが 767 バイトを超えてしまっている

この３つの条件が揃うとエラーが出る模様

例えば VRACHAR(1024) とか指定することありますよね？

その場合の 1024 がプレフィックス長、
そしてそのカラムがインデックスなら "インデックスプレフィックス長" と呼ばれてます。

その長さは InnDB のデフォルト設定の場合、
最大長さが 767バイトに制限されてるみたいです。

少し間違ってる部分もあるかもですが、大体こういうこと

それで結局この解決策はなにかというと・・・

次みたいに CHARACTER SET ascii をつけてやればいいだけ

CREATE TABLE IF NOT EXISTS hoge (
  user_id      BIGINT NOT NULL AUTO_INCREMENT,
  app_type     VARCHAR(255) NOT NULL,
  app_id       VARCHAR(512) 
    CHARACTER SET ascii UNIQUE,
  PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS hoge (

user_id BIGINT NOT NULL AUTO_INCREMENT,

app_type VARCHAR(255) NOT NULL,

app_id VARCHAR(512)

CHARACTER SET ascii UNIQUE,

PRIMARY KEY(id)

);

こうすればアスキーで保存されるので、エラーが出なくなります。

ただし VARCHAR(1024) とかだと、 767 バイト以上を超えてるからダメです。

ユニーク指定してる VARCHAR とか CHAR 、 BINARY 、 TEXT ・・・

そういうのの長さは必ず 767 バイト以内に収めるようにしてください。

これが一応の解決策

ただ 767 バイト以上にしたい場合はどうすればいいか不明です。

なんか代わりの方法があるのかもしれないけど、謎

もし詳しい方はコメント欄とか教えていただけるとありがたいです。

ここまでのまとめ

ということで、 Specified key was too long の原因と解決策まとめ

このエラーの原因
ユニークでなおかつ VARCHAR とかの可変長が取れるカラムがあるとき、その長さが 767 バイト以上を超えちゃってると発生する。そういう仕様
このエラーの解決策
問題のあるカラムに CHARACTER SET ascii をつけてやればＯＫ。長さを 767 バイト以上にしたい場合は分からないけど、とりあえず解決！！

まだまだ MySQLは分からないことは多いです。

以上、MySQLで Specified key was too long エラーが出た時の対処法でした。