一、问题描述
描述:创建唯一索引时,数据库报错:
specified key was too long max key length is 767bytes
数据库版本:5.5.22-log
引擎: InnoDB
数据库结构为:
CREATE TABLE `cdkey` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`cdkey` varchar(200) NOT NULL COMMENT '邀请码',
`created_time` datetime NOT NULL COMMENT '创建时间',
`updated_time` datetime NOT NULL COMMENT '修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='邀请码'
创建语句如下:
alter table `cdkey` add index `INDEX` (`cdkey`);
运行报错:
二、问题分析
允许索引字段前缀的字节限制为 767 bytes,且:
- 当字符集为 utf8mb4 时,一个字符对应 4 bytes;
- 当字符集为 utf8 时,一个字符对应 3 bytes;
所以对于(一)中的问题:
字符集为 utf8mb4, 所以可申请索引的 varchar 长度为:767/4 ≈ 191;
而字符集为 utf8 时,可设置的创建索引 varchar 长度为: 767/3 ≈ 255
三、版本差异
官方文档:innodb_large_prefix
在 5.6.3 之后的版本,字节限制提高到了 3072 bytes.
测试环境:5.7.9
引擎: InnoDB
数据库结构:
CREATE TABLE `test_index2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(1000) NOT NULL,
`uid` int(11) NOT NULL,
`created_time` datetime NOT NULL,
`updated_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
运行索引创建命令:
alter table `test_index2` add index `INDEX` (`name`);
报错为:
从(二)可得:
字符集为 utf8mb4 时,限制 varchar 字符为:3072/4 = 768;
字符集为 utf8 时,限制 varchar 字符为:3072/3 = 1024;
因此字符集为 utf8mb4 时,将 name 字段索引前缀长度控制在 768 以内即可成功创建索引。