解决 Specified key was too long ... 767 bytes 的另一種方法

能用 innodb_large_prefix 解决问题就别再用 Schema::defaultStringLength(191); 解决了。要了解问题的本质，才能够找到更适当的方式解决问题。

Update Log 2020.03.28 :
感谢 @miaotiao 提醒
（1.）目前这个方法只能解决 InnoDB 的问题，对于 MyISAM 引擎还是没有用。
（2.）对于 InnoDB 使用 Redundant 或 COMPACT 行格式的表，长度的最大长度为 767 个字节。 对于 MyISAM 限制为 1000 个字节。

图片来源：https://dev.mysql.com/doc/refman/8.0/en/co...

问题描述

Laravel 在 5.4 版本之后把默认数据库字符集更改成 utf8mb4，作为对存储 emojis 的支持。对于那些运行 MariaDB 或旧版本的 MySQL 时，会在运行迁移时遇到下面的错误：

[Illuminate\Database\QueryException]
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))

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

如果你尝试在网上找解决问题的方法，通常找到的解决方法会要你在 AppServiceProvider.php 文件加入这一行，然后问题就没了：

use Illuminate\Support\Facades\Schema;

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

但这样的方法只是解决了表征，而不是本质问题。而真实原因是 Laravel 在 5.4 版本之后把默认数据库字符集更改成 utf8mb4，作为对存储 emojis 的支持。那为什么 Laravel 改用 utf8mb4 编码就会超过长度呢？

那是因为 string 资料栏位，等同于 MySQL 长度为 255 的 VARCHAR 栏位类型，所以使用 utf8 时，会使用 255 * 3 = 765 (bytes)。

当编码改成 utf8mb4 后，每个字元固定长度为4-byte，所以大小就会有 255 * 4 = 1020 (bytes) ，而超过MySQL 用来当作索引的键值长度的最大值。这也就是为什么在资料库迁移时会报错的原因所在。

上述解决问题的方法将 VARCHAR 的长度改成键值长度最大值以内。由算式767 / 4 = 191 ... 3 得知在这个范围内若以4-byte 来储存1 个字符，一共可以储存191 个字符，最大长度就只会到764 bytes，符合MySQL 标准，因而解决这个问题。

但若是你资料库已经有资料，且你无法确保现有资料中 string 资料类型的值都在 191 以下的话，就麻烦了。因此最好的解决方法不是改字符长度，而是改 MySQL 设定值。

解决方法

在 MySQL 5.5.14 之后，新增了一个名为 innodb_large_prefix 的功能，启用后可将键值长度扩充到 3072 bytes。而这个功能相依于MySQL 5.5 新增的一个叫做Barracuda 的innodb 的档案格式（File Formate），这个档案格式除了支援innodb 原有的资料列格式（Row Formats），还包括了新的资料列格式：COMPRESSED和DYNAMIC。

所以要解决这个问题，就是修改 MySQL 的选项，将档案格式改成 Barracuda，并且启用 innodb_large_prefix 这个选项去扩充键值长度。最后在 迁移表格时，指定使用 DYNAMIC 的资料列格式即可。

在 MySQL 的设定档 my.conf (或 my.ini) 修改为：(记得先备份资料库)

[mysqld]
character-set-server=utf8mb4
collat​​ion-server=utf8mb4_general_ci
innodb_large_prefix=1
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_default_row_format=DYNAMIC

[mysqldump]
default-character-set = utf8mb4

接着在 Laravel 的 config/database.php 的 mysql 索引修改 engine 这一行，让 Laravel 在运行资料时，预设都是使用 Dynamic 的存取方法运行程序。

'mysql' => [
    ...
    'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
    ...
],

以上，欢迎指教

参考资料

1. 解决 MySQL 使用 utf8mb4 编码导致 Rails 资料库迁移失败的问题
2. MySQL 问题: 1071 (42000): Specified key was too long

本作品采用《CC 协议》，转载必须注明作者和本文链接