【MySql】Specified key was too long; max key length is 767 bytes的解决方案

哈根达斯
2022-04-17 / 0 评论 / 321 阅读 / 正在检测是否收录...

问题背景

由于业务需要,需要做数据库迁移,迁移由5.7版本得mysql到现有的mysql5.6版本,主要是因为更innodb_large_prefix 参数有关

由于MySQL的InnoDB引擎表索引字段长度的限制为767字节,因此对于多字节字符集的大字段或者多字段组合,创建索引时会出现该问题。在5.6中innodb_large_prefix 默认关闭,在5.7中默认开启。在5.6中默认为 off

Specified key was too long; max key length is 767 bytes

原来是主键的长度太长,最大长度仅支持767字节,注意,这里的是字节。

那么,我定义一个主键是varchar(500)的字段,不管我使用utf8编码,那么500字符长度将占用5003的字节长度(utf8最多支持3个字节长度);或是我使用utf8mb4编码,那么500字符长度将占用5004的字节长度(utf8mb4最多支持4个字节长度),都大大超过了767字节。

如何去解决呢?

先检查一下是不是数据库被限制了索引的大小,查询innodb_large_prefix这个字段,这个字段限制了索引前缀的大小。

关闭此限制后,索引前缀的大小将可以达到3072字节。

方法1:修改全局变量

修改全局变量 ,当mysql重启后将失效,建议直接修改配置文件

SHOW variables like 'innodb_large_prefix';

如果查询的值是OFF的话 执行下面命令

SET GLOBAL INNODB_LARGE_PREFIX = ON;

另外,innodb_large_prefix这个属性在5.6上是默认关闭的,而在5.7上是默认开启的。

执行完了 之后 还得查看当前的innodb_file_format引擎格式类型是不是BARRACUDA

执行

SHOW variables like 'innodb_file_format';

如果不是的话则需要修改

SET GLOBAL innodb_file_format = BARRACUDA;
方法二:修改配置
[mysqld]
innodb_large_prefix = on
innodb_file_format = BARRACUDA

最后,创建表的时候,还需要指定表的 row format 格式为 Dynamic 或者 Compressed,如下示例:

CREATE TABLE test(
 
  name varchar(500) CHARACTER SET utf8 COLLATE utf8_bin,
 
  PRIMARY KEY (`name `) USING BTREE
 
) ENGINE = InnoDB  CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

版权声明:本文为CSDN博主「SunAlwaysOnline」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
参考原文链接:https://blog.csdn.net/qq_33591903/article/details/103927547

0

评论 (0)

取消