执行以下命令时:
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
当前回答
Specified key was too long; max key length is 767 bytes
You got that message because 1 byte equals 1 character only if you use the latin-1 character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
其他回答
MySQL对字符串中每个字符的字节数假设最坏的情况。对于MySQL 'utf8'编码，每个字符是3个字节，因为该编码不允许字符超过U+FFFF。对于MySQL 'utf8mb4'编码，它是每个字符4字节，因为这是MySQL所谓的实际UTF-8。
因此，假设您使用'utf8'，那么您的第一列将占用索引的60字节，第二列将占用索引的1500字节。
将抱怨索引字段的CHARSET更改为“latin1” 例如:ALTER TABLE tbl CHANGE myfield varchar(600) CHARACTER SET latin1 DEFAULT NULL Latin1为一个字符使用一个字节，而不是四个
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.
在导入文件中用utf8替换utf8mb4。
但请注意，utf8字符集已弃用，它不支持所有的Unicode字符，例如表情符号，所以如果这样做，您将失去对Unicode的完全支持。
Specified key was too long; max key length is 767 bytes
You got that message because 1 byte equals 1 character only if you use the latin-1 character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
