max key length is 767 bytes

最新推荐文章于 2025-06-12 10:42:12 发布

转载最新推荐文章于 2025-06-12 10:42:12 发布 · 100 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/anxiaole/blog/1842644

文章标签：

#数据库 #python

本文详细解析了MySQL中MyISAM和InnoDB存储引擎下的索引长度限制，包括单列索引和组合索引的长度限制，并提供了如何解决超出长度限制的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

Specified key was too long;max key length is 767 bytes

https://blog.youkuaiyun.com/gladyoucame/article/details/16825779

https://blog.youkuaiyun.com/shaochenshuo/article/details/51064685

https://blog.youkuaiyun.com/bigdataf/article/details/78921409

关于mysql索引长度限制

From the manual at http://dev.mysql.com/doc/refman/5.6/en/create-table.html >>从5.6的官方文档中我们能找到如下双引号中解释
"For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length.
...
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters ...">>>对于myisam和innodb存储引擎，prefixes的长度限制分别为1000 bytes和767 bytes。注意prefix的单位是bytes，但是建表时我们指定的长度单位是字符。

A utf8 character can use up to 3 bytes. Hence you cannot index columns or prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8 characters. >>以utf8字符集为例，一个字符占3个bytes。因此在utf8字符集下，对myisam和innodb存储引擎创建索引的单列长度不能超过333个字符和255个字符

从上面可以看出，mysql 在创建单列索引的时候对列的长度是有限制的 myisam和innodb存储引擎下长度限制分别为1000 bytes和767 bytes。(注意bytes和character的区别)

2) 组合索引长度限制

对于innodb存储引擎，多列索引的长度限制如下：

每个列的长度不能大于767 bytes；所有组成索引列的长度和不能大于3072 bytes

问题解决方案

1) using col_name(length) syntax to specify an index prefix length.

alter table Table_Name add key(column_name(prefix_len));

或

create index index_name on Table_Name(column_name(prefix_len));

对列的前面某部分创建索引

2) 启用innodb_large_prefix参数

innodb_large_prefix >>启用innodb_large_prefix参数能够取消对于索引中每列长度的限制(但是无法取消对于索引总长度的限制)
启用innodb_large_prefix有如下前提条件：
Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.(Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) >>启用innodb_large_prefix必须同时指定innodb_file_format=barracuda，innodb_file_per_table=true，并且建表的时候指定表的row_format为dynamic或者compressed(mysql 5.6中row_format默认值为compact)
See Section 14.6.7, “Limits on InnoDB Tables” for the relevant maximums associated with index key prefixes under various settings. >>
For tables using the REDUNDANT and COMPACT row formats, this option does not affect the allowed key prefix length.

转载于:https://my.oschina.net/anxiaole/blog/1842644