一招解决MySql Specified key was too long； max key length is 767 bytes

背景

今天迁移环境上阿里云的DMS建表

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(255) NOT NULL,
  `registry_value` varchar(255) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_group`,`registry_key`,`registry_value`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4
 

直接提示 Specified key was too long; max key length is 767 bytes

并让我打开MySQL常见错误处理方法 · 语雀 查看解决方式

十三、Specified key was too long; max key length is 767 bytes

原因：mysql的“字符串类型”(varchar、char等)字段作为索引时，有一个约束单个索引字段存储长度不能超过767字节。

按照表为utf8mb4字符集时，一个字符需要4个字节存储。那么最大定义索引前缀为 767/4=191.即字段a varchar(500)要建立索引时需要定义前缀索引 a(191)，不能超过191的一个值。

处理方法：utf8为3个字节存储一个字符，gbk为2个字节存储一个字符，依次类推得到对应字符串类型字段的前缀索引长度修正即可。结构设计修改路径：索引=》包含列=》前缀长度，进行设置。

如果是【库表同步】请直接联系你的DBA修改为和【源】数据库一致的编码。如何查看我的业务DBA是谁？

PS：索引767问题排查

显然这个方式并不是我想要的，很多业务已经测试过不可能去缩小字段

解决办法

1. 查看MySQL数据库当前的配置；

show variables like 'innodb_large_prefix'; 

2. 修改配置

在SQLYog、Navicat、命令行都可以执行以下语句

set global innodb_large_prefix=ON;//开启不限制索引长度

执行次命令需要一定的权限，如没有需要找管理员 

3. 建表时需要指定ROW_FORMAT=Dynamic或compressed

CREATE TABLE `xxl_job_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registry_group` varchar(50) NOT NULL,
  `registry_key` varchar(255) NOT NULL,
  `registry_value` varchar(255) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_g_k_v` (`registry_group`,`registry_key`,`registry_value`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 ROW_FROMAT=DYnamic;

现在就可以不报错了！！！！