MySQL 错误 #1071 - 指定的键太长；最大密钥长度为 767 字节 [英] MySQL Error #1071 - Specified key was too long; max key length is 767 bytes

问题描述

当我执行以下命令时:

ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);

我收到此错误消息:

#1071 - Specified key was too long; max key length is 767 bytes

关于 column1 和 column2 的信息:

Information about column1 and column2:

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

我认为 varchar(20) 只需要 21 个字节，而 varchar(500) 只需要 501 个字节.所以总字节数是 522，小于 767.那么为什么我会收到错误消息?

I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

#1071 - Specified key was too long; max key length is 767 bytes

推荐答案

767 bytes in MySQL version 5.6(和之前的版本)，是 规定了 InnoDB 表的前缀限制.MyISAM 表的长度为 1,000 字节.在 MySQL 5.7 版(及更高版本)中，此限制已增加到 3072 字节.

767 bytes in MySQL version 5.6 (and prior versions), is the stated prefix limitation for InnoDB tables. It's 1,000 bytes long for MyISAM tables. This limit has been increased to 3072 bytes In MySQL version 5.7 (and upwards).

您还必须注意，如果您在 utf8mb4 编码的大字符或 varchar 字段上设置索引，则必须划分最大索引前缀长度767 字节(或 3072 字节)乘以 4 得到 191.这是因为 utf8mb4 字符的最大长度是四个字节.对于 utf8 字符，它将是三个字节，导致最大索引前缀长度为 255(或减去空终止符，254 个字符).

You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 255 (or minus null-terminator, 254 characters).

您可以选择的一种方法是为 VARCHAR 字段设置下限.

One option you have is to just place lower limit on your VARCHAR fields.

另一种选择(根据对此问题的回应)是获取列的子集而不是整个金额，即:

Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

调整，因为您需要获取要应用的密钥，但我想知道是否值得检查有关此实体的数据模型以查看是否有可能进行改进，这将使您能够实施预期的业务规则而不会遇到问题MySQL 限制.

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements possible, which would allow you to implement the intended business rules without hitting the MySQL limitation.

这篇关于MySQL 错误 #1071 - 指定的键太长；最大密钥长度为 767 字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！