MySQL:Specified key was too long; max key length is 767 bytes
1、先检查索引
show variables like 'innodb_large_prefix';
off的话,执行
set global innodb_large_prefix=on;
2、
查看当前innodb_file_format引擎格式类型是不是BARRACUDA
show variables like 'innodb_file_format';
否需修改
set global innodb_file_format = BARRACUDA;
ON :Innodb表的行记录格式是Dynamic或Compressed的前提下,单列索引长度上限扩展到3072个字节
OFF:Innodb表的单例索引长度最多为767个字节,索引长度超出后,主键索引会创建失败,辅助索引会被截断成为前缀索引
767个字节,utf8mb4 每个字符占用4个字节,所以索引最大长度只能为191个字符,即varchar(191)
Mysql5.6中默认为off;Mysql5.7中默认为on,无需用修改。
家里的电脑MySQL5.7,sql文件正常写入了,公司是5.6版本报错Specified key was too long; max key length is 767 bytes;
另:mysql #1709 - Index column size too large. The maximum column size is 767 bytes.
创建表SQL语句中检查row_format值改成 row_format=dynamic
记录一下。