字符串 mysql索引_长字符串的MySQL索引

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?

解决方案

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:

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

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

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

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.

It also adds this:

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.