#1071 - 指定的键太长；最大键长度为1000字节。

Question

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

以下查询导致了上述错误：

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;

有人知道为什么会出现这种情况以及如何解决吗？ 问题是 - 在我的本地机器上，相同的查询可以完美运行，在之前的主机上也能正常工作。顺便说一下，它来自一个成熟的项目 - phpdevshell - 所以我想这些人知道他们在做什么，尽管你永远不知道。

如果有线索，请提供。

我正在使用phpMyAdmin。

- CodeVirtuoso

---

如果涉及到Java Hibernate，请参考答案https://stackoverflow.com/a/76016256/9370144 - Nadun Kulatunge

请点击此处查看解决方案：https://youtu.be/EWZfHyNs08c - undefined

Answer 1

正如@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：关于MySQL，INT（1）和INT（32）数据类型表明了一种误解。数字参数对存储或允许列使用的值范围没有影响。 INT始终是4个字节，并且始终允许-2147483648至2147483647的值。数字参数用于在显示时填充值，除非使用ZEROFILL选项，否则不会产生任何影响。

- Bill Karwin

---

非常感谢您详细的解释。除了解决问题外，我还学到了一些有价值的东西。 - CodeVirtuoso

你实用查询中有一个微妙的错误，它用于测量字符串的真实长度：你假设这些字符串全部存在，也就是说，它们都在那里。如果其中一些是空的，它将使你的计算出现偏差，并且会低估短字符串。你需要使用 count([field_name]) 而不是 count(*)。 - D Mac

这真是个救命稻草，当你找到一个MySQL数据库，其中所有的varchar字段都设置为长度255（即使在现实中最多只有10个字符的列），并且你尝试添加组合索引时。谢谢！ - Udo Klimaschewski

@Jon 我不知道你想让我说什么。MySQL 无法在如此长的字符串上创建索引，无论是唯一的还是非唯一的。你可以添加另一个列来存储字符串的哈希值，并在该列上放置唯一索引。 - Bill Karwin

@wbzy00，正确，COUNT()将0的值与1的值计数相同。但是SUM()仅计算值为1的值，因为添加0不会改变总和。因此，布尔表达式的SUM()等于布尔表达式为真的行的COUNT()。这在MySQL中有效，因为MySQL将false视为整数值0，将true视为整数值1。 - Bill Karwin

Answer 2

这个错误意味着索引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

---

谢谢回复，这个方法可行，但是需要放弃使用 utf8 字符集。有没有办法克服这个限制（我拥有完整的服务器访问权限），这个限制是否有其必要性？ - CodeVirtuoso

这些建议很糟糕，请学习一下字符集是什么以及这样做会在未来导致重大问题。混合字符集数据库不仅在使用联接或子查询时会出现问题，还会将您的数据放入非规范化格式中，并且以后几乎不可能进行修复。 - Geoffrey

我将字符集移动到latin1，使其正常工作，然后再次更改为utf-8。 - Ran

Answer 3

我曾遇到过这个问题，并通过以下方式解决：

原因

MySQL存在一个已知的与MyISAM、UTF8字符集和索引相关的bug，你可以在这里检查。

解决方法

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

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

  set GLOBAL storage_engine='InnoDb';

• 对于MySQL 5.6及以上版本，请使用以下命令：

  SET GLOBAL default_storage_engine = 'InnoDB';

• 最后，确保您按照迁移到MySQL中提供的说明操作。

参考

- Vishrant

---

在大多数情况下，我们会忘记将存储引擎配置为“InnoDB”。 以下答案是最简单的，可能会解决大多数用户的问题。谢谢。 - Frederiko Cesar

Answer 4

在创建或修改表之前运行此查询。 SET @@global.innodb_large_prefix = 1; 这将将最大键长度设置为3072字节。

- Raza Ahmed

---

MySQL 8.0移除了这个变量，因为在该版本中它已经始终处于启用状态。作为一个变量，它不再需要了。 - Bill Karwin

Answer 5

我曾经也遇到过同样的问题，使用以下查询语句解决了它。

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

例如：create database my_db character set utf8 collate utf8mb4;

编辑：

(考虑到评论中的建议)

将 utf8_bin 更改为 utf8mb4

- Sudhir Dhumal

---

这指引我朝着正确的方向前进。对我来说，我需要将排序规则更改为：utf8_general_ci。 - Ian Newland

这不是正确的方向，请不要这样做！MySQL的“utf8”并非真正的“utf8”，而是一个有缺陷的专有格式，它本不应该存在。 “utf8mb4” 是真正的“utf8”，并且建议将其作为适当的“utf8”支持的默认选项。 - Geoffrey

utf8mb4 is the correct encoding to use on mysql and not utf8 - alok

Answer 6

如果您正在使用 Laravel 7 或 Laravel 8， 请前往 config/database.php。

'engine' => 'innoDb',

使用Wamp或Xampp应该可以解决这个问题。

- Razor Mureithi

---

在我的情况下，这个方法起了作用。我有一个使用InnoDB引擎的utf8mb4数据库，并使用utf8mb4_general_ci排序规则，而我的配置为空。将引擎更改为innodb后，我能够运行迁移。 - Marcos Rocha

这对我有用，我正在运行 Laravel 8。 - undefined

Answer 7

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

我在尝试将我们的开发数据库转储并加载到本地 VMWare 虚拟机时遇到了这个限制。最后我意识到远程开发服务器是 64 位的，而我创建的虚拟机是 32 位的。我创建了一个 64 位的虚拟机，然后我成功地在本地加载了数据库。

- Ryan Olson

Answer 8

嗯，我刚刚像这样从MyISAM更改为InnoDB

更改前

ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

更改后

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

- Codeparl

Answer 9

我刚刚通过改变原始数据库中“长度”值的结构并将其导出到服务器来解决了此错误，将其总数更改为约“1000”。 :)

- Vishwadeep Kapoor

Answer 10

在运行后端应用程序时遇到相同的异常，我们可以设置引擎 "InnoDb" set GLOBAL storage_engine='InnoDb';

如果上述配置对您不起作用，则可以更改密钥长度，例如，默认情况下选择了 255 个字符，您可以将其更改为 100 以下 示例 - create table role ( role_name varchar(99) not null, role_description varchar(255), primary key (role_name)); 这对我有用

- Lalit Sati