MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

问题现象

执行如下sql语句时报错

CREATE TABLE IF NOT EXISTS `student` (
    `name` VARCHAR ( 256 ) NOT NULL COMMENT '学生名字，不考虑重名问题',
    `age` INT ( 11 ) NOT NULL COMMENT '年龄',
    `address` VARCHAR ( 1024 ) DEFAULT NULL COMMENT '地址',
    PRIMARY KEY ( `name` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '学生基本信息表';

报错信息

#1071 - Specified key was too long; max key length is 767 bytes

问题排查

MYSQL 5.6版本（以及更早版本）的InnoDB引擎限制索引数据长度为767bytes，MyISAM的限制是1000bytes。在MYSQL 5.7版本限制已经放开到3072bytes。

查询MYSQL版本

SELECT VERSION();

由于student建表语句字符集格式为utf8，在MySQL中 的 utf8 实际上是一个不完整的 UTF-8 实现，它最多支持 3字节，因此不能存储需要 4字节 的 Unicode 字符（如表情符号、部分罕见汉字等）。MySQL 的 utf8mb4 是完整的 UTF-8 实现，支持最多 4字节，可以存储所有 Unicode 字符。

因此VARCHAR(256)的字节数为

256*3=768>767

所以varchar最大长度为VARCHAR(255)。

5.7版本可以通过配置项控制是否放开，如果想要在5.7版本（及以后版本）中放开767的限制，可以更改数据库配置

# 查询是否开启大索引，是否开启
SHOW VARIABLES LIKE 'innodb_large_prefix';
# 查询INNODB文件格式
SHOW VARIABLES LIKE 'innodb_file_format';
SHOW VARIABLES LIKE 'innodb_file_format_max';
# 设置开启大索引
set global innodb_large_prefix=on;
# 设置INNODB文件格式配置为Barracuda
set global innodb_file_format=Barracuda;
set global innodb_file_format_max=Barracuda;

问题拓展

innodb_file_format配置项

innodb_file_format配置用于控制InnoDB文件编码格式，有两个配置项，四种格式

• Antelope（羚羊）：是Built-in-InnoDB（MySQL内置的InnoDB）支持文件格式的代号，有两种“数据表格式”（row_format）：Redundant（冗余）、Compact（紧凑）

• Barracuda（梭子鱼）：是InnoDB Plugin支持的文件格式，在原来的基础上新增了两种数据表格式的支持：Dynamic 和 Compressed

Antelope

在 Antelope 两种格式下，如果blob列值长度 <= 768 bytes，就不会发生行溢出(page overflow)，内容都在数据页(B-tree Node)；如果列值长度 > 768字节，那么前768字节依然在数据页，而剩余的则放在溢出页(off-page)。对于InnoDB来说，内存是极为珍贵的，如果把768字节长度的blob都放在数据页，虽然可以节省部分IO，但相对来说能缓存行数就变少，也就是能缓存的索引值变少了，降低了索引效率。

Barracuda

Barracuda 的两种行格式对blob采用完全行溢出，即聚集索引记录(数据页)里面只保留20字节的指针，指向真实存放它的溢出段地址。

dynamic 行格式，列存储是否放到off-page页，主要取决于行大小，它会把行中最长的那一列放到off-page，直到数据页能存放下两行。TEXT/BLOB列 <=40 bytes 时总是存放于数据页。这种方式可以避免compact那样把太多的大列值放到 B-tree Node，因为dynamic格式认为，只要大列值有部分数据放在off-page，那把整个值放入都放入off-page更有效。

compressed 物理结构上与dynamic类似，但是对表的数据行使用zlib算法进行了压缩存储。在long blob列类型比较多的情况下用，可以降低off-page的使用，减少存储空间(一般40%左右)，但要求更高的CPU，buffer pool里面可能会同时存储数据的压缩版和非压缩版，所以也多占用部分内存。