Laravel5.4 資料庫遷移錯誤 SQLSTATE [42000] Syntax error or access violation 1071

Hans941發表於2018-10-15
Laravel資料庫SQLError

laralve5.4版本中我寫好了資料庫遷移檔案, 在本機的mysql5.7.23中執行沒有問題.
但是提交git後同事(mysql5.6.*)執行該遷移就報錯了很是奇怪.
從網上查詢解決方案後,又嘗試著深入寫分析了下問題
以下記錄了查詢錯誤的過程.

In Connection.php line 647:                       
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `password_reset  
  s` add index `password_resets_email_index`(`email`))

從報錯資訊可以看出:索引長度的最大限制是767bytes, 而欲在email欄位建立的索引長度超過了這個範圍

計算email欄位索引長度

首先檢視建立email欄位並加索引的檔案

 table->string('email')->index();

這樣建立的email欄位預設型別varchar預設長度為255, 編碼(Collation)型別會讀取laravelProject/config/database.php 中的配置

<?php

return [
    'connections' => [
        //...
        'mysql' => [
        //...
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
        ],
        //...
    ],
];

可以看出varchar會使用utf8mb4編碼, utf8mb4編碼(4bytes)屬於utf8編碼(3bytes)的擴充套件型別,
由此可以計算索引長度為255*4+2=1022 >767

分析錯誤原因

對比laravel5.3和laravel5.4的laravelProject/config/database.php檔案 可以看出
laravel5.4中改變了預設的編碼型別(utf8 => utf8mb4), 從而使email欄位索引長度超過了的預設限制長度.
而新版本mysql>= 5.7.7
(mysql5.7.7更新日誌):預設開啟了innodb_large_prefix ,使索引長度限制由767bytes增加到了3072bytes. 所以不會導致遷移報錯.

mysql5.7.7更新日誌

四種解決方案

知道原因後解決方案就很明顯了

  1. 編輯AppServiceProvider.php 

    use Illuminate\Support\Facades\Schema;
public function boot()
{
Schema::defaultStringLength(191);
}

    原理:改變了預設的string()方法生成的varchar長度,而191*4+2 = 766 < 767合法;

  2. 修改laravelProject/config/database.php 配置檔案

    <?php
return [
'connections' => [
   //...
   'mysql' => [
   //...
       'charset' => 'utf8',
       'collation' => 'utf8_unicode_ci',
   ],
   //...
],
];

    原理: 將預設編碼仍然修改為utf8編碼.

  3. 升級mysql>=5.7.7

  4. 修改mysql配置

    如果由於某些原因你不能升級你的資料庫版本,則可以單獨修改資料庫的配置,解除innodb_large_prefix的限制.(需FQ)

以上解決方案任選其一即可!!!

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章