mysql索引过长Specialed key was too long的解决方法_Mysql

mysql索引过长Specialed key was too long的解决方法

2021-12-02 16:51一灰灰 Mysql

在创建要给表的时候遇到一个有意思的问题，提示Specified key was too long; max key length is 767 bytes，本文就来介绍一下解决方法，如果你也遇到此类问题，可以参考一下

				?

									CREATE TABLE `test_table` (

									  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

									  `name` varchar(1000) NOT NULL DEFAULT '',

									  `link` varchar(1000) NOT NULL DEFAULT '',

									  PRIMARY KEY (`id`),

									  KEY `name` (`name`)

									) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

我们可以看到，对于name，我们设置长度为1000可变字符，因为采用utf8mb4编码, 所以它的大小就变成了 1000 * 4 > 767
所以再不修改其他配置的前提下，varchar的长度大小应该是 767 / 4 = 191

有兴趣的同学可以测试下，分别指定name大小为191, 192时，是不是前面的可以创建表成功，后面的创建表失败，并提示错误Specified key was too long; max key length is 767 bytes

解决办法一

使用innodb引擎
启用innodb_large_prefix选项，修改约束扩展至3072字节
重新创建数据库

my.cnf配置

				?

									set global innodb_large_prefix=on;

									set global innodb_file_per_table=on;

									set global innodb_file_format=BARRACUDA;

									set global innodb_file_format_max=BARRACUDA;

上面这个3072字节的得出原因如下

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。
所以一个记录最多不能超过8k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。
由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

解决办法二

在创建表的时候，加上 row_format=DYNAMIC

				?

									CREATE TABLE `test_table` (

									  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

									  `name` varchar(255) NOT NULL DEFAULT '',

									  `link` varchar(255) NOT NULL DEFAULT '',

									  PRIMARY KEY (`id`),

									  KEY `name` (`name`)

									) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=DYNAMIC;