[Err] 1071 - Specified key was too long； max key length is 767 bytes，【各版本mysql均已解决】

[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.

错误信息如下所示：

[SQL]

CREATE TABLE `permissions` (
    `role` varchar(50) NOT NULL,
    `resource` varchar(512) NOT NULL,
    `action` varchar(8) NOT NULL,
    UNIQUE INDEX `uk_role_permission` (`role`,`resource`,`action`) USING BTREE
);
[Err] 1709 - Index column size too large. The maximum column size is 767 bytes.

 

问题原因：

索引  “uk_role_permission” 的长度 4*255>767 ，

是由参数innodb_large_prefix 控制，

此参数控制索引前缀长度的，不开启最长的长度是 765byte，开启最长3072byte，一般出现此报错的数据库版本是小于MYSQL5.7，同时不同的版本开启的方式也略有不同。

一、在mysql5.6中的解决方法：

 1.1、set global innodb_large_prefix=on;

          或  set global innodb_large_prefix=1;

 show variables like "%innodb_large_prefix%"

1.2、set global innodb_file_format=Barracuda ;

   set global innodb_file_format_max=Barracuda ;

 

 修改后结果如下：

show variables like '%innodb_file_format%'

 1.3、已经创建的表修改 row_format 的格式：

alter table table_name1 row_format=dynamic；

1.4、或者在创建表时指定row_format：

错误案例一：

CREATE TABLE `permissions` (
    `role` varchar(50) NOT NULL,
    `resource` varchar(512) NOT NULL,
    `action` varchar(8) NOT NULL,
    UNIQUE INDEX `uk_role_permission` (`role`,`resource`,`action`) USING BTREE
) row_format=dynamic;

 

错误案例二：

解决方法如下：

create table sys_dict_data
(
    id                   bigint NOT NULL COMMENT 'id',
    dict_type_id         bigint NOT NULL COMMENT '字典类型ID',
    dict_label           varchar(255) NOT NULL COMMENT '字典标签',
    dict_value           varchar(255) COMMENT '字典值',
    remark               varchar(255) COMMENT '备注',
    sort                 int unsigned COMMENT '排序',
    creator              bigint COMMENT '创建者',
    create_date          datetime COMMENT '创建时间',
    updater              bigint COMMENT '更新者',
    update_date          datetime COMMENT '更新时间',
    primary key (id),
    unique key uk_dict_type_value (dict_type_id, dict_value),
    key idx_sort (sort)
)ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COMMENT='字典数据' row_format=dynamic;

 

二、在mysql5.7中不存在该问题，默认格式正常，只要字段长度符合即可。

innodb_large_prefix=1         (默认值 1)

innodb_default_row_format=dynamic         (默认是dynamic) 

innodb_file_format=Barracuda          (默认值 Barracuda)

需要调整的参数的默认值是符合要求

 三、在mysql8.0版本中， 此参数已废弃，默认的行格式最长3072 byte。

总结：

        一般在MySQL 5.7及以上版本不会遇到此问题，只有在MySQL 5.6版本会遇到此问题，建议MySQL 5.6 升级到MySQL 5.7，因为MySQL 5.6 修改比较麻烦。