问题描述
我知道以前已经回答过与此标题有关的问题，但请继续阅读.在发帖之前，我已经仔细阅读了有关此错误的所有其他问题/答案.
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 字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！