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バイトです。 つまり４倍なわけでして。

`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については、下記で解説しました

hit.hateblo.jp

簡単に説明しますと。

Urlのカラムに入ったカラムは自動的にUrl1とUrl2に分割される（分割） 512文字でユニークだったら、まず問題ねーだろう？（妥協）

です。

根本的に、長い文字を索引にするのってどーなの？というところのツッコミもあるとは思いますが。