MySQL VARCHAR(255) UTF8对于键来说太长了，但是最大长度是1000字节

I know there have been plenty of questions about this, but I think my math is right.

我知道有很多关于这个的问题，但是我认为我的计算是正确的。

MySQL reserves 3 bytes per UTF8 character.
MySQL每个UTF8字符保留3字节。
MyISAM allows keys of length 1000 bytes.
MyISAM允许长度为1000字节的键。
My UTF8 VARCHAR(255) should be 255 * 3 = 765 bytes
我的UTF8 VARCHAR(255)应该是255 * 3 = 765字节

Unless UNQUE requires an extra 200+ bytes per entry, why doesn't this work?

除非UNQUE要求每个条目额外增加200+字节，否则为什么不这样做呢?

mysql> ALTER TABLE entry ADD UNIQUE INDEX (name(255));
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Is there anything I can do about this?

我能做点什么吗?

EDIT:

编辑:

It turns out the limit is 250. It seems chars count as 4 bytes for unique indices, but I don't know why.

结果是250。看起来chars对于唯一索引是4字节，但是我不知道为什么。

EDIT 2:

编辑2:

Thanks Vladislav Vaintroub, the charset is indeed utf8mb4. That solves the mystery. I hadn't seen any documentation on this change.

谢谢Vladislav Vaintroub, charset确实是utf8mb4。这种方法解决了谜。我没有看到任何关于这个变化的文档。

I'm guessing it builds the non unique index by implicitly truncating the field, which is unacceptable for unique indices so it refuses.

我猜它是通过隐式截断字段来构建非唯一索引的，这对于唯一索引是不可接受的，所以它拒绝。

If you re-enter your comment as an answer I'd be happy to accept it.

如果你重新输入你的评论作为回答，我很乐意接受。

Solution: Specify utf8, not utf8mb4 (MySQL Admin doesn't allow this, so create the table manually)

解决方案:指定utf8，而不是utf8mb4 (MySQL Admin不允许这样，所以要手动创建表)

3 个解决方案

#1

If you're using utf8mb4, and you have unique indexes on varchar columns that are greater than 191 characters in length, you'll need to turn on innodb_large_prefix to allow for larger columns in indexes, because utf8mb4 requires more storage space than utf8 or latin1. Add the following to your my.cnf file.

如果您使用的是utf8mb4，并且varchar列上的索引长度大于191个字符，那么您需要打开innodb_large_prefix，以便允许索引中的列更大，因为utf8mb4需要比utf8或latin1更多的存储空间。将以下内容添加到my.cnf文件中。

[mysqld]
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

More info about the why and future from MySQL 5.7 documentation:

更多关于为什么和未来从MySQL 5.7文件:

If innodb_large_prefix is enabled (the default in MySQL 5.7.7), the index key prefix limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format. If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any row format.

如果启用了innodb_large_prefix (MySQL 5.7.7中的默认值)，那么对于使用动态或压缩行格式的InnoDB表，索引键前缀限制为3072字节。如果innodb_large_prefix被禁用，那么对于任何行格式的表，索引键前缀限制为767字节。

innodb_large_prefix is deprecated in MySQL 5.7.7 and will be removed in a future release. innodb_large_prefix was introduced in MySQL 5.5 to disable large index key prefixes for compatibility with earlier versions of InnoDB that do not support large index key prefixes.

在MySQL 5.7.7中，innodb_large_prefix已被弃用，并将在以后的版本中删除。在MySQL 5.5中引入了innodb_large_prefix，以禁用大型索引键前缀，以便与不支持大型索引键前缀的早期版本InnoDB兼容。

To sum up, the limit is only there for compatibility and will be increased in future versions.

总而言之，这个限制仅仅是为了兼容性，在未来的版本中将会增加。

#2

Anyone who does need a larger key length should look at innodb_large_prefix

任何需要较大密钥长度的人都应该查看innodb_large_prefix

visit http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_large_prefix

访问http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html sysvar_innodb_large_prefix

#3

MySQL reserves the max amount for a UTF8 field which is 4 bytes, so that is why you are blowing past the 1000 byte limit. My recommendation is to create the varchar at less than 255 or create it without UTF8.

MySQL保留UTF8字段的最大数量，即4个字节，这就是为什么您要超过1000字节的限制。我的建议是在小于255的时候创建varchar，或者在没有UTF8的情况下创建varchar。

Both of those solutions are probably not right for you or you would have already tried that.

这两种解决方案都可能不适合你，或者你已经尝试过了。

The only other solution I can think of is to split the column into 2 small columns and create an unique index on both of those fields, but I believe that you would get the same error as above.

我所能想到的唯一另一个解决方案是将这一列分割成两个小的列，并在这两个字段上创建一个惟一的索引，但是我相信您会得到与上面相同的错误。

Since you probably need UTF8, I would seriously look at reducing the varchar(255) column down a little to 250 (or 249) to make this work.

由于您可能需要UTF8，因此我将认真考虑将varchar(255)列减少到250(或249)，以使其能够工作。

#1

[mysqld]
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci