MySQL #1071 - 指定的密钥太长；最大密钥长度为 1000 字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8746207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
#1071 - Specified key was too long; max key length is 1000 bytes
提问by CodeVirtuoso
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;
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.
有谁知道为什么以及如何解决它？问题是 - 这个相同的查询在我的本地机器上完美运行，并且在我以前的主机上也运行良好。顺便说一句，它来自一个成熟的项目 - phpdevshell - 所以我猜这些人知道他们在做什么，虽然你永远不知道。
Any clue appreciated.
任何线索表示赞赏。
I'm using phpMyAdmin.
我正在使用 phpMyAdmin。
回答by Bill Karwin
As @Devart says, the total length of your index is too long.
正如@Devart 所说，索引的总长度太长。
The short answer is that you shouldn't be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.
简短的回答是无论如何你都不应该索引这么长的 VARCHAR 列，因为索引会非常庞大且效率低下。
The best practice is to use prefix indexesso you're only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.
最佳做法是使用前缀索引，这样您就可以只索引数据的左子串。无论如何，您的大部分数据都会比 255 个字符短很多。
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`;
It tells you the proportion of rows that have no more than a given string length in the menu_linkcolumn. You might see output like this:
它告诉您列中不超过给定字符串长度的行的比例menu_link。你可能会看到这样的输出：
+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
| 21.78 | 80.20 | 100.00 | 100.00 |
+---------------+---------------+---------------+----------------+
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.
这告诉您 80% 的字符串少于 20 个字符，并且所有字符串都少于 50 个字符。所以不需要索引超过 50 的前缀长度，当然也不需要索引 255 个字符的全长。
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. INTis 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 ZEROFILLoption.
PS：INT(1)和INT(32)数据类型表明了对 MySQL 的另一个误解。数字参数对存储或列允许的值范围没有影响。 INT总是 4 个字节，它总是允许从 -2147483648 到 2147483647 的值。数字参数是关于显示期间的填充值，除非您使用该ZEROFILL选项，否则没有任何影响。
回答by Devart
This error means that length of index indexis more then 1000 bytes. MySQL and storage engines may have this restriction. I have got similar error on MySQL 5.5 - 'Specified key was too long; max key length is 3072 bytes' when ran this script:
此错误意味着索引长度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 is multi-bytes, and key length is calculated in this way - 500 * 3 * 6 = 9000 bytes.
UTF8是多字节的，key长度是这样计算的——500*3*6=9000字节。
But note, next query works!
但请注意，下一个查询有效！
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;
...because I used CHARSET=latin1, in this case key length is 500 * 6 = 3000 bytes.
...因为我使用了 CHARSET=latin1，在这种情况下，密钥长度为 500 * 6 = 3000 字节。
回答by Vishrant
I had this issue, and solved by following:
我遇到了这个问题，并通过以下方式解决了：
Cause
There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes that you can check here.
Resolution
Make sure MySQL is configured with the InnoDB storage engine.
Change the storage engine used by default so that new tables will always be created appropriately:
set GLOBAL storage_engine='InnoDb';For MySQL 5.6 and later, use the following:
SET GLOBAL default_storage_engine = 'InnoDB';And finally make sure that you're following the instructions provided in Migrating to MySQL.
原因
MySQL 有一个与 MyISAM、UTF8 字符集和索引相关的已知错误，您可以在此处查看。
解析度
确保 MySQL 配置了 InnoDB 存储引擎。
更改默认使用的存储引擎，以便始终正确创建新表：
set GLOBAL storage_engine='InnoDb';对于 MySQL 5.6 及更高版本，请使用以下内容：
SET GLOBAL default_storage_engine = 'InnoDB';最后确保您遵循迁移到 MySQL 中提供的说明。
回答by Raza Ahmed
run this query before creating or altering table.
在创建或更改表之前运行此查询。
SET @@global.innodb_large_prefix = 1;
SET @@global.innodb_large_prefix = 1;
this will set max key length to 3072 bytes
这会将最大密钥长度设置为 3072 字节
回答by Ryan Olson
This index size limit seems to be larger on 64 bit builds of MySQL.
这个索引大小限制在 64 位 MySQL 版本上似乎更大。
I was hitting this limitation trying to dump our dev database and load it on a local VMWare virt. Finally I realized that the remote dev server was 64 bit and I had created a 32 bit virt. I just created a 64 bit virt and I was able to load the database locally.
我在尝试转储我们的开发数据库并将其加载到本地 VMWare virt 时遇到了这个限制。最后我意识到远程开发服务器是 64 位的，我创建了一个 32 位的虚拟机。我刚刚创建了一个 64 位 virt，并且能够在本地加载数据库。
回答by Vishwadeep Kapoor
I have just made bypass this error by just changing the values of the "length" in the original database to the total of around "1000" by changing its structure, and then exporting the same, to the server. :)
我刚刚通过更改其结构将原始数据库中“长度”的值更改为“1000”左右，然后将其导出到服务器，从而绕过了此错误。:)
回答by Sudhir Dhumal
I was facing same issue, used below query to resolve it.
我遇到了同样的问题，使用下面的查询来解决它。
While creating DB you can use utf-8 encoding
创建数据库时，您可以使用 utf-8 编码
eg. create database my_db character set utf8 collate utf8mb4;
例如。 create database my_db character set utf8 collate utf8mb4;
EDIT: (Considering suggestions from comments) Changed utf8_bin to utf8mb4
编辑：（考虑来自评论的建议）将 utf8_bin 更改为 utf8mb4
