MySQL 如何创建这个表？#1071 - 指定的密钥太长；最大密钥长度为 1000 字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20014106/
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
How to create this table? #1071 - Specified key was too long; max key length is 1000 bytes
提问by Raplus
CREATE TABLE mini
(
realurl varchar(200) NOT NULL,
catagory varchar(200),
PRIMARY KEY (realurl,catagory),
FOREIGN KEY (realurl) REFERENCES main(realurl)
)
Error : `#1071 - Specified key was too long; max key length is 1000 bytes
错误：`#1071 - 指定的键太长；最大密钥长度为 1000 字节
Why I can't create this table? What should I change to create this table?
为什么我不能创建这个表？我应该改变什么来创建这个表？
回答by S.Mishra
This common problem mostly occurs because by default MySql uses character limit for column names. Which is:
这个常见问题主要是因为默认情况下 MySql 对列名使用字符限制。这是：
INNODB utf8mb4 VARCHAR(191)
What we need is given below:
我们需要的东西如下：
INNODB utf8 VARCHAR(255)
To get rid of this issue, I would suggest to use utf8 while creating database along with COLLATE set to utf8_general_ci. I prefer to use below command to create database in MariaDb or MySql:
为了摆脱这个问题，我建议在创建数据库时使用 utf8，并将 COLLATE 设置为 utf8_general_ci。我更喜欢使用以下命令在 MariaDb 或 MySql 中创建数据库：
DROP DATABASE IF EXISTS <YOUR_DATABASE_NAME>;
CREATE DATABASE <YOUR_DATABAE_NAME> DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Above command helped me in my specific scenario. I use 'Sequelize' to create tables on run time. When I tried to run my code in Ubuntu after creating database with simple 'CREATE DATABASE ' command, I got this 1071 error.
上面的命令在我的特定场景中帮助了我。我使用“ Sequelize”在运行时创建表。当我在使用简单的“CREATE DATABASE”命令创建数据库后尝试在 Ubuntu 中运行我的代码时，出现了 1071 错误。
Running above command with utf8 character set and collate helped me get rid of the error.
使用 utf8 字符集运行上面的命令并整理帮助我摆脱了错误。
回答by RSaha
PRIMARY KEY (realurl,catagory)has a size of (200 + 200) * 3 = 1,200 bytes, which is greater than the 1,000 byte limit, as MySQL stores utf8 encoded chars as 3 bytes.
PRIMARY KEY (realurl,catagory)大小为 (200 + 200) * 3 = 1,200 字节，这大于 1,000 字节的限制，因为 MySQL 将 utf8 编码字符存储为 3 个字节。
You'll need to reduce the size of the the fields that make up the primary key or you can upgrade MySQL version to the latest release.
您需要减小构成主键的字段的大小，或者您可以将 MySQL 版本升级到最新版本。
Also see this other question: Error: Specified key was too long; max key length is 1000 bytes.
另请参阅另一个问题：错误：指定的键太长；最大密钥长度为 1000 字节。
回答by Nids Barthwal
I solved this issue. Create your database using the following command .
我解决了这个问题。使用以下命令创建数据库。
CREATE DATABASE CHARACTER SET utf8;
回答by Rajasekar Gunasekaran
MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MyISAM. This has never been a problem for me, until I started using UTF-16 as the character set for one of my databases. UTF-16 can use up to 4 bytes per character which means that in an InnoDB table, you can't have any keys longer than 191 characters.
MySQL 在 InnoDB 中有 767 字节的前缀限制，在 MyISAM 中有 1000 字节的前缀限制。这对我来说从来都不是问题，直到我开始使用 UTF-16 作为我的一个数据库的字符集。UTF-16 每个字符最多可以使用 4 个字节，这意味着在 InnoDB 表中，任何键都不能超过 191 个字符。
回答by Ivan Kukurudziak
You can drop "your database" and create again with:
您可以删除“您的数据库”并再次创建：
CREATE DATABASE mydatabase CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
or change config file. Open /etc/mysql/mariadb.conf.d/50-server.cnf and change:
或更改配置文件。打开 /etc/mysql/mariadb.conf.d/50-server.cnf 并更改：
character-set-server = utf8
collation-server = utf8_general_ci
回答by Mayank Dudakiya
To fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:
要解决此问题，您只需编辑 AppServiceProvider.php 文件并在 boot 方法中设置默认字符串长度：
use Illuminate\Support\Facades\Schema;
public function boot()
{
Schema::defaultStringLength(191);
}
Laravel 5.4 made a change to the default database character set, and it's now utf8mb4 which includes support for storing emojis. This only affects new applications and as long as you are running MySQL v5.7.7 and higher you do not need to do anything.
Laravel 5.4 对默认数据库字符集进行了更改，现在是 utf8mb4，其中包括对存储表情符号的支持。这只会影响新应用程序，只要您运行 MySQL v5.7.7 及更高版本，您就不需要做任何事情。
For those running MariaDB or older versions of MySQL you may hit this error when trying to run migrations:
对于那些运行 MariaDB 或旧版本 MySQL 的用户，您可能会在尝试运行迁移时遇到此错误：
[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email))
[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
回答by Techifylogic
Try adding below settings in your My.INI file for permanent solution -
尝试在 My.INI 文件中添加以下设置以获得永久解决方案 -
## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'
回答by AnkitK
If you had changed innodb_log_file_sizetry to restore the previous value.
如果您已更改，请innodb_log_file_size尝试恢复以前的值。
