解决 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.phpmysql 索引修改 engine 这一行,让 Laravel 在运行资料时,预设都是使用 Dynamic 的存取方法运行程序。

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

以上,欢迎指教

参考资料

  1. 解决 MySQL 使用 utf8mb4 编码导致 Rails 资料库迁移失败的问题
  2. MySQL 问题: 1071 (42000): Specified key was too long
本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由系统于 1个月前 自动加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 5
sunxyw

我认为这才是解决 Specified key was too long ... 767 bytes 问题的正确方法,像 Schema::defaultStringLength(191); 这种治标不治本的方法不应该被提倡。

2个月前 评论
rc1021 (楼主) 2个月前

分析的很详细 :+1:

2个月前 评论

我有点小问题:

  • 首先,你这个方法只能解决 InnoDB 的问题,对于 MyISAM 引擎还是没有用。
  • 其次,mysql 新老版本应该都会有这个问题吧。下图是我在 mysql 官网截取的 mysql 8.0 的文档。单列索引的长度,MyISAM 还是1000 bytes,InnoDB 根据行格式的不同,索引长度也不同,只有 dynamic 和 compressed 是 3072 bytes。因为 mysql 8.0 中,InnoDB 引擎默认使用 dynamic ,所以 Column Indexes 才是 3072 bytes。 所以并不是只有旧版本的 mysql 会有这种问题。
    file
2个月前 评论
miaotiao (作者) 2个月前
rc1021 (楼主) 2个月前
songxue77

多谢多谢,刚好遇上这个问题,解决了~!

2个月前 评论

牛比,收藏了

1个月前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!