Mysql之Specified key was too long; max key length is 767 bytes-yellowcong

偏执的太偏执、 2022-05-27 11:24 145阅读 0赞

> 今天导数据库的时候，抽风，报了这个错，当时找了资料，也没太好用啊，导致这个问题的原因索引的字段都太长了，mysql受不了，解决方法就是，让mysql支持比较长的索引,然后在插入表的时候，添加`ROW_FORMAT=DYNAMIC` ，自动格式化索引。

## ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes ##

![这里写图片描述][SouthEast]

查看数据库信息

show variables like 'innodb_large_prefix';  
    show variables like 'innodb_file_format';
    
    --修改最大索引长度限制
    set global innodb_large_prefix=1; 
    set global innodb_file_format=BARRACUDA;
    -- 添加
    set global innodb_file_format_max=BARRACUDA;

![这里写图片描述][SouthEast 1]

![这里写图片描述][SouthEast 2]

2、修改插入sql的语句添加`ROW_FORMAT=DYNAMIC`

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

![这里写图片描述][SouthEast 3]

## 参考文章 ##

[https://help.aliyun.com/knowledge\_detail/41707.html][https_help.aliyun.com_knowledge_detail_41707.html]

[SouthEast]: /images/20220527/da4a176b760b4fa7a6f041b3d617e73d.png
[SouthEast 1]: /images/20220527/afd18ee84ad24c1fa6a1e54a6e19165b.png
[SouthEast 2]: /images/20220527/20b4e75686324e74867ca73b9057c6d1.png
[SouthEast 3]: /images/20220527/fde34512582a490d8d480ed3e7cac2f4.png
[https_help.aliyun.com_knowledge_detail_41707.html]: https://help.aliyun.com/knowledge_detail/41707.html