mysql中innodb utf8字符集varchar索引长度问题
索引总长度的限制是:
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
