blocks|key|688898|text|此错误表示索引index的长度大于1000个字节。MySQL和存储引擎可能有此限制。我在MySQL+5.5上得到了类似的错误-‘指定的密钥太长；最大密钥长度是3072字节’，当运行这个脚本时：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|688899|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;|code-block|syntax|javascript|688900|UTF8为多字节，密钥长度计算方式为-+500+*3*6=+9000字节。|688901|但请注意，下一个查询可以正常工作！|688902|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;|688903|...because我使用了CHARSET=latin1，在本例中，密钥长度为500+*6=+3000字节。|688904|entityMap^0|7|5|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|10|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|11|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|T|$]]

This error means that length of index <code>index</code> is 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:

<pre><code>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;
</code></pre>

UTF8 is multi-bytes, and key length is calculated in this way - 500 * 3 * 6 = 9000 bytes.

But note, next query works!

<pre><code>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;
</code></pre>

...because I used CHARSET=latin1, in this case key length is 500 * 6 = 3000 bytes.

blocks|key|472771|text|在64位版本的MySQL上，此索引大小限制似乎更大。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|472772|在尝试转储我们的开发数据库并将其加载到本地VMWare+virt上时，我遇到了这个限制。最后，我意识到远程开发服务器是64位的，并且我已经创建了一个32位的virt。我刚刚创建了一个64位的virt，并且我能够在本地加载数据库。|472773|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

This index size limit seems to be larger on 64 bit builds of 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.

blocks|key|689009|text|在创建或更改表之前运行此查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689010|SET+@@global.innodb_large_prefix+=+1;|offset|length|style|CODE|689011|这会将最大密钥长度设置为3072字节|689012|entityMap^0|0|0|11|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

run this query before creating or altering table. 

<code>SET @@global.innodb_large_prefix = 1;</code>

this will set max key length to 3072 bytes

blocks|key|689093|text|我刚刚通过更改结构将原始数据库中的"length“值更改为总数"1000”，然后将其导出到服务器，从而绕过了这个错误。:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689094|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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. :)

blocks|key|689170|text|我也面临着同样的问题，使用下面的查询来解决它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689171|在创建DB时，您可以使用utf-8编码|689172|例如：create+database+my_db+character+set+utf8+collate+utf8mb4;|offset|length|style|CODE|689173|编辑：(考虑来自评论的建议)将utf8_bin更改为utf8mb4|689174|entityMap^0|0|0|3|1L|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@$F|Q|G|R|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

I was facing same issue, used below query to resolve it.

While creating DB you can use utf-8 encoding

eg. <code>create database my_db character set utf8 collate utf8mb4;</code>

EDIT: (Considering suggestions from comments)
Changed utf8_bin to utf8mb4

blocks|key|689222|text|我遇到了这个错误，我将索引的外键列的表列长度改小了，所以我这样做了：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689223|VARCHAR(1024)|code-block|syntax|javascript|689224|至：|689225|VARCHAR(512)|689226|然后再次运行查询。|689227|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I had this error and I changed my tables column length smaller for the indexed foreign key columns so I changed it like this:
<pre><code>VARCHAR(1024)
</code></pre>
To:
<pre><code>VARCHAR(512)
</code></pre>
And run the query again.

blocks|key|689300|text|我已经经历了这么多，对我来说没有一个工作，然后我found...best和一个简单的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689301|+++++++if+you+are+working+with+mysql-workbench,
+++++++while+altering+index+select+(Engine:)-innoDb.
+++++++apply+and+enjoy...|code-block|syntax|javascript|689302|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I have gone through so many tricks...but none work for me
and then i found...best and simple one
<pre><code> if you are working with mysql-workbench,
 while altering index select (Engine:)-innoDb.
 apply and enjoy...
</code></pre>

blocks|key|689358|text|如果您使用的是Laravel+7或Laravel+8，请转到config/database.php|type|unstyled|depth|inlineStyleRanges|entityRanges|data|689359|'engine'+=>+'innoDb',|code-block|syntax|javascript|689360|这应该可以工作，特别是使用Wamp或Xampp。|689361|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

if you are using Laravel 7 or Laravel 8,
goto to config/database.php
<pre><code>'engine' =&gt; 'innoDb',
</code></pre>
that should work especially using Wamp or Xampp.

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:

<pre><code>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;
</code></pre>

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.

Any clue appreciated.

I'm using phpMyAdmin.

#1071 - Specified key was too long; max key length is 1000 bytes

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我知道这个标题的问题以前已经回答过了，但请继续阅读。在发帖之前，我已经彻底阅读了关于这个错误的所有其他问题/答案。我收到以下查询的上述错误：CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (  `menu_id` varchar(32) NOT NULL,  `pare...

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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