如何修复 MySql:索引列大小太大(Laravel migrate) [英] How to fix MySql: index column size too large (Laravel migrate)

问题描述

我用一个安装了 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:

[IlluminateDatabaseQueryException]                                                                                                                                      
  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.

这是您解决问题的选项:

This are your options to fix the problem:

在 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.

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

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 的数据库配置文件中更改数据库引擎的字段 charset 和 collat​​ion.

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 migrate)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！