# 1071-指定的密钥太长; 最大密钥长度为1000字节

我知道这个题目的问题以前已经被回答过了，但是请继续读下去。在发帖之前，我已经仔细阅读了所有关于这个错误的其他问题/答案。

我得到以下查询的上述错误:

CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
`menu_id` varchar(32) NOT NULL,
`parent_menu_id` int(32) unsigned DEFAULT NULL,
`menu_name` varchar(255) DEFAULT NULL,
`menu_link` varchar(255) DEFAULT NULL,
`plugin` varchar(255) DEFAULT NULL,
`menu_type` int(1) DEFAULT NULL,
`extend` varchar(255) DEFAULT NULL,
`new_window` int(1) DEFAULT NULL,
`rank` int(100) DEFAULT NULL,
`hide` int(1) DEFAULT NULL,
`template_id` int(32) unsigned DEFAULT NULL,
`alias` varchar(255) DEFAULT NULL,
`layout` varchar(255) DEFAULT NULL,
PRIMARY KEY (`menu_id`),
KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有人知道为什么以及如何解决这个问题吗？ 问题是——同样的查询在我的本地机器上完美地工作，在我以前的主机上也同样工作。Btw.it 来自一个成熟的项目-phpdevshell-所以我猜这些家伙知道他们在做什么，虽然你永远不知道。

任何线索都可以。

我用的是 phpMyAdmin。

234324 次浏览
小开

此错误意味着索引 index的长度超过1000字节。MySQL 和存储引擎可能有这个限制。我在 MySQL 5.5上也遇到过类似的错误——“指定的密钥太长; 最大密钥长度是3072字节”，当运行这个脚本时:

CREATE TABLE IF NOT EXISTS test_table1 (
column1 varchar(500) NOT NULL,
column2 varchar(500) NOT NULL,
column3 varchar(500) NOT NULL,
column4 varchar(500) NOT NULL,
column5 varchar(500) NOT NULL,
column6 varchar(500) NOT NULL,
KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UTF8是多字节的，按照这种方式计算密钥长度-500 * 3 * 6 = 9000字节。

但请注意，下一个查询工作！

CREATE TABLE IF NOT EXISTS test_table1 (
column1 varchar(500) NOT NULL,
column2 varchar(500) NOT NULL,
column3 varchar(500) NOT NULL,
column4 varchar(500) NOT NULL,
column5 varchar(500) NOT NULL,
column6 varchar(500) NOT NULL,
KEY `index` (column1, column2, column3, column4, column5, column6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

... 因为我使用了 CHARSET = latin1，在本例中，密钥长度为500 * 6 = 3000字节。

小开
最佳答案

正如@Devart 所说，索引的总长度太长了。

简短的回答是，您不应该索引这么长的 VARCHAR 列，因为索引将是非常庞大和低效的。

最佳实践是使用 前缀索引，这样您只需索引数据的左侧子字符串。无论如何，您的大多数数据将大大短于255个字符。

可以在定义索引时为每列声明前缀长度。例如:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

但是给定列的最佳前缀长度是多少呢:

SELECT
ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

它告诉您在 menu_link列中不超过给定字符串长度的行的比例。您可能会看到如下输出:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

这表示80% 的字符串小于20个字符，所有字符串小于50个字符。因此，不需要对超过50个前缀长度的字符进行索引，当然也不需要对255个字符的完整长度进行索引。

PS: INT(1)和 INT(32)数据类型表明了对 MySQL 的另一种误解。数值参数对存储或列允许的值范围没有影响。INT总是4字节，并且它总是允许从 -2147483648到2147483647的值。数值参数是关于在显示期间填充值的，除非使用 ZEROFILL选项，否则不会产生任何效果。

小开

在64位版本的 MySQL 中，这个索引大小限制似乎更大。

我试图转储我们的开发数据库并将其加载到本地 VMWare virt 中，但是遇到了这个限制。最后我意识到远程开发服务器是64位的，我创建了一个32位的 virt。我刚刚创建了一个64位的 virt，我可以在本地加载数据库。

小开

在创建或更改表之前运行此查询。

SET @@global.innodb_large_prefix = 1;

这将把密钥最大长度设置为3072字节

小开

我已经绕过了这个错误，通过改变其结构，将原始数据库中的“ length”的值改为“1000”左右，然后导出到服务器。:)

小开

我遇到了这个问题，通过以下方法解决了:

因为

MySQL 有一个已知的 bug，与 MyISAM (UTF8字符)有关 设置和索引，您可以在这里检查。

决心

  • 确保 MySQL 配置了 InnoDB 存储引擎。

  • 更改默认使用的存储引擎，以便始终适当地创建新表:

    set GLOBAL storage_engine='InnoDb';

  • 对于 MySQL 5.6及更高版本，请使用以下代码:

    SET GLOBAL default_storage_engine = 'InnoDB';

  • 最后，请确保遵循 迁移到 MySQL中提供的说明。

参考文献

小开

我面临着同样的问题; 使用下面的查询来解决它。

在创建数据库时，可以使用 utf-8编码。

例如 create database my_db character set utf8 collate utf8mb4;

编辑:

(考虑来自评论的建议)

将 utf8_bin改为 utf8mb4

小开

我有这个错误，我改变了我的表格列的长度，为索引的外键列更小，所以我改变它像这样:

VARCHAR(1024)

致:

VARCHAR(512)

然后再次运行查询。

小开

我玩了那么多把戏，但没有一个对我有用。 然后我找到了最好最简单的一个:

如果您正在使用 mysql-workbench，同时修改 index select (Engine:)-injDb。

小开

如果您使用的是 Laravel 7或 Laravel 8, Goto 到 config/database. php

'engine' => 'innoDb',

应该工作，特别是使用 Wamp 或 Xampp。

小开

我就这样从 MyISAM变成了 InnoDB

在改变之前

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

在换衣服之后

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

小开

如果自己没有创建 sql，可以检查并设置列的长度为通常的长度。


提交答案