MySQL5.6建索引时遇到 Specified key was too long； max key length is 767 bytes错误提示解决办法

一、错误提示

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

二、原因分析

在数据库中，索引的字段设置太长了，导致不支持。【根本原因：5.6版本的innodb大长度前缀默认是关闭的】。

mysql建立索引时，数据库计算key的长度是累加所有index用到的字段的char长度，在按照下面的比例乘起来

不能超过限定的key长度767：

latin1 = 1 byte = 1 character

uft8 = 3 byte = 1 character

utf8mb4 = 4byte = 1character

gbk = 2 byte = 1 character

做个实验：

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(190) NOT NULL,
  `registry_value` varchar(250) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
 
registry_key 190 * 4 = 760因此创建成功
 
若将registry_key的字节数改成192，则195 * 4 = 780 则创建不成功

注意

网上说，如果是联合索引的话，应该是两个索引的字节加起来，然后折算成字节数。例如：

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(190) NOT NULL,
  `registry_value` varchar(110) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_key`, `registry_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
那么索引需要的字节数是：（190 + 110） * 4 = 1200
创建不成功
 
 
但是实际上呢，是能创建成功。
在创建索引的时候进行了优化，取字节数最长的那个 190 * 4 = 760因此能创建成功。

 三、解决方法

1、第一种解决方法

修改索引的varchar字符，只要让字符 * 字节数 < 767即可，所以网上推荐 缩小字符数

但是有时某个字段的字符数是一定要足够大的，这时候用第二种方式。

2、第二种解决方法

// 查看
 
show variables like "innodb_large_prefix";
 
show variables like "innodb_file_format";
 
//修改最大索引长度限制
set global innodb_large_prefix=1;
或
set global innodb_large_prefix=on;
 
set global innodb_file_format=BARRACUDA;

 

修改插入sql的语句添加ROW_FORMAT=DYNAMIC；

create table idx_length_test_02
(
  id int auto_increment primary key,
  name varchar(255)
)ROW_FORMAT=DYNAMIC ENGINE=InnoDB default charset utf8mb4;