Mysql索引長度限制

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

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

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

一、原因分析

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

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

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

對于 InnoDB 引擎來說：

1) Mysql 版本 <=5.7.6

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

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

2) Mysql 版本 >=5.7.7

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

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

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

對于 MyISAN 引擎來說：

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

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

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

二、解決方法

(1)修改存儲引擎

(2)修改字符集

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

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

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

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

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

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

innodb_large_prefix=ON
innodb_file_per_table=ON
innodb_file_format=Barracuda

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

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

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

并且，在創(chuàng)建新表時還要指定 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)建索引時限制字段上的前綴索引長度：

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é)

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

最新評論