执行以下命令时:

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

当前回答

您可以添加长列的md5列

其他回答

为了解决这个问题,这个方法对我很有效。

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

您使用的是什么字符编码?有些字符集(如UTF-16等等)每个字符使用一个以上的字节。

索引长度& MySQL / MariaDB


Laravel默认使用utf8mb4字符集,其中包括在数据库中存储“表情符号”的支持。如果您运行的MySQL版本比5.7.7版本更老,或者MariaDB版本比10.2.2版本更老,您可能需要手动配置由迁移生成的默认字符串长度,以便MySQL为它们创建索引。你可以在AppServiceProvider中调用Schema::defaultStringLength方法来配置:

use Illuminate\Support\Facades\Schema; /** * Bootstrap any application services. * * @return void */ public function boot() { Schema::defaultStringLength(191); } 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. Reference from blog : https://www.scratchcode.io/specified-key-too-long-error-in-laravel/ Reference from Official laravel documentation : https://laravel.com/docs/5.7/migrations

由于前缀限制,将发生此错误。在5.7之前的MySQL版本中,对于InnoDB表的前缀限制是767字节。MyISAM表有1000字节长。在MySQL 5.7及以上版本中,这个限制已经增加到3072字节。

在出现错误的服务上运行以下操作可以解决您的问题。这必须在MYSQL CLI中运行。

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;
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.