• 极客文库-知识库上线!
  • 极客文库小编@勤劳的小蚂蚁,为您推荐每日资讯,欢迎关注!
  • 每日更新优质编程文章!
  • 更多功能模块开发中。。。

MySQL 问题分析 : ERROR 1071 (42000) : Specified key was too long


今天在MySQL 5.6 版本的数据库中修改 InnoDB 表字段长度时遇到了”ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”错误,第一次遇到这个错误,遂花了点学习、研究过、总结这个问题。 

我们先来创建一个测试表,构造这样的错误。

mysql> use MyDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
  
Database changed
mysql> CREATE TABLE `TEST` (
    ->   `CODE_NAME` varchar(100) NOT NULL DEFAULT ”,
    ->   `CODE_SEQ` smallint(6) NOT NULL DEFAULT ‘1’,
    ->   `ACTIVE` char(1) DEFAULT ‘Y’,
    ->   `CODE_VALUE1` varchar(250) DEFAULT NULL,
    ->   PRIMARY KEY (`CODE_NAME`,`CODE_SEQ`),
    ->   KEY `IDX_GEN_CODE` (`CODE_NAME`,`CODE_VALUE1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
  
  
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql>

其实这个“ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes”错误是指超出索引字节的限制,并不是指字段长度限制。在官方文档“Limits on InnoDB Tables”有关于这方面的介绍、描述(详情请见参考资料):

MySQL 5.6 文档内容如下 

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.
 
Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.
 
The limits that apply to index key prefixes also apply to full-column index keys.

MySQL 5.7 文档内容如下:

If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.
 
innodb_large_prefix is deprecated and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.
 
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.
 
Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enablinginnodb_large_prefix on the master if it cannot also be enabled on slaves.
 
The limits that apply to index key prefixes also apply to full-column index keys.

如果启用了系统变量 innodb_large_prefix(默认启用,注意实验版本为MySQL 5.6.41,默认是关闭的,MySQL 5.7 默认开启),则对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表,索引键前缀限制为 3072 字节。如果禁用 innodb_large_prefix,则对于任何行格式的表,索引键前缀限制为 767 字节。

innodb_large_prefix 将在以后的版本中删除、弃用。在 MySQL 5.5 中引入了 innodb_large_prefix,用来禁用大型前缀索引,以便与不支持大索引键前缀的早期版本的 InnoDB 兼容。

对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表,索引键前缀长度限制为 767 字节。例如,您可能会在 TEXT 或 VARCHAR 列上使用超过 255 个字符的列前缀索引达到此限制,假设为 utf8mb3 字符集,并且每个字符最多包含 3 个字节。

尝试使用超出限制的索引键前缀长度会返回错误。要避免复制配置中出现此类错误,请避免在主服务器上启用 enableinnodb_large_prefix(如果无法在从服务器上启用)。

适用于索引键前缀的限制也适用于全列索引键。

注意:上面是 767 个字节,而不是字符,具体到字符数量,这就跟字符集有关。GBK 是双字节的,UTF-8 是三字节的

解决方案:

1:启用系统变量 innodb_large_prefix

注意:光有这个系统变量开启是不够的。必须满足下面几个条件:

  • 系统变量 innodb_large_prefix 为 ON
  • 系统变量 innodb_file_format 为 Barracuda
  • ROW_FORMAT 为 DYNAMIC 或 COMPRESSED

如下测试所示:

mysql> show variables like ‘%innodb_large_prefix%’;
+———————+——-+
| Variable_name       | Value |
+———————+——-+
| innodb_large_prefix | OFF   |
+———————+——-+
1 row in set (0.00 sec)
  
mysql> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
  
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> 
mysql> show variables like ‘%innodb_file_format%’;
+————————–+———–+
| Variable_name            | Value     |
+————————–+———–+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
+————————–+———–+
3 rows in set (0.01 sec)
  
mysql> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)
  
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql> 
  
mysql> 
mysql> show table status from MyDB where name=’TEST’G;
*************************** 1. row ***************************
           Name: TEST
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-09-20 13:53:49
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
  
mysql>  ALTER TABLE TEST ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> show table status from MyDB where name=’TEST’G;
*************************** 1. row ***************************
           Name: TEST
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2018-09-20 14:04:05
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: row_format=DYNAMIC
        Comment: 
1 row in set (0.00 sec)
  
ERROR: 
No query specified
  
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


2:使用前缀索引解决这个问题

之所以要限制索引键值的大小,是因为性能问题,而前缀索引能很好的解决这个问题。不需要修改任何系统变量。

mysql> show index from TEST;
…………………………….
  
mysql> ALTER TABLE TEST DROP INDEX IDX_GEN_CODE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> CREATE IDX_GEN_CODE TEST ON TEST (CODE_NAME, CODE_VALUE1(12));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  
mysql> ALTER TABLE TEST MODIFY CODE_VALUE1 VARCHAR(350);
Query OK, 1064 rows affected (0.08 sec)
Records: 1064  Duplicates: 0  Warnings: 0

问题延伸: 为什么 InnoDB 的索引字节数限制为 767 字节? 而不是 800 字节呢? 这样限制又是出于什么具体性能的考虑呢? 暂时还没有弄清楚这些细节问题! 

参考资料:


丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:MySQL 问题分析 : ERROR 1071 (42000) : Specified key was too long
喜欢 (0)
[247507792@qq.com]
分享 (0)
勤劳的小蚂蚁
关于作者:
温馨提示:本文来源于网络,转载文章皆标明了出处,如果您发现侵权文章,请及时向站长反馈删除。

欢迎 注册账号 登录 发表评论!

  • 精品技术教程
  • 编程资源分享
  • 问答交流社区
  • 极客文库知识库

客服QQ

247507792

工作时间:09:00-23:00

新浪微博:点我访问

个人博客:点我访问