【MySql】索引长度限制

在使用 Laravel 数据库迁移的时候，可能会抛出一个异常："SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes" (SQL: alter table `users` add unique `users_email_unique`(`email`))。也就是在给 users 表的 email 字段添加唯一索引时，email 字段索引的长度超过了最大索引长度 767 字节。

原因

关于索引长度的限制，最主要的因素就是存储引擎和字符集。

字符集的影响在于，不同的字符集，单个字符包含的最大字节数有所不同。比如 utf8 字符集，一个字符最多包含 3 个字节。而 utf8mb4 一个字符最多包含 4 个字节。

对于 InnoDB 引擎来说：

1) Mysql 版本 <=5.7.6

如果是单字段索引，则字段长度不应超过 767 字节。
如果是联合索引，则每个字段长度都不应超过 767 字节，且所有字段长度合计不应超过 3072 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

InnoDB	最大字节数	单字段索引最大字符数	联合索引合计最大字符数
lantin1	1	767	3072
gbk	2	383	1536
utf8	3	255	1024
utf8mb4	4	191	768

2） Mysql 版本 >=5.7.7

在该版本情况下，由于 InnoDB 引擎的 innodb_large_prefix 等选项默认值改变，单字段索引长度限制增大。

如果是单字段索引，则字段长度不应超过 3072 字节。
如果是联合索引，则每个字段长度都不应超过 3072 字节，且所有字段长度合计不应超过 3072 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

InnoDB	最大字节数	单字段索引最大字符数	联合索引合计最大字符数
lantin1	1	3072	3072
gbk	2	1536	1536
utf8	3	1024	1024
utf8mb4	4	768	768

对于 MyISAN 引擎来说：

如果 Mysql 版本 <=5.5.4 则其默认存储引擎为 MyISAN。

如果是单字段索引，则字段长度不应超过 1000 字节。
如果是联合索引，则每个字段长度都不应超过 1000 字节，且所有字段长度合计不应超过 1000 字节。

这种情况下，常见字符集和引擎组合后的字符长度限制如下：

MyISAM	最大字节数	单字段索引最大字符数	多字段索引合计最大字符数
lantin1	1	1000	1000
gbk	2	500	500
utf8	3	333	333
utf8mb4	4	250	250

测试语句样例

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

解决方法

修改存储引擎
修改字符集
修改索引字段的字符长度

以 Laravel 迁移为例，在 Mysql 版本 <=5.7.6 且字符集为 utf8mb4 的情况下，设置需要添加索引的字段长度为 191：

// database/migrations/2014_10_12_000000_create_users_table.php
$table->string('email', 191)->unique();

// database/migrations/2014_10_12_100000_create_password_resets_table.php
$table->string('email',191)->index();

或者，直接设置 string 属性的字段的默认长度为 191，只需要在 AppServiceProvider 类的 boot 方法中取消 Laravel 已经写好的注释即可：

// app/Providers/AppServiceProvider.php
use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}

Mysql<=5.7.6 的情况，修改 InnoDB 选项，将单字段索引长度限制由 767 字节（bytes）提高到 3072 字节。

先查看当前默认选项值：

1
2
3

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

确认选项值是否是：

1
2
3

innodb_large_prefix=ON
innodb_file_per_table=ON
innodb_file_format=Barracuda

如果不是，进行相应的修改：

1
2
3

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

上面两项的值设置为 1 而不是 ON，是因为在 Mysql5.6 之前，布尔值的变量还无法识别 ON 或者 OFF，只能是数字 1 或者 0。

并且，在创建新表时还要指定 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;

或修改当前表：

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

在创建索引时限制字段上的前缀索引长度：

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;