#1071-指定的密钥太长；最大密钥长度为1000个字节 [英] #1071 - Specified key was too long; max key length is 1000 bytes

问题描述

我知道有关该标题的问题已经回答过，但是请继续阅读.发布前，我已经彻底阅读了有关此错误的所有其他问题/答案.

I know questions with this title have been answered before, but please do read on. I've read thoroughly all the other questions/answers on this error before posting.

对于以下查询，我收到上述错误:

I am getting the above error for the following query:

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-所以我猜这些家伙知道他们在做什么，尽管你永远都不知道.

Does anyone have idea why and how to fix it? The catch is - this same query works perfectly on my local machine, and worked as well on my previous host. Btw.it's from a mature project - phpdevshell - so I'd guess these guys know what they are doing, although you never know.

任何线索都很感激.

我正在使用phpMyAdmin.

I'm using phpMyAdmin.

推荐答案

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

As @Devart says, the total length of your index is too long.

简短的答案是，无论如何您都不应索引如此长的VARCHAR列，因为索引将非常庞大且效率低下.

The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

最佳做法是使用前缀索引，因此您仅索引数据的左子串.无论如何，您的大多数数据都会少于255个字符.

The best practice is to use prefix indexes so you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

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

You can declare a prefix length per column as you define the index. For example:

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

但是给定列的最佳前缀长度是多少?这是一种找出答案的方法:

But what's the best prefix length for a given column? Here's a method to find out:

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列中不超过给定字符串长度的行的比例.您可能会看到这样的输出:

It tells you the proportion of rows that have no more than a given string length in the menu_link column. You might see output like this:

+---------------+---------------+---------------+----------------+
| 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个字符的整个长度.

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there's no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

PS:INT(1)和INT(32)数据类型表明对MySQL的另一种误解.数字参数与存储或该列允许的值范围无关. INT始终为4个字节，并且始终允许从-2147483648到2147483647之间的值.数字参数与显示期间的填充值有关，除非您使用ZEROFILL选项，否则该值无效.

PS: The INT(1) and INT(32) data types indicates another misunderstanding about MySQL. The numeric argument has no effect related to storage or the range of values allowed for the column. INT is always 4 bytes, and it always allows values from -2147483648 to 2147483647. The numeric argument is about padding values during display, which has no effect unless you use the ZEROFILL option.

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