MySQL VARCHAR(255)UTF8对于密钥而言太长，但最大长度为1000个字节 [英] MySQL VARCHAR(255) UTF8 is too long for key, but max length is 1000 bytes

问题描述

我知道对此有很多疑问，但是我认为我的数学是正确的.

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

• MySQL每个UTF8字符保留3个字节.
• MyISAM允许长度为1000个字节的密钥.
• 我的UTF8 VARCHAR(255)应该为255 * 3 = 765个字节

• MySQL reserves 3 bytes per UTF8 character.
• MyISAM allows keys of length 1000 bytes.
• My UTF8 VARCHAR(255) should be 255 * 3 = 765 bytes

除非UNQUE每个条目需要额外的200+字节，否则为什么行不通?

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

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

对此我能做些什么吗?

结果是限制为250.对于唯一索引，字符数计为4个字节，但我不知道为什么.

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

感谢Vladislav Vaintroub，这个字符集确实是utf8mb4.那解决了这个谜.我没有看到有关此更改的任何文档.

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

我猜想它通过隐式截断字段来构建非唯一索引，这对于唯一索引是不可接受的，因此它会拒绝.

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.

解决方案::指定utf8，而不是utf8mb4(MySQL Admin不允许这样做，因此请手动创建表)

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

推荐答案

如果您使用的是utf8mb4，并且varchar列上的唯一索引长度超过191个字符，则需要将innodb_large_prefix设置为允许在索引中使用更大的列，因为utf8mb4比utf8或latin1需要更多的存储空间.将以下内容添加到您的my.cnf文件中.

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.

[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

---

MySQL 5.7文档<:

如果启用了innodb_large_prefix(MySQL 5.7.7中的默认设置)，则 使用的InnoDB表的索引键前缀限制为3072个字节 动态或压缩行格式.如果禁用了innodb_large_prefix， 任何行格式的表的索引键前缀限制为767个字节.

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中已弃用，将被删除 在将来的版本中.在MySQL 5.5中引入了innodb_large_prefix 禁用大索引键前缀以与早期版本兼容 不支持大索引键前缀的InnoDB版本.

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.

总而言之，此限制仅出于兼容性考虑，在以后的版本中将增加.

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

这篇关于MySQL VARCHAR(255)UTF8对于密钥而言太长，但最大长度为1000个字节的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！