单一字段的索引长度限制为767 bytes, 索引总长度的限制是: The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes. (1024 bytes for non-64-bit builds before MySQL 5.0.17, and for all builds before 5.0.15.) 在utf8字符集下，varchar(255) 的[数据部分]占用 255*3=765 bytes，最接近767bytes， 256*3 = 768bytes，已经超过767。
[BIGHD](root@localhost) [cm]> CREATE TABLE `temp_2` ( -> `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, -> `pn` VARCHAR(128) DEFAULT '', -> `first_path` VARCHAR(256) DEFAULT '', -> `dir` VARCHAR(255) DEFAULT '', -> `a` text, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> -> ; Query OK, 0 ROWS affected (0.15 sec) [BIGHD](root@localhost) [cm]> [BIGHD](root@localhost) [cm]> [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (first_path); Query OK, 0 ROWS affected, 1 warning (0.18 sec) Records: 0 Duplicates: 0 Warnings: 1 [BIGHD](root@localhost) [cm]> SHOW warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | +---------+------+---------------------------------------------------------+ 1 ROW IN SET (0.00 sec) [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (dir); Query OK, 0 ROWS affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (a(767)); Query OK, 0 ROWS affected, 1 warning (0.32 sec) Records: 0 Duplicates: 0 Warnings: 1 [BIGHD](root@localhost) [cm]> SHOW warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | +---------+------+---------------------------------------------------------+ 1 ROW IN SET (0.00 sec) [BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2; | temp_2 | CREATE TABLE `temp_2` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `pn` VARCHAR(128) DEFAULT '', `first_path` VARCHAR(256) DEFAULT '', `dir` VARCHAR(255) DEFAULT '', `a` text, PRIMARY KEY (`id`), KEY `first_path` (`first_path`(255)), KEY `dir` (`dir`), KEY `a` (`a`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
所以key里面a(255)，表示 255个字符(一个utf8字符占3字节)。
再看一下联合索引的情况：
[BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY (pn, first_path); Query OK, 0 ROWS affected, 1 warning (0.10 sec) Records: 0 Duplicates: 0 Warnings: 1 [BIGHD](root@localhost) [cm]> [BIGHD](root@localhost) [cm]> SHOW warnings; +---------+------+---------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------+ | Warning | 1071 | Specified KEY was too long; MAX KEY LENGTH IS 767 bytes | +---------+------+---------------------------------------------------------+ 1 ROW IN SET (0.00 sec) [BIGHD](root@localhost) [cm]> SHOW CREATE TABLE temp_2; | temp_2 | CREATE TABLE `temp_2` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `pn` VARCHAR(128) DEFAULT '', `first_path` VARCHAR(256) DEFAULT '', `dir` VARCHAR(255) DEFAULT '', `a` text, PRIMARY KEY (`id`), KEY `first_path` (`first_path`(255)), KEY `dir` (`dir`), KEY `a` (`a`(255)), KEY `pn` (`pn`,`first_path`(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
即使是联合索引，767的限制也是针对字段的，而不是联合索引的总长度。
CREATE TABLE `temp_2` ( `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `a` text, `b` text, `c` text, `d` text, `e` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 255 * 3 = 765 * 4 = 3060 + 4*3 = 3072 [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(4)); Query OK, 0 ROWS affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 [BIGHD](root@localhost) [cm]> [BIGHD](root@localhost) [cm]> ALTER TABLE temp_2 ADD KEY(a(255), b(255), c(255), d(255), e(5)); ERROR 1071 (42000): Specified KEY was too long; MAX KEY LENGTH IS 3072 bytes
查看更多关于innodb utf8字符集下索引的长度限制的详细内容...