长字符串的MySQL索引 [英] MySQL index for long strings

问题描述

我有一个MySQL InnoDb表，我想在其中存储长(限制为20k个符号)字符串.有什么方法可以为此字段创建索引吗?

I have MySQL InnoDb table where I want to store long (limit is 20k symbols) strings. Is there any way to create index for this field?

推荐答案

您将需要限制索引的长度，否则可能会得到创建索引上的MySQL手册条目对此进行了描述:

You will need to limit the length of the index, otherwise you are likely to get error 1071 ("Specified key was too long"). The MySQL manual entry on CREATE INDEX describes this:

可以使用col_name(length)语法指定仅使用列值的开头部分的索引来创建索引前缀长度:

Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

• 可以为CHAR，VARCHAR，BINARY和VARBINARY列指定前缀.

• Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns.

BLOB和TEXT列也可以建立索引，但是必须给出前缀长度.

BLOB and TEXT columns also can be indexed, but a prefix length must be given.

对于非二进制字符串类型，前缀长度以字符形式给出；对于二进制字符串类型，以字节为单位形式.也就是说，索引条目由CHAR，VARCHAR和TEXT列的每个列值的首长字符以及BINARY，VARBINARY和BLOB列的每个列值的首长字节组成.

Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns.

它还添加了以下内容:

前缀支持和前缀长度(如果支持)取决于存储引擎.例如，对于MyISAM表，前缀的最大长度为1000字节，对于InnoDB表，前缀的最大长度为767字节.

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

这篇关于长字符串的MySQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！