MySQLのUNIQUEなINDEXには長さ767byteまでしか使えない件と対策

はじめに

たとえばこんなDDLを投げる。

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hoge varchar(256) NOT NULL,
  UNIQUE KEY (hoge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

するとエラーになる。

Specified key was too long; max key length is 767 bytes (SQLState:S1000)

エラーに書かれているとおり、keyは最大で767byteまでしか使えないらしい。
ちなみにkeyはPRIMARY KEYとUNIQUE KEYがダメ、ただのKEYならOK。

で、どうするか。

1.素直に諦める

上記例ではテーブルがCHARSET=utf8のため1文字3byteとなり、合計で256*3=768byteなのでダメだということなので、varchar(255)に変更すればよい。

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hoge varchar(255),
  UNIQUE KEY (hoge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

これで済むならそうする。しかしMySQL6以降はutf8が4byteになるため、この制約がそのままならvarchar(191)までしか入らないと思われるため、そのシステムは永久にMySQL5系専用になりそう。この制約がそのままだったらMySQL6は爆発すればいいと強く思うところなんだけど、誰しも爆風に巻き込まれたくないのでこの対応は微妙っぽい。

2.テーブルの文字コードを変える

文字コードがutf8だからいかんので、文字コードをシングルバイト仕様に変えれば767byteまで使えるようになる。

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hoge varchar(767),
  UNIQUE KEY (hoge)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ただし、テーブルに日本語は入れるなということ。

3.フィールドの文字コードを変える

MySQLではフィールド単位でも文字コードが変えられる。
国際派ではない僕としてはどんな用途で使うのか想像できなかったけど、今回のような用途に使うものなのだろうと勝手に理解した。

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hoge varchar(767) CHARACTER SET latin1,
  UNIQUE KEY (hoge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

テーブルとフィールドで文字コードが違うというのは感覚的に気持ち悪いし、どこかでテーブルとフィールドの文字コードが違うとアレになるという話を見た気がするけど、latin1じゃない文字列とは比較しません！という作りにしておけば実用的かと。

4.バイナリで入れる

でも日本語も入れたいんだよね、という時にはいっそバイナリ型にするとか。

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  hoge varbinary(767),
  UNIQUE KEY (hoge)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ちょっと出し入れが面倒になるけど、何でも入るようになるからいいよね？

結局

3,4あたりが妥当ですかね。