执行以下命令时:

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.

其他回答

更改排序规则。您可以使用支持几乎所有功能的utf8_general_ci

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

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

在查询之前运行此查询:

SET @@global.innodb_large_prefix = 1;

这将把限制增加到3072字节。

我发现这个查询在检测哪些列的索引违反了最大长度方面很有用:

SELECT
  c.TABLE_NAME As TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS DataType,
  c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
  s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
  ON s.table_name = c.TABLE_NAME
 AND s.COLUMN_NAME = c.COLUMN_NAME 
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.CHARACTER_MAXIMUM_LENGTH > 191 
  AND c.DATA_TYPE IN ('char', 'varchar', 'text')

以下是我最初的回答:

我只是删除数据库并像这样重新创建,错误就消失了: 如果rhodes存在,则删除数据库;创建数据库rhodes default 字符集utf8默认COLLATE utf8_general_ci;

然而,它并不适用于所有情况。

这实际上是在字符集为utf8(或utf8mb4)的VARCHAR列上使用索引的问题,而VARCHAR列的字符长度超过一定长度。对于utf8mb4,这个长度是191。

有关如何在MySQL数据库中使用长索引的更多信息,请参阅本文中的长索引部分:http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4