解決 Specified key was too long ... 767 bytes 的本質問題

rc1021發表於2020-03-21

別再用 Schema::defaultStringLength(191); 解決 Specified key was too long; max key length is 767 bytes 問題了! 士的精神，要了解問題的底層，才能解決問題的本質。

> 問題描述

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 協議》，轉載必須註明作者和本文連結

相關文章