php Laravel 迁移：唯一键太长，即使指定

Laravel migration: unique key is too long, even if specified

提问by harryg

I am trying to migrate a users table in Laravel. When I run my migration I get this error:

我正在尝试迁移 Laravel 中的用户表。当我运行迁移时，出现此错误：

[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 usersadd unique users_email_uniq(email))

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

my migration is as follows:

我的迁移如下：

Schema::create('users', function(Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('username', 32);
    $table->string('email', 320);
    $table->string('password', 64);
    $table->string('role', 32);
    $table->string('confirmation_code');
    $table->boolean('confirmed')->default(true);
    $table->timestamps();

    $table->unique('email', 'users_email_uniq');
});

After some googling I came across this bug reportwhere Taylor says you can specify the index key as the 2nd parameter of unique(), which I have done. It still gives the error. What is going on here?

经过一番谷歌搜索后，我发现了这个错误报告，其中 Taylor 说您可以将索引键指定为 的第二个参数unique()，我已经这样做了。它仍然给出错误。这里发生了什么？

回答by Antonio Carlos Ribeiro

Specify a smaller length for your e-mail:

为您的电子邮件指定较小的长度：

$table->string('email', 250);

Which is the default, actually:

这是默认值，实际上：

$table->string('email');

And you should be good.

你应该很好。

For Laravel 5.4 you can find a solution in this Laravel 5.4: Specified key was too long error, Laravel Newspost:

对于 Laravel 5.4，您可以在Laravel 5.4: Specified key was too long 错误中找到解决方案，Laravel 新闻帖子：

As outlined in the Migrations guide to fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:

如迁移指南中所述，您只需编辑 AppServiceProvider.php 文件并在 boot 方法中设置默认字符串长度即可解决此问题：

use Illuminate\Database\Schema\Builder;


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

回答by scorer

Update 1

更新 1

As of Laravel 5.4those changes are no more needed.

从Laravel 5.4 开始，不再需要这些更改。

Laravel 5.4 uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are upgrading your application from Laravel 5.3, you are not required to switch to this character set.

Laravel 5.4 默认使用 utf8mb4 字符集，包括支持在数据库中存储“表情符号”。如果你是从 Laravel 5.3 升级你的应用程序，你不需要切换到这个字符集。

Update 2

更新 2

Current production MariaDB versions DO NOTsupport this setting by default globally. It is implemented in MariaDB 10.2.2+ by default.

当前生产的 MariaDB 版本在默认情况下不支持此设置。它默认在MariaDB 10.2.2+ 中实现。

Solution

解决方案

And if you intentionally want to use the correct future-default (starting from Laravel 5.4) UTF8 multi-byte utf8mb4support for then start to fix your database configuration.

如果您有意使用正确的未来默认值（从 Laravel 5.4 开始）UTF8 多字节utf8mb4支持，请开始修复您的数据库配置。

In Laravel config/database.phpdefine:

在 Laravel 中config/database.php定义：

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

DYNAMICallows to store long keyindexes.

DYNAMIC允许存储长键索引。

Server settings (by default included in MySQL 5.7.7+ / MariaDB 10.2.2+):

服务器设置（默认包含在 MySQL 5.7.7+ / MariaDB 10.2.2+ 中）：

[mysqld]
# default character set and collation
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

# utf8mb4 long key index
innodb_large_prefix = 1
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = 1

For clients:

对于客户：

[mysql]
default-character-set=utf8mb4

And then STOPyour MySQL/MariaDB server. After that START. Hot RESTART may not work.

然后停止你的 MySQL/MariaDB 服务器。在那之后开始。热重启可能不起作用。

sudo systemctl stop mysqld
sudo systemctl start mysqld

Now you have Laravel 5.x with UTF8 support.

现在你有了支持 UTF8 的 Laravel 5.x。

回答by varta

If you're on or updated to Laravel 5.4 This worked for me;

如果您使用或更新到 Laravel 5.4 这对我有用；

Just 1 change. in AppServiceProvider.php

只需 1 次更改。在 AppServiceProvider.php 中

use Illuminate\Support\Facades\Schema;

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

As mentioned in the migrate guide https://laravel.com/docs/master/migrations#creating-indexes

如迁移指南中所述https://laravel.com/docs/master/migrations#creating-indexes

回答by Brendan

If anyone else stumbles upon this answer like I did but for a different reason, you may check your Laravel DB charset/collation.

如果其他人像我一样偶然发现了这个答案，但出于不同的原因，您可以检查您的 Laravel DB 字符集/排序规则。

I was installing an application (Snipe-IT) and had configured the Laravel database config to use the following:

我正在安装一个应用程序（Snipe-IT）并配置了 Laravel 数据库配置以使用以下内容：

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_general_ci',

Removing mb4from both strings fixed the issue, although I believe Antonio's answer is the real solution to the problem.

mb4从两个字符串中删除都解决了这个问题，尽管我相信安东尼奥的答案是解决问题的真正方法。

回答by user3278647

This worked for me:

这对我有用：

 $table->charset = 'utf8';
 $table->collation = 'utf8_unicode_ci';

回答by Ramv V

Remove mb4 from charset and collation from config/database.php, then it will execute successfully.
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

将 mb4 从 charset 和 collat​​ion 从 config/database.php 中删除，然后它就会成功执行。
'charset' => 'utf8',
'collat​​ion' => 'utf8_unicode_ci',

回答by Avijit Mandal

For laravel 5.6
This solution solve my problem
go to config/database.php
Find the code below

对于laravel 5.6
这个解决方案解决了我的问题
去config/database.php
查找下面的代码

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Change this two field

更改这两个字段

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

With This

有了这个

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

回答by Muthu17

I have faced the same issue, and fixed it by adding the below two lines in my app/database.php

我遇到了同样的问题，并通过在我的 app/database.php 中添加以下两行来修复它

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

My file looks like below :

我的文件如下所示：

<?php

return [

    /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */

    'default' => env('DB_CONNECTION', 'mysql'),

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

    ......

回答by Vanndy

For laravel 5.4, simply edit file

对于laravel 5.4，只需编辑文件

App\Providers\AppServiceProvider.php

App\Providers\AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

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

回答by Faizan Noor

File: config/database.php
change the following
FROM ->
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

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