MySQL 結局、索引の最大長っていくつなの?を考える Specified key was too long; max key length is 3072 bytes
MySQL8.0の話ですので、以前のバージョンの話とはちょっと変わってしまうかもしれませんが、テーブルの作成で、ものすごくしょうもないことでハマってしまいました。
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
まず、前提として、索引(インデックス)の最大長は、3072バイトです。ってところから始めます(笑)
これがいけると思っていたところからが地獄の始まり(笑)都合よく、私は、varchar(1024)を1024バイトだと思ってました(汗)
CREATE TABLE `table1` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Url` varchar(1024) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ix01` (`Url`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ; ---------------------------------------- ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
varchar(1024)はどういう意味???
これ、たぶん、私以外はしっていると思うのですが、1024バイトじゃなくて、1024文字なんですよね!! じゃーバイト数はなんなの?っことなのですが、これは文字コードセットによって変わってきます。
utf8mb4
昨今ですと、絵文字とかも入る、文字コードセットなのですが、varchar(1)の場合のバイト数は、4バイトです。 つまり4倍なわけでして。
`Url` varchar(1024) NOT NULL,
の場合、1024 X 4 = 4096バイトになりますよね。3072バイトの制約を超えているので、エラーになるわけです。
utf8(utf8mb3)
では、utf8の場合はどうでしょうか?
CREATE TABLE `table1` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Url` varchar(1024) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ix01` (`Url`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; ------------------------------------------ mysql> show warnings ; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
なんかワーニング出てるけど今度は、うまくいきました。ワーニングは、utf8ってutf8mb3の別名なんだけど、将来的に、utf8mb4の別名になるよん!ってことかな?って思います。
どちらにしても、うまく索引は作られたようです。
つまり、
utf8(utf8mb3)のvarchar(1)の場合のバイト数は、3バイトです。 つまり3倍の計算になると思うのですが
の場合、1024 X 3 = 3072バイトになりますよね。3072制約にぴったりなので、大丈夫!って話です。
。。で、書いていて発見したのですが(いまさらか!!)
utf8mb3,utf8mb4のお尻の、3,4とかでバイト数を換算すればいいのか!!って
でもどーしても索引が貼りたい!!
utf8mb4の話で進めますが、どーしても索引を貼りたいんだ!ということはあると思います。でも、現状、3072バイトの壁を破ることはできないです。では、どうするか?という話になるのですが、分割と妥協が必要になってくるかと思います。 私でしたら、さきほどエラーだった、CREATE文を以下のように変更すると思うのです。。
エラーだったCREATE 文
Urlのカラムが、3072バイトを超えるので、エラーになってやつです。
CREATE TABLE `table1` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Url` varchar(1024) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ix01` (`Url`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
こーいう風に変えます
CREATE TABLE `table1` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Url` varchar(1024) NOT NULL, `Url1` varchar(512) GENERATED ALWAYS AS (left(`Url`,512)) STORED NOT NULL, `Url2` varchar(512) GENERATED ALWAYS AS (substr(`Url`,513)) STORED, PRIMARY KEY (`Id`), UNIQUE KEY `ix01` (`Url`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
GENERATED ALWAYSについては、下記で解説しました
簡単に説明しますと。
Urlのカラムに入ったカラムは自動的にUrl1とUrl2に分割される(分割) 512文字でユニークだったら、まず問題ねーだろう?(妥協)
です。
根本的に、長い文字を索引にするのってどーなの?というところのツッコミもあるとは思いますが。