执行以下命令时:

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

当前回答

如果您最近修改了innodb_log_file_size,请尝试恢复之前的值。

其他回答

如果你要创建如下内容:

CREATE TABLE IF NOT EXISTS your_table (
  id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

应该是这样的

CREATE TABLE IF NOT EXISTS your_table (
      id int(7) UNSIGNED NOT NULL AUTO_INCREMENT,
      name varchar(256) COLLATE utf8mb4_bin NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ROW_FORMAT=FIXED;

但是您需要从代码中检查该列的唯一性,或者添加一个新列作为varchar列的MD5或SHA1

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

laravel 5.7到9.0

应遵循的步骤

进入App\Providers\AppServiceProvider.php。 将此添加到提供商使用Illuminate\Support\Facades\Schema;在顶部。 在Boot函数中添加这个Schema::defaultStringLength(191);

就这些,好好享受吧。

这解决了我的问题

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE

索引长度& 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