MySQL Laravel 迁移错误：语法错误或访问冲突：1071 指定的键太长；最大密钥长度为 767 字节

Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

提问by absiddiqueLive

Migration error on Laravel 5.4 with php artisan make:auth

Laravel 5.4 上的迁移错误 php artisan make:auth

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter tabl e usersadd 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

[Illuminate\Database\QueryException] SQLSTATE[42000]：语法错误或访问冲突：1071 指定的键太长；最大密钥长度为 767 字节（SQL：alter table e usersadd unique users_email_unique( email)）

[PDOException] SQLSTATE[42000]: 语法错误或访问冲突：1071 指定的键太长；最大密钥长度为 767 字节

回答by absiddiqueLive

According to the official documentation, you can solve this quite easily.

根据官方文档，你可以很容易地解决这个问题。

Add following code to AppServiceProvider.php(/app/Providers/AppServiceProvider.php)

将以下代码添加到AppServiceProvider.php(/app/Providers/AppServiceProvider.php)

use Illuminate\Database\Schema\Builder; // Import Builder where defaultStringLength method is defined

function boot()
{
    Builder::defaultStringLength(191); // Update defaultStringLength
}

MySQL reserves always the max amount for a UTF8 field which is 4 bytes so with 255 + 255 with your DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; you are over the 767 max key length limit. By @scaisedge

MySQL 始终为 UTF8 字段保留最大数量，即 4 个字节，因此 255 + 255 与您的 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 您已超过 767 最大密钥长度限制。来自@scaisege

回答by Koushik Das

I don't know why the above solution and the official solution which is adding

我不知道为什么上面的解决方案和正在添加的官方解决方案

Schema::defaultStringLength(191);

in AppServiceProviderdidn't work for me. What worked for was editing the database.phpfile in configfolder. Just edit

inAppServiceProvider对我不起作用。有效的是编辑database.php文件config夹中的文件。只需编辑

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

to

到

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

and it should work, although you will be unable to store extended multibyte characters like emoji.

它应该可以工作，尽管您将无法存储 emoji 等扩展的多字节字符。

I did it with Laravel 5.7. Hope it helps.

我用 Laravel 5.7 做到了。希望能帮助到你。

回答by Dexter Bengil

I'm just adding this answer here as it's the quickestsolution for me. Just set the default database engine to 'InnoDB'on

我只是在这里添加这个答案，因为它是quickest我的解决方案。只需将默认数据库引擎设置为'InnoDB'on

/config/database.php

/config/database.php

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

then run php artisan config:cacheto clear and refresh the configuration cache

然后运行php artisan config:cache以清除并刷新配置缓存

回答by user7688086

In the AppServiceProvider.php,you include this code top of the file.

在 中AppServiceProvider.php，您在文件的顶部包含此代码。

use Illuminate\Support\Facades\Schema;

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

回答by Esteban Herrera

This issue is caused in Laravel 5.4 by the database version.

这个问题是在 Laravel 5.4 中由数据库版本引起的。

According to the docs(in the Index Lengths & MySQL / MariaDBsection):

根据文档（在该Index Lengths & MySQL / MariaDB部分中）：

Laravel uses the utf8mb4character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLengthmethod within your AppServiceProvider.

Laravelutf8mb4默认使用字符集，其中包括支持在数据库中存储“表情符号”。如果您运行的 MySQL 版本早于 5.7.7 版本或 MariaDB 早于 10.2.2 版本，则可能需要手动配置迁移生成的默认字符串长度，以便 MySQL 为它们创建索引。您可以通过调用这个配置 Schema::defaultStringLength你的内法AppServiceProvider。

In other words, in <ROOT>/app/Providers/AppServiceProvider.php:

换句话说，在<ROOT>/app/Providers/AppServiceProvider.php：

// Import Schema
use Illuminate\Support\Facades\Schema;
// ...

class AppServiceProvider extends ServiceProvider
{

public function boot()
{
    // Add the following line
    Schema::defaultStringLength(191);
}

// ...

}

But as the comment on the other answer says:

但正如对另一个答案的评论所说：

Be careful about this solution. If you index email fields for example, stored emails can only have a max length of 191 chars. This is less than the official RFC states.

请注意此解决方案。例如，如果您为电子邮件字段编制索引，则存储的电子邮件最多只能有 191 个字符。这比官方 RFC 规定的要少。

So the documentation also proposes another solution:

所以文档还提出了另一种解决方案：

Alternatively, you may enable the innodb_large_prefixoption for your database. Refer to your database's documentation for instructions on how to properly enable this option.

或者，您可以innodb_large_prefix为您的数据库启用该选项。有关如何正确启用此选项的说明，请参阅您的数据库文档。

回答by helloroy

For someone who don't want to change AppServiceProvider.php. (In my opinion, it's bad idea to change AppServiceProvider.phpjust for migration)

对于不想改变的人AppServiceProvider.php。（在我看来，AppServiceProvider.php仅仅为了迁移而改变是个坏主意）

You can add back the data length to the migration file under database/migrations/as below:

您可以将数据长度添加回迁移文件database/migrations/，如下所示：

create_users_table.php

create_users_table.php

$table->string('name',64);
$table->string('email',128)->unique();

create_password_resets_table.php

create_password_resets_table.php

$table->string('email',128)->index();

回答by Abdul Rasheed

If you face this error while work on laravel while using command: php artisan migratethen you just add 2 lines in the file: app->Providers->AppServiceProvider.php

如果您在使用命令时在 laravel 上工作时遇到此错误：php artisan migrate那么您只需在文件中添加 2 行：app->Providers->AppServiceProvider.php

1. use Schema;
2. Schema::defaultStringLength(191);

1. use Schema;
2. Schema::defaultStringLength(191);

please check this image. then run php artisan migratecommand again.

请检查这张图片。然后php artisan migrate再次运行命令。

回答by Anjani Barnwal

update & insert these lines in app/Providers/AppServiceProvider.php

更新并在 app/Providers/AppServiceProvider.php 中插入这些行

use Illuminate\Support\Facades\Schema;  // add this line at top of file

public function boot()
{
    Schema::defaultStringLength(191); // add this line in boot method
}

回答by Arslan Ahmad

I am adding two sollutionthat work for me.

我正在添加两种对我有用的解决方案。

1st sollution is:

第一个解决方案是：

1. Open database.phpfile insde configdir/folder.

2. Edit 'engine' => null,to 'engine' => 'InnoDB',

   This worked for me.

1. 打开database.php文件 insde配置目录/文件夹。

2. 编辑'engine' => null,为'engine' => 'InnoDB',

   这对我有用。

2nd sollution is:

第二种解决方案是：

1. Open database.phpfile insde configdir/folder.
   2.Edit
   'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
   to
   

   'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',

1. 打开database.php文件 insde配置目录/文件夹。
   2.编辑
   'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
   为
   

   'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',

Goodluck

祝你好运

回答by Udhav Sarvaiya

I have found two solutions for this error

我找到了针对此错误的两种解决方案

OPTION 1:

选项1：

Open your userand password_resettable in database/migrationsfolder

在database/migrations文件夹中打开您的user和password_reset表

And just change the length of the email:

只需更改电子邮件的长度：

$table->string('email',191)->unique();

---

---

OPTION 2:

选项 2：

Open your app/Providers/AppServiceProvider.phpfile and inside the boot()method set a default string length:

打开app/Providers/AppServiceProvider.php文件并在boot()方法中设置默认字符串长度：

use Illuminate\Support\Facades\Schema;

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