Hatena::ブログ(Diary)

tanamonの日記

2009-09-30

[][]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あたりが妥当ですかね。

sh2sh2 2009/10/01 13:29 PRIMARY KEYに長いデータを入れるのは性能上あまりよくないので、
CREATE TABLE test (id int auto_increment primary key,
hoge varchar(255), key (hoge)) ENGINE=InnoDB;
とすることが多いと思います。
hogeの一意性をとるにはINSERT前に一度SELECTしてみるしかないかもしれません。

tanamontanamon 2009/10/01 14:10 僕もいつもはprimary keyをidにしているので、例では少し端折りすぎていました。
というわけで、記事をhogeをunique keyにしたものに変更しました。
指摘どうもです。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

リンク元