Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about the solution that the mysql index is too long and the private key is too long. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

An interesting problem was encountered when creating the table to be given, prompting Specified key was too long; max key length is 767 bytes. From the description, the key is too long and exceeds the specified limit of 767 bytes.

The following is the table structure that caused the problem

CREATE TABLE `test_ table` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (1000) NOT NULL DEFAULT'', `link`varchar (1000) NOT NULL DEFAULT'', PRIMARY KEY (`id`), KEY `name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

We can see that for name, we set the length to 1000 variable characters, and because of the utf8mb4 encoding, its size becomes 1000 * 4 > 767.

Therefore, without modifying other configurations, the length of varchar should be 767 / 4 = 191.

Interested students can test, respectively, specify the name size of 191,192, whether the former can create the table successfully, the latter create the table failed, and prompt the error Specified key was too long; max key length is 767 bytes

Solution one

Use the innodb engine

Enable the innodb_large_prefix option to modify the constraint and extend it to 3072 bytes

Recreate the database

My.cnf configuration

Set global innodb_large_prefix=on;set global innodb_file_per_table=on;set global innodb_file_format=BARRACUDA;set global innodb_file_format_max=BARRACUDA

The reasons for the above 3072 bytes are as follows

We know that the default size of an InnoDB page is 16k. Because it is a Btree organization, a page on the leaf node is required to contain at least two records (otherwise the linked list will be degraded).

So a record cannot exceed 8k at most. And because of InnoDB's clustered index structure, a secondary index should contain a primary key index, so each single index cannot exceed 4k (in extreme cases, competition and a secondary index reach this limit).

Due to the need to reserve and auxiliary space, after deduction can not exceed 3500, take an "integer" is (1024,3).

Solution two

When you create a table, add row_format=DYNAMIC

CREATE TABLE `test_ Table` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar (255) NOT NULL DEFAULT'', `link`varchar (255) NOT NULL DEFAULT'', PRIMARY KEY (`id`), KEY `name` (`name`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 row_format=DYNAMIC

The purpose of this parameter is as follows

The MySQL index only supports 767 bytes, and utf8mb4 occupies 4 bytes per character, so the maximum length of the index can only be 191 characters, that is, varchar (191). To use larger fields, mysql needs to be set to support data compression and modify the table attribute row_format = {DYNAMIC | COMPRESSED}

After reading the above, do you have any further understanding of the solution that the mysql index is too long and the private key is too long? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.