Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the error problem of database ERROR 1071 (42000)". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The following error occurred when indexing a table today:

Mysql > ALTER TABLE ym_sys_dict ADD INDEX idx_dcode_dvalue (`dict_ code`, `dict_ value`); ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

When you consult the document, you see the following explanation:

"For CHAR, VARCHAR, BINARY, and VARBINARY columns, 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 up to 1000 bytes long (767bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters." > > for myisam and innodb storage engines, the length limit of prefixes is 1000 bytes and 767 bytes, respectively. Note that the unit of prefix is bytes, but the unit of length we specified when building the table is characters. A utf8 character can use up to 3 bytes. Hence you cannot index columns or prefixes of columns longer than 333 (MyISAM) or 255 (InnoDB) utf8 characters. > > take the utf8 character set as an example, one character accounts for 3 bytes. Therefore, under the utf8 character set, the single column length for indexing myisam and innodb storage engines cannot exceed 333characters and 255characters.

Mysql index length limit:

1) single-column index:

Mysql has a limit on column length when creating a single-column index. Under the myisam and innodb storage engines, the length limits are 1000 bytes and 767 bytes, respectively. (note the difference between bytes and character)

2) combined index:

For the innodb storage engine, the length limit for multi-column indexes is as follows: the length of each column cannot be greater than 767 bytes; and the length of all constituent index columns cannot be greater than 3072 bytes

Smallint occupies 2 bytes,timestamp and 4 bytes,utf8 character set. Under the utf8 character set, one character occupies three byte.

There are two solutions to this problem:

1) modify the parameter innodb_large_prefix, which defaults to OFF and changes to ON

Mysql > show variables like 'innodb_large_prefix';+-+-+ | Variable_name | Value | +-+-+ | innodb_large_prefix | OFF | +-+-+

2) modify the field length

View the table structure:

Mysql > show create table ym_sys_dict\ int * 1. Row * * Table: ym_sys_dictCreate Table: CREATE TABLE `dictionary name` (`id` int (20) NOT NULL AUTO_INCREMENT, `dict_ name` NOT NULL COMMENT 'dictionary name' `dict_ type`varchar 'NOT NULL COMMENT' dictionary type', `dict_ code` varchar (100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `dict_ value` varchar (1000) DEFAULT NULL, `order_ num` int (11) DEFAULT'0' COMMENT 'sort', `remark` varchar 'DEFAULT' 'COMMENT' remarks', `del_ type`tinyint (4) DEFAULT'0' COMMENT 'delete tag-1: deleted 0: normal', PRIMARY KEY (`id`), UNIQUE KEY `dict_ type` (`dict_ type`) `dict_ code`) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COMMENT=' data dictionary table'

After communicating with the developer, the dict_value field length is set to too long, and the field length is changed to 100.

Alter table ym_sys_dict modify dict_value varchar (100)

Then you can add the index normally.

Mysql > ALTER TABLE ym_sys_dict ADD INDEX idx_dcode_dvalue (`dict_ code`, `dict_ value`); Query OK, 0 rows affected (0.01value`) Records: 0 Duplicates: 0 Warnings: 0 "how to solve the error problem of database ERROR 1071 (42000)". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.