Mysql索引長度限制

在設(shè)置 utf8mb4 字段的字符長度時(shí)，可能會拋出一個(gè)異常：

"Specified key was too long; max key length is 1000 bytes"

也就是在給表的索引字段添加字符長度時(shí)，超過了最大索引長度 1000 字節(jié)。

一、原因分析

關(guān)于索引長度的限制，最主要的因素就是存儲引擎和字符集。

字符集的影響在于，不同的字符集，單個(gè)字符包含的最大字節(jié)數(shù)有所不同。

比如 utf8 字符集，一個(gè)字符最多包含 3 個(gè)字節(jié)。而 utf8mb4 一個(gè)字符最多包含 4 個(gè)字節(jié)。

對于 InnoDB 引擎來說：

1) Mysql 版本 <=5.7.6

• 如果是單字段索引，則字段長度不應(yīng)超過 767 字節(jié)。
• 如果是聯(lián)合索引，則每個(gè)字段長度都不應(yīng)超過 767 字節(jié)，且所有字段長度合計(jì)不應(yīng)超過 3072 字節(jié)。

這種情況下，常見字符集和引擎組合后的字符長度限制如下：

2) Mysql 版本 >=5.7.7

在該版本情況下，由于 InnoDB 引擎的 innodb_large_prefix 等選項(xiàng)默認(rèn)值改變，單字段索引長度限制增大。

• 如果是單字段索引，則字段長度不應(yīng)超過 3072 字節(jié)。
• 如果是聯(lián)合索引，則每個(gè)字段長度都不應(yīng)超過 3072 字節(jié)，且所有字段長度合計(jì)不應(yīng)超過 3072 字節(jié)。

這種情況下，常見字符集和引擎組合后的字符長度限制如下：

對于 MyISAN 引擎來說：

如果 Mysql 版本 <=5.5.4 則其默認(rèn)存儲引擎為 MyISAN。

• 如果是單字段索引，則字段長度不應(yīng)超過 1000 字節(jié)。
• 如果是聯(lián)合索引，則每個(gè)字段長度都不應(yīng)超過 1000 字節(jié)，且所有字段長度合計(jì)不應(yīng)超過 1000 字節(jié)。

這種情況下，常見字符集和引擎組合后的字符長度限制如下：

二、解決方法

(1)修改存儲引擎

(2)修改字符集

(3)修改索引字段的字符長度

在 Mysql 版本 <=5.7.6 且字符集為 utf8mb4 的情況下，設(shè)置需要添加索引的字段長度為 191。

(4)在 Mysql<=5.7.6 的情況，修改 InnoDB 選項(xiàng)，將單字段索引長度限制由 767 字節(jié)（bytes）提高到 3072 字節(jié)。

先查看當(dāng)前默認(rèn)選項(xiàng)值：

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_file_format';

確認(rèn)選項(xiàng)值是否是：

innodb_large_prefix=ON
innodb_file_per_table=ON
innodb_file_format=Barracuda

如果不是，進(jìn)行相應(yīng)的修改：

SET GLOBAL innodb_large_prefix = 1;
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_file_format = barracuda;

上面兩項(xiàng)的值設(shè)置為 1 而不是 ON，是因?yàn)樵?Mysql5.6 之前，布爾值的變量還無法識別 ON 或者 OFF，只能是數(shù)字 1 或者 0。

并且，在創(chuàng)建新表時(shí)還要指定 ROW_FORMAT=DYNAMIC 或 ROW_FORMAT=COMPRESSED。

如：

CREATE TABLE `test` (
`a` varchar(767),
`b` varchar(1),
`c` varchar(200),
`d` varchar(168),
`e` varchar(5),
KEY `a_index` (`a`,`b`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

或修改當(dāng)前表：

ALTER TABLE [...], ROW_FORMAT=DYNAMIC;

(5)在創(chuàng)建索引時(shí)限制字段上的前綴索引長度：

CREATE TABLE `test` (
`a` varchar(767),
`b` varchar(1),
`c` varchar(200),
`d` varchar(168),
`e` varchar(5),
KEY `a_index` (`a`(191),`b`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。