MySQL VARCHAR(255) UTF8 对于键来说太长，但最大长度为 1000 字节
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6172798/
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
MySQL VARCHAR(255) UTF8 is too long for key, but max length is 1000 bytes
提问by David Kanarek
I know there have been plenty of questions about this, but I think my math is right.
我知道有很多关于这个的问题，但我认为我的数学是正确的。
- MySQL reserves 3 bytes per UTF8 character.
- MyISAM allows keys of length 1000 bytes.
- My UTF8 VARCHAR(255) shouldbe
255 * 3 = 765bytes
- MySQL 为每个 UTF8 字符保留 3 个字节。
- MyISAM 允许长度为 1000 字节的密钥。
- 我的 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。对于唯一索引，似乎字符数为 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，字符集确实是 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不允许这样做，所以手动创建表）
回答by jsears
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，并且您在长度大于 191 个字符的 varchar 列上有唯一索引，则需要打开 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 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.
如果启用了 innodb_large_prefix（MySQL 5.7.7 中的默认值），对于使用 DYNAMIC 或 COMPRESSED 行格式的 InnoDB 表，索引键前缀限制为 3072 字节。如果禁用 innodb_large_prefix，则任何行格式的表的索引键前缀限制为 767 字节。
innodb_large_prefix 在 MySQL 5.7.7 中已弃用，并将在未来版本中删除。innodb_large_prefix 是在 MySQL 5.5 中引入的，用于禁用大索引键前缀，以便与不支持大索引键前缀的早期版本的 InnoDB 兼容。
To sum up, the limit is only there for compatibility and will be increased in future versions.
综上所述，限制只是为了兼容性，在以后的版本中会增加。
回答by JohnL
MySQL reserves the max amount for a UTF8field which is 4 bytes, so that is why you are blowing past the 1000 byte limit. My recommendation is to create the varcharat less than 255 or create it without UTF8.
MySQLUTF8为 4 字节的字段保留了最大数量，所以这就是为什么你要超过 1000 字节的限制。我的建议是创建varchar小于 255 或不创建UTF8.
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.
我能想到的唯一其他解决方案是将列拆分为 2 个小列并在这两个字段上创建唯一索引，但我相信您会得到与上述相同的错误。
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）以完成这项工作。
回答by Omesh
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
