Mysql ERROR 1071 (42000): 指定的key太长；最大密钥长度为 3072 字节 [英] Mysql ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

问题描述

当我尝试创建表格时:

CREATE TABLE `oro_workflow_restriction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `workflow_step_id` int(11) DEFAULT NULL,
  `workflow_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `attribute` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `field` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_class` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mode` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mode_values` longtext COLLATE utf8mb4_unicode_ci COMMENT '(DC2Type:json_array)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `oro_workflow_restriction_idx` (`workflow_name`,`workflow_step_id`,`field`,`entity_class`,`mode`),
  KEY `IDX_3D2C17E271FE882C` (`workflow_step_id`),
  KEY `IDX_3D2C17E21BBC6E3D` (`workflow_name`),
  CONSTRAINT `FK_3D2C17E21BBC6E3D` FOREIGN KEY (`workflow_name`) REFERENCES `oro_workflow_definition` (`name`) ON DELETE CASCADE,
  CONSTRAINT `FK_3D2C17E271FE882C` FOREIGN KEY (`workflow_step_id`) REFERENCES `oro_workflow_step` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我收到关于指定密钥太长的错误；最大密钥长度为 3072 字节.

I got an error about Specified key was too long; max key length is 3072 bytes.

我不明白为什么会出现错误.最长的密钥长度是唯一密钥:4 + 4 * 255*3 = 3071 字节 <3072 字节.(如果我的计算是正确的)

显然 utf8mb4 是每个字符 4 个字节，我最多只能有 varchar(191).就我而言，它是 4 + 4 * 255*4 = 4084 字节 > 3072 字节.

Clearly utf8mb4 is 4 bytes per character, I can have only varchar(191) at maximum. In my case it is 4 + 4 * 255*4 = 4084 bytes > 3072 bytes.

有没有一种不需要改变数据类型就能让它工作的方法?

Is there a way that doesn't require to change the data type to get it working?

推荐答案

两种解决方案

通过删除唯一约束

如果您可以删除唯一约束.为搜索目的启用全文索引，但您将无法强制执行唯一约束.

If you can drop the unique constraint. Enable full-text indexing for search purposes, but you won't be able to enforce unique constraints.

通过规范化架构

将 varchar(255) 列从该表中取出并存储引用表中的 ids.这些将是整数.对于整数，复合键也能正常工作.

Put the varchar(255) columns out of this table and store ids from referred tables. These will be integers. And with integers, composite keys will work just fine.

我认为 MySQL 在将其限制为 3072 字节方面做得很好.否则，人们会创建各种无用的索引，从而导致性能瓶颈.

I think MySQL has done a good job limiting this to 3072 bytes. Otherwise, people would create all kinds of useless indexes that would lead to a performance bottleneck.

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