在使用 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

  1. 如果是单字段索引,则字段长度不应超过 767 字节。
  2. 如果是联合索引,则每个字段长度都不应超过 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 等选项默认值改变,单字段索引长度限制增大。

  1. 如果是单字段索引,则字段长度不应超过 3072 字节。
  2. 如果是联合索引,则每个字段长度都不应超过 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。

  1. 如果是单字段索引,则字段长度不应超过 1000 字节。
  2. 如果是联合索引,则每个字段长度都不应超过 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;

二、解决方法

  1. 修改存储引擎

  2. 修改字符集

  3. 修改索引字段的字符长度

    以 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);
    }
    
  1. Mysql<=5.7.6 的情况,修改 InnoDB 选项,将单字段索引长度限制由 767 字节(bytes)提高到 3072 字节。

    先查看当前默认选项值:

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

    确认选项值是否是:

    innodb_large_prefix=ON
    innodb_file_per_table=ON
    innodb_file_format=Barracuda
    

    如果不是,进行相应的修改:

    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=DYNAMICROW_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;
    

    或修改当前表:

    ALTER TABLE [...], ROW_FORMAT=DYNAMIC;
    
  2. 在创建索引时限制字段上的前缀索引长度:

    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;
    

三、参考文章

MySQL InnoDB引擎索引长度受限怎么办?

MySQL 经典案例分析:Specified key was too long

mysql change innodb_large_prefix

MySQL 5.5 innodb_large_prefix configuration setting not taking effect

innodb_file_format 选项官方文档

InnoDB Row Formats 行格式官方文档

default_storage_engine 选项官方文档

(完)