HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

キーの最大長と文字エンコーディング設定

MySQL

今までsjisとかujisとかだったMySQLのデータベースをutf8mb4に変換しようとしていたときの話。キーの最大長という壁にぶつかったらしい。

MySQLのリファレンスを見ると、こんなことが書いてある。


プレフィックスは、最大1000バイト長(InnoDBテーブルに対しては767バイト)まで可能です。プレフィックスの最大長はバイトで評価されます。一方、CREATE TABLEステートメント内のプレフィックス長は文字数として解釈されます。プレフィックス長を、マルチバイトキャラクタセットを使用するカラムに対して指定するときこのことを考慮に入れなければいけません。
ここでは、直接的にはTEXT型やBLOB型について言っているのだが、VARCHAR型なども同様。
ということで、以下のようなCREATE TABLE文をMySQL 5.5で流してみる。

-- OK
CREATE TABLE hoge1_1(
      str1 VARCHAR(333) BINARY CHARACTER SET utf8 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- NG
CREATE TABLE hoge1_2(
      str1 VARCHAR(334) BINARY CHARACTER SET utf8 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- OK
CREATE TABLE hoge1_3(
      str1 VARCHAR(250) BINARY CHARACTER SET utf8mb4 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
-- NG
CREATE TABLE hoge1_4(
      str1 VARCHAR(251) BINARY CHARACTER SET utf8mb4 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

-- OK
CREATE TABLE hoge2_1(
      str1 VARCHAR(255) BINARY CHARACTER SET utf8 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- NG
CREATE TABLE hoge2_2(
      str1 VARCHAR(256) BINARY CHARACTER SET utf8 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- OK
CREATE TABLE hoge2_3(
      str1 VARCHAR(191) BINARY CHARACTER SET utf8mb4 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- NG
CREATE TABLE hoge2_4(
      str1 VARCHAR(192) BINARY CHARACTER SET utf8mb4 NOT NULL
    , CONSTRAINT pkey PRIMARY KEY(str1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OK
CREATE TABLE hoge3_1(
      str1 VARCHAR(2000) BINARY CHARACTER SET utf8 NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- OK
CREATE TABLE hoge3_2(
      str1 VARCHAR(2000) BINARY CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

クエリの上に結果を書いているが、MyISAMだと1000バイト、InnoDBだと767バイトを超えると、キー(インデックス)に指定することができない(utf8は1文字が3バイト、utf8mb4は1文字が4バイト)。
(実は、上記は厳密に境界値を検証できているわけではないが‥)


まぁ、根拠もないのに「なんとなく」VARCHAR(255)とか書くのはやめろよ、と(謎)。
だったら、後で困るのを覚悟しながらTEXT型を使えよ、と(更謎)。

もしかしたらこの辺も参考になるかな。