执行以下命令时:

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

当前回答

我们在尝试使用utf8mb4向VARCHAR(255)字段添加UNIQUE索引时遇到了这个问题。虽然在这里已经很好地概述了这个问题,但我想为我们如何发现并解决它添加一些实际的建议。

当使用utf8mb4时,字符计数为4个字节,而在utf8下,字符计数为3个字节。InnoDB数据库有一个限制,索引只能包含767字节。因此,当使用utf8时,您可以存储255个字符(767/3 = 255),但使用utf8mb4时,您只能存储191个字符(767/4 = 191)。

你完全可以使用utf8mb4为VARCHAR(255)字段添加常规索引,但发生的事情是索引大小自动被截断为191个字符-就像这里的unique_key:

这很好,因为常规索引只是用来帮助MySQL更快地搜索数据。整个字段不需要被索引。

那么,为什么MySQL为常规索引自动截断索引,但抛出一个显式错误时,试图这样做的唯一索引?好吧,为了让MySQL能够判断插入或更新的值是否已经存在,它需要索引整个值,而不仅仅是它的一部分。

最后,如果您想在一个字段上拥有唯一的索引,那么该字段的整个内容必须适合该索引。对于utf8mb4,这意味着将VARCHAR字段长度减少到191个字符或更少。如果这个表或字段不需要utf8mb4,那么可以将其删除回utf8,并能够保留255长度的字段。

其他回答

我们在尝试使用utf8mb4向VARCHAR(255)字段添加UNIQUE索引时遇到了这个问题。虽然在这里已经很好地概述了这个问题,但我想为我们如何发现并解决它添加一些实际的建议。

当使用utf8mb4时,字符计数为4个字节,而在utf8下,字符计数为3个字节。InnoDB数据库有一个限制,索引只能包含767字节。因此,当使用utf8时,您可以存储255个字符(767/3 = 255),但使用utf8mb4时,您只能存储191个字符(767/4 = 191)。

你完全可以使用utf8mb4为VARCHAR(255)字段添加常规索引,但发生的事情是索引大小自动被截断为191个字符-就像这里的unique_key:

这很好,因为常规索引只是用来帮助MySQL更快地搜索数据。整个字段不需要被索引。

那么,为什么MySQL为常规索引自动截断索引,但抛出一个显式错误时,试图这样做的唯一索引?好吧,为了让MySQL能够判断插入或更新的值是否已经存在,它需要索引整个值,而不仅仅是它的一部分。

最后,如果您想在一个字段上拥有唯一的索引,那么该字段的整个内容必须适合该索引。对于utf8mb4,这意味着将VARCHAR字段长度减少到191个字符或更少。如果这个表或字段不需要utf8mb4,那么可以将其删除回utf8,并能够保留255长度的字段。

MySQL对字符串中每个字符的字节数假设最坏的情况。对于MySQL 'utf8'编码,每个字符是3个字节,因为该编码不允许字符超过U+FFFF。对于MySQL 'utf8mb4'编码,它是每个字符4字节,因为这是MySQL所谓的实际UTF-8。

因此,假设您使用'utf8',那么您的第一列将占用索引的60字节,第二列将占用索引的1500字节。

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

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')

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

根据下面给出的列,这两个变量字符串列使用utf8_general_ci排序规则(隐含utf8字符集)。

在MySQL中,utf8字符集每个字符最多使用3个字节。因此,它需要分配500*3=1500字节,这比MySQL允许的767字节要大得多。这就是为什么您会得到1071错误。

换句话说,您需要基于字符集的字节表示来计算字符数,因为并非每个字符集都是一个字节表示(正如您所假设的那样)。例如,MySQL中的utf8每个字符最多使用3个字节,767/3≈255个字符,而对于utf8mb4,最多使用4个字节表示,767/4≈191个字符。

众所周知,MySQL

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci