mysql innodb utf8_innodb utf8字符集下索引的长度限制

单一字段的索引长度限制为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