如何修复 MySql：索引列大小太大（Laravel migrate）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42043205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to fix MySql: index column size too large (Laravel migrate)
提问by Sven van Zoelen
I duplicated a project with a vagrant box which installs Debian, Nginx, PhpMyAdmin, .. With the new project the Laravel's php artisan migrateis not working anymore and I get the error:
我用一个安装了 Debian、Nginx、PhpMyAdmin 的流浪盒复制了一个项目。在新项目中，Laravelphp artisan migrate不再工作，我收到错误消息：
[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?
如何修复大小以便我可以运行迁移方法？
回答by Paul Spiegel
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 utf8and 1020 (255*4) bytes using utf8mb4. This is because in MySQL utf8takes up to 3 bytes and utf8mb4up to 4 bytes (the real UTF8). Thus creating a VARCHAR(255)(unique) index with utf8mb4will fail.
正如您在错误消息中看到的 - “最大列大小为 767 字节”，如果您想在其上创建索引。一VARCHAR(255)列最多可以占用 765 (255*3) 个utf8字节，使用utf8mb4. 这是因为在 MySQLutf8中最多占用 3 个字节和utf8mb4最多 4 个字节（真正的 UTF8）。因此，创建VARCHAR(255)（唯一）索引 withutf8mb4将失败。
This are your options to fix the problem:
这是您解决问题的选项：
Set default collation in my.ini:
设置默认排序规则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)
为表/列设置默认排序规则。（我不建议这样做）
Change the column size to 190 (varchar(190)) or less.
将列大小更改为 190 ( varchar(190)) 或更小。
Laravel 5.4 fix
Laravel 5.4 修复
The Mysql server configuration is overwriten by Laravel's migration command. It will set the collation and charset to the configuration's version.
Mysql 服务器配置被 Laravel 的迁移命令覆盖。它将排序规则和字符集设置为配置的版本。
Change the fields charsetand collationof the db engine in the database config file located in config/database.php.
更改字段charset并collation在位于数据库配置文件中的数据库引擎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,
],
..
回答by Pratik
For mariadb, update your *my.cnffile with following configuration,
对于 mariadb，*my.cnf使用以下配置更新您的文件，
innodb_default_row_format=dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true
Then, you have to restart mariadbservice for updated configuration to take effect.
然后，您必须重新启动mariadb服务以使更新的配置生效。
回答by Rick James
Three solutions, each with a drawback:
三个解决方案，每个都有一个缺点：
MySQL 5.7 avoids the problem. Consider upgrading.
VARCHAR(255)is usually var bigger than necessary. If you can safely shrink to 191 or less, the error will go away.Switch to utf8 (from utf8mb4), ifyou don't need Chinese or Emoji.
MySQL 5.7 避免了这个问题。考虑升级。
VARCHAR(255)通常 var 比必要的大。如果您可以安全地缩小到 191 或更少，错误就会消失。如果不需要中文或表情符号，请切换到 utf8（从 utf8mb4）。
回答by Anderson Pe?aloza
By default MySQL uses the character set utf8 which means that we use 3 bytes for every 1 character. This means, column type of varchar(10) uses 30 bytes resulting in the max prefix size for compact row format to be equivalent to varchar(255). That is 255 * 3bytes = 765 bytes which is, two bytes less than the max of 767 bytes.
默认情况下，MySQL 使用字符集 utf8，这意味着我们每 1 个字符使用 3 个字节。这意味着，varchar(10) 的列类型使用 30 个字节，从而导致紧凑行格式的最大前缀大小等于 varchar(255)。即 255 * 3bytes = 765 字节，即比 767 字节的最大值少两个字节。
With innodb_large_prefix set to on and using row format COMPRESSED or DYNAMIC, you can increase the max prefix character size to 65536 bytes instead of 767 bytes. The below chart shows the max character length with InnoDB large prefix and [COMPRESSED| DYNAMIC] row formats. These values, expect for utf8mb4, are higher than the maximum row size of a table, so there is no way to hit these limits
将 innodb_large_prefix 设置为 on 并使用行格式 COMPRESSED 或 DYNAMIC，您可以将最大前缀字符大小增加到 65536 字节而不是 767 字节。下图显示了 InnoDB 大前缀和 [COMPRESSED| 的最大字符长度] 动态] 行格式。这些值（utf8mb4 除外）高于表的最大行大小，因此无法达到这些限制
More info here https://discuss.pivotal.io/hc/en-us/articles/115004086747-Apps-are-down-due-to-the-Maximum-Column-Size-is-767-bytes-Constraint-in-MySQL
回答by gtamborero
For Laravel 5.5 or higher change on your config/database.php file the engine to InnoDB ROW_FORMAT=DYNAMIC:
对于 Laravel 5.5 或更高版本，将 config/database.php 文件引擎更改为 InnoDB ROW_FORMAT=DYNAMIC：
'connections' => [
...
'mysql' => [
...
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
]]
Thanks to laracasts: https://laracasts.com/discuss/channels/eloquent/migrations-and-table-options-row-format
感谢 laracasts：https://laracasts.com/discuss/channels/eloquent/migrations-and-table-options-row-format
回答by Fulela
Also had this issue what I just did, was revert from utf8mb4_unicode_ci to utf8_unicode_ci in the db connection script
我刚刚做的也有这个问题，在数据库连接脚本中从 utf8mb4_unicode_ci 恢复到 utf8_unicode_ci
回答by Anton
It works with 5.5.52-MariaDB.
它适用于5.5.52-MariaDB。
Set all encoding to utf8_general_ci(server, database, connection).
将所有编码设置为utf8_general_ci（服务器、数据库、连接）。
