执行以下命令时:
ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);
我得到了这个错误信息:
#1071 - Specified key was too long; max key length is 767 bytes
columnn1和column2的信息:
column1 varchar(20) utf8_general_ci
column2 varchar(500) utf8_general_ci
我认为varchar(20)只需要21个字节，而varchar(500)只需要501个字节。所以总字节数是522，小于767。为什么我得到了错误消息?
#1071 - Specified key was too long; max key length is 767 bytes
当前回答
Laravel框架解决方案
根据Laravel 5.4。*文档;你必须在app/Providers/AppServiceProvider.php文件的引导方法中设置默认字符串长度，如下所示:
use Illuminate\Support\Facades\Schema;
public function boot()
{
Schema::defaultStringLength(191);
}
Laravel 5.4对这个修复的解释。*文档:
Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider. Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.
其他回答
如果您最近修改了innodb_log_file_size，请尝试恢复之前的值。
5解决方法:
在5.7.7中提高了限制(MariaDB 10.2.2?)。并且可以通过5.6(10.1)中的一些工作来增加它。
如果你因为试图使用字符集utf8mb4而达到极限。然后做以下其中一种(每一种都有缺点)来避免错误:
⚈ Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
⚈ Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈ ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈ Use a "prefix" index -- you lose some of the performance benefits.
⚈ Or... Stay with older version but perform 4 steps to raise the limit to 3072 bytes:
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- (or COMPRESSED)
——http://mysql.rjweb.org/doc.php/limits 767 _limit_in_innodb_indexes
对我来说，#1071 -指定键太长了;通过限制列大小200来改变主键/唯一键组合后，最大键长度是767字节”得到了解决。
ALTER TABLE `mytable` ADD UNIQUE (
`column1` (200) ,
`column2` (200)
);
我从varchar变成了nvarchar，对我有用。
MySQL对字符串中每个字符的字节数假设最坏的情况。对于MySQL 'utf8'编码，每个字符是3个字节，因为该编码不允许字符超过U+FFFF。对于MySQL 'utf8mb4'编码，它是每个字符4字节，因为这是MySQL所谓的实际UTF-8。
因此，假设您使用'utf8'，那么您的第一列将占用索引的60字节，第二列将占用索引的1500字节。
