MySQL #1071 - 指定的密钥太长；最大密钥长度为 767 字节

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

提问by Steven

When I executed the following command:

当我执行以下命令时：

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

I got this error message:

我收到此错误消息：

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

Information about column1 and column2:

关于 column1 和 column2 的信息：

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

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?

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

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

采纳答案by OMG Ponies

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

767 字节是MySQL 5.6 版（和之前版本）中 InnoDB 表的规定前缀限制。MyISAM 表的长度为 1,000 字节。在 MySQL 5.7 及更高版本中，此限制已增加到 3072 字节。

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 254.

您还必须注意，如果您在 utf8mb4 编码的 big char 或 varchar 字段上设置索引，则必须将 767 字节（或 3072 字节）的最大索引前缀长度除以 4，结果为 191。这是因为utf8mb4 字符的最大长度为四个字节。对于 utf8 字符，它将是三个字节，导致最大索引前缀长度为 254。

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

您拥有的一种选择是仅对 VARCHAR 字段设置下限。

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) );

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 that would allow you to implement the intended business rules without hitting the MySQL limitation.

调整您需要获取要应用的密钥，但我想知道是否值得检查有关此实体的数据模型，以查看是否有改进可以让您实现预期的业务规则而不会遇到 MySQL 限制。

回答by PinkTurtle

If anyone is having issues with INNODB / Utf-8 trying to put an UNIQUEindex on a VARCHAR(256)field, switch it to VARCHAR(255). It seems 255 is the limitation.

如果有人在尝试UNIQUE在VARCHAR(256)字段上放置索引时遇到INNODB / Utf-8 问题，请将其切换到VARCHAR(255). 似乎255是限制。

回答by Aley

When you hit the limit. Set the following.

当你达到极限。进行以下设置。

• INNODB utf8VARCHAR(255)
• INNODB utf8mb4VARCHAR(191)

• 创新数据库 utf8VARCHAR(255)
• 创新数据库 utf8mb4VARCHAR(191)

回答by morganwahl

MySQL assumes worst case for the number of bytes per character in the string. For the MySQL 'utf8' encoding, that's 3 bytes per character since that encoding doesn't allow characters beyond U+FFFF. For the MySQL 'utf8mb4' encoding, it's 4 bytes per character, since that's what MySQL calls actual UTF-8.

MySQL 假设字符串中每个字符的字节数是最坏的情况。对于 MySQL 'utf8' 编码，每个字符 3 个字节，因为该编码不允许超过U+FFFF. 对于 MySQL 'utf8mb4' 编码，它是每个字符 4 个字节，因为这就是 MySQL 所说的实际 UTF-8。

So assuming you're using 'utf8', your first column will take 60 bytes of the index, and your second another 1500.

因此，假设您使用的是“utf8”，那么您的第一列将占用索引的 60 个字节，而您的第二列将占用 1500 个字节。

回答by Raza Ahmed

run this query before your query:

在查询之前运行此查询：

SET @@global.innodb_large_prefix = 1;

this will increase limit to 3072 bytes.

这会将限制增加到3072 bytes.

回答by Ali Shaukat

Solution For Laravel Framework

Laravel 框架的解决方案

As per Laravel 5.4.* documentation; You have to set the default string length inside the bootmethod of the app/Providers/AppServiceProvider.phpfile as follows:

根据Laravel 5.4.* 文档；您必须boot在app/Providers/AppServiceProvider.php文件的方法中设置默认字符串长度，如下所示：

use Illuminate\Support\Facades\Schema;

public function boot() 
{
    Schema::defaultStringLength(191); 
}

Explanation of this fix, given by Laravel 5.4.* documentation:

这个修复的解释，由Laravel 5.4.* 文档给出：

Laravel uses the utf8mb4character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLengthmethod within your AppServiceProvider.

Alternatively, you may enable the innodb_large_prefixoption for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Laravelutf8mb4默认使用字符集，其中包括支持在数据库中存储“表情符号”。如果您运行的 MySQL 版本早于 5.7.7 版本或 MariaDB 早于 10.2.2 版本，则可能需要手动配置迁移生成的默认字符串长度，以便 MySQL 为它们创建索引。您可以通过调用这个配置Schema::defaultStringLength你的内法AppServiceProvider。

或者，您可以innodb_large_prefix为您的数据库启用该选项。有关如何正确启用此选项的说明，请参阅您的数据库文档。

回答by Amber

What character encoding are you using? Some character sets (like UTF-16, et cetera) use more than one byte per character.

您使用的是什么字符编码？某些字符集（如 UTF-16 等）每个字符使用一个以上的字节。

回答by Buksy

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?

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

UTF8 requires 3 bytes per characterto store the string, so in your case 20 + 500 characters = 20*3+500*3 = 1560bytes which is more thanallowed 767bytes.

UTF8 每个字符需要 3 个字节来存储字符串，因此在您的情况下，20 + 500 个字符 = 20*3+500*3 = 1560个字节，这超过了允许的767个字节。

The limit for UTF8 is 767/3 = 255 characters, for UTF8mb4 which uses 4 bytes per character it is 767/4 = 191 characters.

UTF8 的限制是 767/3 = 255 个字符，对于每个字符使用 4 个字节的 UTF8mb4，它是 767/4 = 191 个字符。

---

---

There are two solutions to this problem if you need to use longer column than the limit:

如果您需要使用比限制更长的列，有两种解决方案：

1. Use "cheaper" encoding (the one that requires less bytes per character)
   In my case, I needed to add Unique index on column containing SEO string of article, as I use only [A-z0-9\-]characters for SEO, I used latin1_general_ciwhich uses only one byte per character and so column can have 767 bytes length.
2. Create hash from your column and use unique index only on that
   The other option for me was to create another column which would store hash of SEO, this column would have UNIQUEkey to ensure SEO values are unique. I would also add KEYindex to original SEO column to speed up look up.

1. 使用“更便宜”的编码（每个字符需要更少字节的编码）
   在我的情况下，我需要在包含 SEO 文章字符串的列上添加唯一索引，因为我只使用[A-z0-9\-]字符进行 SEO，我使用latin1_general_ci它每个字符只使用一个字节所以列可以有 767 个字节的长度。
2. 从您的列创建哈希并仅在该列上使用唯一索引
   对我来说另一个选择是创建另一个列来存储 SEO 的哈希，该列将具有UNIQUE确保 SEO 值唯一的键。我还会KEY向原始 SEO 列添加索引以加快查找速度。

回答by vee

The answer about why you get error message was already answered by many users here. My answer is about how to fix and use it as it be.

许多用户已经在这里回答了有关为什么会收到错误消息的答案。我的答案是关于如何修复和使用它。

Refer from this link.

从这个链接参考。

1. Open MySQL client (or MariaDB client). It is a command line tool.
2. It will ask your password, enter your correct password.
3. Select your database by using this command use my_database_name;

1. 打开 MySQL 客户端（或 MariaDB 客户端）。它是一个命令行工具。
2. 它会询问您的密码，输入正确的密码。
3. 使用此命令选择您的数据库 use my_database_name;

Database changed

数据库已更改

1. set global innodb_large_prefix=on;

4. set global innodb_large_prefix=on;

Query OK, 0 rows affected (0.00 sec)

查询正常，0 行受影响（0.00 秒）

1. set global innodb_file_format=Barracuda;

5. set global innodb_file_format=Barracuda;

Query OK, 0 rows affected (0.02 sec)

查询正常，0 行受影响（0.02 秒）

1. Go to your database on phpMyAdmin or something like that for easy management. > Select database > View table structure> Go to Operationstab. > Change ROW_FORMATto DYNAMICand save changes.
2. Go to table's structuretab > Click on Uniquebutton.
3. Done. Now it should has no errors.

6. 转到 phpMyAdmin 上的数据库或类似的东西，以便于管理。> 选择数据库 > 查看表结构> 转到操作选项卡。> 将ROW_FORMAT更改为DYNAMIC并保存更改。
7. 转到表的结构选项卡 > 单击唯一按钮。
8. 完毕。现在它应该没有错误。

The problem of this fix is if you export db to another server (for example from localhost to real host) and you cannot use MySQL command line in that server. You cannot make it work there.

此修复程序的问题是，如果您将 db 导出到另一台服务器（例如从 localhost 到真实主机），并且您无法在该服务器中使用 MySQL 命令行。你不能让它在那里工作。

回答by Anthony Rutledge

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

You got that message because 1 byte equals 1 character only if you use the latin-1character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.

您收到该消息是因为仅当您使用latin-1字符集时，1 个字节才等于 1 个字符。如果使用utf8，则在定义键列时每个字符将被视为 3 个字节。如果使用utf8mb4，则在定义键列时每个字符将被视为 4 个字节。因此，您需要将关键字段的字符限制乘以 1、3 或 4（在我的示例中），以确定关键字段试图允许的字节数。如果您使用的是 uft8mb4，则只能为原生 InnoDB 主键字段定义 191 个字符。只是不要违反 767 字节。