如何修复MySql:索引列大小太大(Laravel迁移)

问题描述

我用一个无用的盒子复制了一个项目，该项目安装了Debian，Nginx，PhpMyAdmin等.对于新项目，Laravel的php artisan migrate不再起作用了，我得到了错误:

I duplicated a project with a vagrant box which installs Debian, Nginx, PhpMyAdmin, .. With the new project the Laravel's php artisan migrate is not working anymore and I get the error:

[Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. (SQL: alter table `courses` add unique `courses_name_unique`(`na
  me`))

当我对工作项目数据库进行转储(结构+数据)并将其导入数据库时​​，如果存在迁移错误，则一切正常，它将创建所有表并导入数据.

When I make a dump (structure + data) of the working project database and import it in the database giving the errors on migrate, then everything is ok and it creates all the tables and data is imported..

如何确定尺寸，以便运行迁移方法?

How can I fix the size so that I can run the migrate method?

推荐答案

如果您要在其上创建索引，则会在错误消息中看到-最大列大小为767字节". VARCHAR(255)列最多可使用utf8占用765(255 * 3)字节，而使用utf8mb4最多可占用1020(255 * 4)字节.这是因为在MySQL中，utf8最多占用3个字节，而utf8mb4最多占用4个字节(实际的UTF8).因此，使用utf8mb4创建VARCHAR(255)(唯一)索引将失败.

As you can see in the error message - "The maximum column size is 767 bytes", if you want to create an index on it. A VARCHAR(255) column can take up to 765 (255*3) bytes using utf8 and 1020 (255*4) bytes using utf8mb4. This is because in MySQL utf8 takes up to 3 bytes and utf8mb4 up to 4 bytes (the real UTF8). Thus creating a VARCHAR(255) (unique) index with utf8mb4 will fail.

这是解决问题的选项:

在my.ini中设置默认排序规则:

Set default collation in my.ini:

collation_server=utf8_unicode_ci
character_set_server=utf8

在创建时为数据库设置默认排序规则:

Set default collation for the database when creating:

CREATE DATABASE IF NOT EXISTS `your_db` COLLATE 'utf8_unicode_ci'

设置表/列的默认排序规则. (我不建议这样做)

Set default collation for the table/column. (I don't recommend that)

将列大小更改为190(varchar(190))或更小.

Change the column size to 190 (varchar(190)) or less.

Laravel的迁移命令将覆盖Mysql服务器的配置.它将排序规则和字符集设置为配置的版本.

The Mysql server configuration is overwriten by Laravel's migration command. It will set the collation and charset to the configuration's version.

更改位于config/database.php的数据库配置文件中db引擎的字段charset和collation.

Change the fields charset and collation of the db engine in the database config file located in 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', ''),
            //'charset' => 'utf8mb4',
            //'collation' => 'utf8mb4_unicode_ci',
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
..

这篇关于如何修复MySql:索引列大小太大(Laravel迁移)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！