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

dwbf0jvd 于 2021-06-20 发布在 Mysql

关注(0)|答案(7)|浏览(317)

我知道这个题目的问题以前已经回答过了，但请继续读下去。在发布之前，我已经彻底阅读了关于这个错误的所有其他问题/答案。
我在以下查询中得到上述错误：

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。

mysql phpmyadmin mysqldump

来源：https://stackoverflow.com/questions/52645229/error-1071-specified-key-was-too-long-max-key-length-is-3072-bytes-sql-stateme

7条答案

按热度按时间

hs1ihplo1#

正如@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个字符的完整长度。
附言：是的 INT(1) 以及 INT(32) 数据类型表明了对mysql的另一个误解。数值参数与存储或列允许的值范围无关。 INT 总是4个字节，它总是允许值从-2147483648到2147483647。数值参数是关于在显示期间填充值的，除非使用 ZEROFILL 选项。

赞(0）回复(0）举报 2021-06-21

vx6bjr1n2#

我遇到了这个问题，通过以下方法解决了这个问题：
原因
mysql中有一个已知的bug与myisam、utf8字符集和索引有关，您可以在这里查看。
决心
确保mysql配置了innodb存储引擎。
更改默认情况下使用的存储引擎，以便始终适当地创建新表： set GLOBAL storage_engine='InnoDb'; 对于MySQL5.6及更高版本，请使用以下命令： SET GLOBAL default_storage_engine = 'InnoDB'; 最后，确保您遵循了迁移到mysql中提供的说明。
参考

赞(0）回复(0）举报 2021-06-21

zynd9foi3#

这个错误意味着索引的长度 index 大于1000字节。mysql和存储引擎可能有此限制。我在MySQL5.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是多字节的，密钥长度是这样计算的-50036=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字节。

赞(0）回复(0）举报 2021-06-21

hc2pp10m4#

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

赞(0）回复(0）举报 2021-06-20

jucafojl5#

我刚刚绕过了这个错误，将原始数据库中的“length”值更改为大约“1000”的总数，方法是更改其结构，然后将其导出到服务器。：）

赞(0）回复(0）举报 2021-06-20

du7egjpx6#

在mysql的64位版本上，这个索引大小限制似乎更大。
我尝试转储我们的dev数据库并将其加载到本地vmwarevirt上时遇到了这个限制。最后我意识到远程开发服务器是64位的，我创建了一个32位的virt。我刚刚创建了一个64位virt，可以在本地加载数据库。

赞(0）回复(0）举报 2021-06-20

cl25kdpy7#