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

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

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

您可以选择的一种方法是在 VARCHAR 字段上设置下限。

另一种选择（根据 response to this issue）是获取列的子集而不是整个数量，即：

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

根据需要进行调整以获取应用密钥，但我想知道是否值得审查有关此实体的数据模型以查看是否有可能进行改进，这将允许您在不影响 MySQL 限制的情况下实施预期的业务规则.

---

当你达到极限时。设置以下。

INNODB utf8 VARCHAR(255)

INNODB utf8mb4 VARCHAR(191)

---

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

---

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

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

---

在查询之前运行此查询：

SET @@global.innodb_large_prefix = 1;

这会将限制增加到 3072 bytes。

---

Laravel 框架解决方案

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

use Illuminate\Support\Facades\Schema;

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

此修复的说明，由 Laravel 5.4.* documentation 提供：

Laravel 默认使用 utf8mb4 字符集，其中包括支持在数据库中存储“表情符号”。如果您运行的 MySQL 版本早于 5.7.7 版本或 MariaDB 版本早于 10.2.2 版本，您可能需要手动配置迁移生成的默认字符串长度，以便 MySQL 为其创建索引。您可以通过在 AppServiceProvider 中调用 Schema::defaultStringLength 方法来配置它。或者，您可以为您的数据库启用 innodb_large_prefix 选项。有关如何正确启用此选项的说明，请参阅数据库的文档。

---

你使用什么字符编码？某些字符集（如 UTF-16 等）每个字符使用多个字节。

---

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

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

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

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

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

---

在您的导入文件中将 utf8mb4 替换为 utf8。

https://i.stack.imgur.com/W1AoE.png

---

此处的许多用户已经回答了有关您收到错误消息的原因的答案。我的回答是关于如何修复和使用它。

请参阅 this link。

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

数据库已更改

设置全局 innodb_large_prefix=on;

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

设置全局 innodb_file_format=梭子鱼；

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

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

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

---

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

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

---

5个解决方法：

限制在 5.7.7 (MariaDB 10.2.2?) 中提高。并且可以通过 5.6 (10.1) 中的一些工作来增加它。

如果您因为尝试使用 CHARACTER SET utf8mb4 而达到限制。然后执行以下操作之一（每个都有一个缺点）以避免错误：

⚈  Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
⚈  Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈  Use a "prefix" index -- you lose some of the performance benefits.
⚈  Or... Stay with older version but perform 4 steps to raise the limit to 3072 bytes:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)

-- http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

---

您可以添加一列 md5 的长列

---

我解决了这个问题：

varchar(200) 

替换为

varchar(191)

所有超过 200 个的唯一或主 varchar 键将它们替换为 191 或将它们设置为文本。

---

对于 laravel 5.7 到 9.0

要遵循的步骤

转到 App\Providers\AppServiceProvider.php。将此添加到提供程序使用 Illuminate\Support\Facades\Schema；在顶部。在 Boot 函数里面添加这个 Schema::defaultStringLength(191);

这一切，享受。

---

我们在尝试使用 utf8mb4 向 VARCHAR(255) 字段添加 UNIQUE 索引时遇到了这个问题。虽然这里已经很好地概述了这个问题，但我想为我们如何解决这个问题添加一些实用的建议。

使用 utf8mb4 时，字符计为 4 个字节，而在 utf8 下，它们可以计为 3 个字节。 InnoDB 数据库有一个限制，索引只能包含 767 个字节。所以使用utf8时，可以存储255个字符（767/3 = 255），但是使用utf8mb4时，只能存储191个字符（767/4 = 191）。

您绝对可以使用 utf8mb4 为 VARCHAR(255) 字段添加常规索引，但会发生索引大小自动截断为 191 个字符的情况 - 就像这里的 unique_key：

https://i.stack.imgur.com/3hGHE.png

这很好，因为常规索引只是用来帮助 MySQL 更快地搜索您的数据。整个字段不需要被索引。

那么，为什么 MySQL 会自动截断常规索引的索引，但在尝试对唯一索引执行此操作时会抛出显式错误？好吧，为了让 MySQL 能够确定被插入或更新的值是否已经存在，它需要实际索引整个值，而不仅仅是它的一部分。

归根结底，如果您想在某个字段上拥有唯一索引，则该字段的全部内容必须适合该索引。对于 utf8mb4，这意味着将 VARCHAR 字段长度减少到 191 个字符或更少。如果该表或字段不需要 utf8mb4，则可以将其放回 utf8 并能够保留 255 个长度的字段。

---

这是我的原始答案：

我只是删除数据库并像这样重新创建，错误消失了：drop database if exists rhodes;创建数据库 rhodes 默认 CHARACTER 设置 utf8 默认 COLLATE utf8_general_ci;

但是，它并不适用于所有情况。

这实际上是在字符集 utf8（或 utf8mb4）的 VARCHAR 列上使用索引的问题，而 VARCHAR 列的字符长度超过了一定长度。在 utf8mb4 的情况下，该特定长度为 191。

有关如何在 MySQL 数据库中使用长索引的更多信息，请参阅本文中的长索引部分：http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

---

为了解决这个问题，这对我来说就像一个魅力。

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

---

我对此主题进行了一些搜索，最后得到了一些自定义更改

对于 MySQL 工作台 6.3.7 版本图形界面可用

启动 Workbench 并选择连接。转到管理或实例并选择选项文件。如果 Workbench 询问您读取配置文件的权限，然后按两次 OK 允许它。在中心位置管理员选项文件窗口来了。转到 InnoDB 选项卡并检查 innodb_large_prefix 如果它未在 General 部分中选中。将 innodb_default_row_format 选项值设置为 DYNAMIC。

对于 6.3.7 以下的版本，直接选项不可用，因此需要使用命令提示符

以管理员身份启动 CMD。转到安装 mysql 服务器的目录大多数情况下，它位于“C:\Program Files\MySQL\MySQL Server 5.7\bin”，因此命令是“cd \”“cd Program Files\MySQL\MySQL Server 5.7\bin”。现在运行命令 mysql -u userName -p databasescheema 现在它要求输入各个用户的密码。提供密码并进入 mysql 提示符。我们要设置一些全局设置，一一输入下面的命令 set global innodb_large_prefix=on;设置全局 innodb_file_format=barracuda;设置全局 innodb_file_per_table=true;现在最后我们必须更改所需表的 ROW_FORMAT，默认情况下它的 COMPACT 我们必须将其设置为 DYNAMIC。使用以下命令 alter table table_name ROW_FORMAT=DYNAMIC;完毕

---

更改您的排序规则。你可以使用支持几乎所有的 utf8_general_ci

---

索引长度和 MySQL / MariaDB

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

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

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

来自博客的参考： https://www.scratchcode.io/specified-key-too-long-error-in-laravel/

来自官方 laravel 文档的参考： https://laravel.com/docs/5.7/migrations

---

创建表时只需将 utf8mb4 更改为 utf8 即可解决我的问题。例如：CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 到 CREATE TABLE ... DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;。

---

这解决了我的问题

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

---

根据下面给出的列，这 2 个变量字符串列使用 utf8_general_ci 排序规则（隐含 utf8 字符集）。

在 MySQL 中，utf8 字符集对每个字符最多使用 3 个字节。因此，它需要分配 500*3=1500 字节，这比 MySQL 允许的 767 字节大得多。这就是您收到此 1071 错误的原因。

换句话说，您需要根据字符集的字节表示来计算字符数，因为并非每个字符集都是单字节表示（如您所假设的那样）。MySQL 中的 IE utf8 每个字符最多使用 3 个字节，767/ 3≈255 个字符，对于 utf8mb4，最多 4 字节表示，767/4≈191 个字符。

众所周知，MySQL

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

---

就我而言，当我使用 linux 重定向输出/输入字符备份数据库时遇到了这个问题。因此，我如下所述更改语法。 PS：使用 linux 或 mac 终端。

备份（没有 > 重定向）

# mysqldump -u root -p databasename -r bkp.sql

恢复（没有 < 重定向）

# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql

错误“指定的密钥太长；最大密钥长度为 767 字节”简单消失了。

---

我发现此查询可用于检测哪些列的索引违反了最大长度：

SELECT
  c.TABLE_NAME As TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS DataType,
  c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
  s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
  ON s.table_name = c.TABLE_NAME
 AND s.COLUMN_NAME = c.COLUMN_NAME 
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.CHARACTER_MAXIMUM_LENGTH > 191 
  AND c.DATA_TYPE IN ('char', 'varchar', 'text')

---

请检查 sql_mode 是否像

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

如果是，请更改为

sql_mode=NO_ENGINE_SUBSTITUTION

或者

重新启动您的服务器，更改您的 my.cnf 文件（如下所示）

innodb_large_prefix=on

---

由于前缀限制，将发生此错误。 767 字节是 5.7 之前的 MySQL 版本中 InnoDB 表的声明前缀限制。 MyISAM 表的长度为 1,000 字节。在 MySQL 5.7 及更高版本中，此限制已增加到 3072 字节。

在给您错误的服务上运行以下命令应该可以解决您的问题。这必须在 MYSQL CLI 中运行。

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

---

问题

MySQL 中有最大密钥长度限制。

InnoDB — 最大密钥长度为 1,536 字节（对于 8kb 页面大小）和 768 字节（对于 4kb 页面大小）（来源：Dev.MySQL.com）。

MyISAM — 最大密钥长度为 1,000 字节（来源 Dev.MySQL.com）。

这些以字节为单位！因此，一个 UTF-8 字符可能需要超过一个字节才能存储到密钥中。

因此，您只有两个直接的解决方案：

仅索引文本类型的前 n 个字符。

创建全文搜索——文本中的所有内容都可以搜索，类似于 ElasticSearch

索引文本类型的前 N 个字符

如果您要创建表，请使用以下语法为某些字段的前 255 个字符编制索引：KEY sometextkey (SomeText(255))。像这样：

CREATE TABLE `MyTable` (
    `id` int(11) NOT NULL auto_increment,
    `SomeText` TEXT NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `sometextkey` (`SomeText`(255))
);

如果您已经拥有该表，那么您可以向字段添加唯一键：ADD UNIQUE(ConfigValue(20));。像这样：

ALTER TABLE
MyTable
ADD UNIQUE(`ConfigValue`(20));

如果字段名称不是保留的 MySQL keyword，则不需要在字段名称周围使用反引号 (```)。

创建全文搜索

全文搜索将允许您搜索 TEXT 字段的全部值。如果您使用 NATURAL LANGUAGE MODE，它将进行全词匹配，如果您使用其他模式之一，它将进行部分词匹配。在此处查看有关全文选项的更多信息：Dev.MySQL.com

使用文本创建表格，并添加全文索引...

ALTER TABLE
        MyTable
ADD FULLTEXT INDEX
        `SomeTextKey` (`SomeTextField` DESC);

然后像这样搜索你的桌子......

SELECT
        MyTable.id, MyTable.Title,
MATCH
        (MyTable.Text)
AGAINST
        ('foobar' IN NATURAL LANGUAGE MODE) AS score
FROM
        MyTable
HAVING
        score > 0
ORDER BY
        score DESC;

---

我有从 varchar 到 nvarchar 的变化，对我有用。

---