RDS for MySQL添加索引时报错Specified key was too long; max key length is 767 bytes

场景描述

创建表或者添加索引时，当索引列长度超出限制报ERROR 1071或ERROR 1709错误。

ERROR 1071: Specified key was too long; max key length is 767 bytes

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

原因分析

MySQL索引的最大长度受多方面限制，包括：

• 版本限制
  • RDS MySQL 5.6：参数“innodb_large_prefix”默认值为OFF，最大索引长度767字节。
  • RDS MySQL 5.7：参数“innodb_large_prefix”默认值为ON，最大索引长度3072字节。
  • RDS MySQL 8.0：大索引功能为内置标准，无“innodb_large_prefix”参数，默认支持3072字节索引。
• 行格式（ROW_FORMAT）
  • COMPACT和REDUNDANT：最大索引长度767字节。
  • DYNAMIC和COMPRESSED：最大索引长度3072字节。
• 字符集

  多字节字符集会导致索引占用更多空间，容易超出限制。

解决方案

1. 在RDS界面查看MySQL引擎版本，如果是5.6、5.7版本，确保参数“innodb_large_prefix”值为ON。

   更多操作，请参见修改RDS for MySQL实例参数。

2. 查看表的行格式，确保行格式为DYNAMIC。
   • 对于已存在的表，确认表的ROW_FORMAT。

     select row_format from information_schema.tables where table_name = 'table_name';

     如果表的行格式不是DYNAMIC，需要先重建表为DYNAMIC格式后再添加索引。

     ALTER TABLE `table_name` ROW_FORMAT=DYNAMIC;

     

     ALTER TABLE ... ROW_FORMAT=DYNAMIC;会重构整个表数据，对于大表，它将消耗大量I/O资源和时间并可能锁表。请务必在业务低峰期或维护时间窗执行，并在执行前做好数据备份。

   • 对于新建的表，确认“innodb_default_row_format”参数是否为DYNAMIC。
     如果不是，则需要在建表时指定ROW_FORMAT为DYNAMIC。

     CREATE TABLE `new_table` ( 
       -- ... 表结构定义 ...
     ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;