laravel 5.4 指定key太长，为什么数字是191
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43832166/
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
laravel 5.4 specified key was too long, why the number 191
提问by ronrun
I know this problem is discussed, and solved or closed.
But what I want to ask is, why is the number 191?
我知道这个问题已被讨论、解决或关闭。
但我想问的是，为什么数字是191？
Schema::defaultStringLength(191);
I tried: if 192, wrong. 191, ok.
After this, I also edit database\migrations\xxx_user_table.php
Change this
我试过：如果是192，就错了。191，好的。在此之后，我还编辑 database\migrations\xxx_user_table.php
更改此
$table->string('name');
to be
成为
$table->string('name', 255);
Then run php artisan migrate, it's ok. Use some mysql tool to see the schema of user table, length of name which is varchar is actually 255, others columns are 191.
然后运行php artisan migrate，就OK了。用一些mysql工具查看user表的schema，varchar的name长度实际上是255，其他列是191。
I saw these articles
@ https://laravel-news.com/laravel-5-4-key-too-long-error
@ Laravel migration: unique key is too long, even if specified
@ https://github.com/laravel/framework/issues/17508
@ https://laravel.com/docs/master/migrations#creating-indexes
我看到这些文章
@ https://laravel-news.com/laravel-5-4-key-too-long-error
@ Laravel迁移：唯一键太长，即使指定
@ https://github.com/ laravel/framework/issues/17508
@ https://laravel.com/docs/master/migrations#creating-indexes
And this: @ https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
还有这个：@ https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.
对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，索引键前缀长度限制为 767 字节。例如，假设使用 utf8mb3 字符集且每个字符最多 3 个字节，您可能会在 TEXT 或 VARCHAR 列上使用超过 255 个字符的列前缀索引达到此限制。
But why length 255 of name is ok?
And 192*3 = 576, smaller than 767, why 192 not ok?
但是为什么名称的长度为 255 就可以了？
而192*3 = 576，比767还小，为什么192不行呢？
回答by Luke Waite
Why is it limited to 191 characters?
为什么限制为 191 个字符？
The key is that Laravel 5.4 defaults to utf8mb4so the database assumes a maximum of 4 bytes for each character.
关键是 Laravel 5.4默认是utf8mb4这样，所以数据库假设每个字符最多 4 个字节。
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
The example you've cited lines up perfectly if updated for utf8mb4. Emphasisadded where edited.
如果针对 utf8mb4 进行更新，您引用的示例将完美匹配。在编辑的地方添加了重点。
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 191characters on a TEXT or VARCHAR column, assuming a utf8mb4character set and the maximum of 4bytes for each character.
对于使用 REDUNDANT 或 COMPACT 行格式的 InnoDB 表，索引键前缀长度限制为 767 字节。例如，假设utf8mb4字符集和每个字符最多4个字节，您可能会在 TEXT 或 VARCHAR 列上的列前缀索引超过191 个字符时达到此限制。
191 * 4 = 764(works)192 * 4 = 768(too large)
191 * 4 = 764（作品）192 * 4 = 768（太大了）
utf8mb4introduces support for Supplementary Characterswhich were not part of utf8which include among other things, emoji. For further reading on whether you need utf8mb4, you might start with this StackOverflow answer on the question.
utf8mb4引入了Supplementary Characters不属于utf8其中的支持，其中包括表情符号。要进一步了解您是否需要utf8mb4，您可以从这个 StackOverflow 对这个问题的回答开始。
How do I fix this?
我该如何解决？
The quickest fix, if you need to maintain the larger field size, is to change your defaults down to utf8. Laravel 5.3 and below defaulted utf8, and that would be safe to use in most cases.
如果您需要保持较大的字段大小，最快的解决方法是将默认值更改为utf8. Laravel 5.3 及以下默认为utf8，在大多数情况下使用它是安全的。
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
Alternatively, you can set the charsetand collationon specific tables in the DB Migration when creating that table. That would look something like this:
或者，您可以在创建该表时在 DB Migration 中的特定表上设置charset和collation。那看起来像这样：
Schema::create('monitors', function (Blueprint $table) {
$table->charset = 'utf8';
$table->collation = 'utf8_unicode_ci';
$table->increments('id');
$table->string('myString')->unique();
});
What If I need utf8mb4and long index keys?
如果我需要utf8mb4长索引键怎么办？
This StackOverflow answershould be a good starting point. It discusses setting innodb_large_prefixto allow longer key lengths.
这个 StackOverflow 答案应该是一个很好的起点。它讨论innodb_large_prefix了允许更长密钥长度的设置。
