mysql - 错误 : Specified key was too long; max key length is 1000 bytes

Question

错误:

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

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id` , `module_id` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

我知道错误发生是因为 255x2x3(每个字符 3 个字节)

这不会发生在所有安装上。我可以更改什么设置？

Answer 1

NO_ENGINE_SUBSTITUTION 被禁用且 INNODB 未激活，一个糟糕的组合

• 在 MySql 5.5 之前，默认的 sqlmode 是一个空字符串，这意味着默认情况下没有设置 sqlmode NO_ENGINE_SUBSTITUTION

根据 MySql 文档(参见 https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution)，这是 sqlmode NO_ENGINE_SUBSTITUTION 的含义:

Control automatic substitution of the default storage engine when astatement such as CREATE TABLE or ALTER TABLE specifies a storageengine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailableengines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the defaultengine is used and a warning occurs if the desired engine isunavailable. For ALTER TABLE, a warning occurs and the table is notaltered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table isnot created or altered if the desired engine is unavailable.

• 因此:如果 NO_ENGINE_SUBSTITUTION 被禁用并且 INNODB 被关闭，如果您在 CREATE TABLE 语句中指定 INNODB，MySql 也会切换到 MYISAM。
• 如果您正在创建的表对于 MYISAM 是正确的，您只会收到一条警告并创建该表。那不是你的情况，你的创建语句包含一个索引，超出了 MYISAM 的 1000 字节限制，然后创建失败，错误 1071 报告 MYISAM 的错误。那是因为工作引擎是 MYISAM，而不是 INNODB。

证明

MySql 版本 5.1.56 社区

案例一:

Options in my.cnf  
sql-mode=""  
default-storage-engine=MYISAM  
skip-innodb uncommented (without#) 

Return on execution of your create statement:
Error Code: 1071. Specified key was too long; max key length is 1000 bytes

Explanation: INNODB is not active, the engine is automatically switched to MYISAM  
that returns this error as they key is longer than MYISAM 1000 bytes limit.  
The key length is: 
2 fields x 255 char x 3 bytes utf8 encoding + 2 x 1 length byte = 1532 bytes   

案例二:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb uncommented (without#)

Return on execution of your create statement:
Error Code: 1286. Unknown table engine 'INNODB'

Explanation: INNODB is not active but the engine substitution is not permitted
by sql mode therefore the DB returns an error about the attempt of using a disabled engine. 

案例三:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb commented (with#)

Return on execution of your create statement:
Table creation OK!

Explanation: INNODB is active (skip-innodb commented) and it is used also if      
the default engine is MYISAM.

要重现测试，请在每次更改 my.cnf 后重新启动 MySql。

由于MySql 5.6版本sqlmode默认不再为空，包含NO_ENGINE_SUBSTITUTION，而且INNODB是默认引擎，所以很难遇到错误。

其他测试

没有其他方法可以重现错误:

Error Code: 1071. Specified key was too long; max key length is 1000 bytes 

在尝试创建 INNODB 表时已找到。

在 INNODB 中你有两种 ERROR 1071:

Error Code: 1071. Specified key was too long; max key length is 767 bytes

这与 innodb_large_prefix ON 或 OFF 无关，仅与用作索引的单个 VARCHAR 列的大小有关。

Mysql 将 varchar utf8 存储为 3 个字节加上 1 个字节，长度最多为 255 个字符，之后为 2 个(请参阅:http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html)，因此如果您尝试使用 VARCHAR(256) utf8 设置键，您将得到:

256 x 3 + 2 = 770 bytes

你会得到之前的错误，因为对于 InnoDB 表，单个列的最大键长度是 767 字节。 VARCHAR(255) 没问题，因为:

255 x 3 + 1 = 766 bytes

我在 Mysql 的四个安装版本 5.1.56、5.5.33、5.6 和 5.7 上对其进行了测试，并得到证实。使用 VARCHAR(255) 的查询没有问题，使用 VARCHAR(256) 的问题:

Error Code: 1071. Specified key was too long; max key length is 767 bytes

如您所见，消息不同，因为它是 INNODB 消息而不是 MYISAM 消息!

INNODB 表的另一种错误 1071 是:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

这与具有多列的键有关。要启用这些键，您需要将 innodb_large_prefix 设置为 on。不管怎样，如果你尝试运行这样的东西:

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id` , `module_id`, `action_name_key`, `action_name_key1`, `action_name_key2` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

使用 5 VARCHAR(255) utf8 列的键，即 3830 字节，您将遇到:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

奇异假设

在寻找原因的过程中，我制定并测试了不同且非常奇怪的假设:

行格式

已测试 REDUNDANT、COMPACT、COMPRESS、DYNAMIC:对使用您的语句创建表没有影响。

文件格式

经过测试的 Antelope 和 Barracuda:您的语句对建表没有影响。

MySql 构建

测试了 32 位和 64 位 MySql:对您的语句创建表没有影响。

其他类似故障

在这里你可以找到相同情况下的相同错误:

https://www.drupal.org/node/2466287

我在 PROOF 中列出的 3 种测试情况下测试了该语句，它再现了与您完全相同的行为，因此我可以说问题是相同的。在那种情况下，他们切换到其他数据库，但问题是设置的混合，而不是数据库版本。

引用资料

这里给出了一篇非常好的使用 INNODB 建立索引的文章:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

警告:在创建索引超过 1000 的 INNODB 表后通过取消注释 my.cnf 中的 skip-innodb 来禁用 INNODB 将不允许启动 MySql 服务

问候