This is the sixth day of my participation in the November Gwen Challenge. See details: The Last Gwen Challenge 2021.

Specified key was too long; Max Key length is 767 bytes. The length of the key exceeds the specified 767 bytes limit

Here 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;
Copy the code

As you can see, for name, we set the length to 1000 variable characters, and since utF8MB4 is used, its size becomes 1000 * 4 > 767

Therefore, if no other configuration is modified, the length of the VARCHAR should be 767/4 = 191

Specified name: 192; Specified name: 191; Specified name: 192; max key length is 767 bytes

Solution 1

  • Use the InnoDB engine
  • Enable innodb_large_prefix option, modify constraint extension to3072 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;
Copy the code

The reason for the 3072 bytes above is as follows

We know that the default InnoDB page size is 16K. As a Btree organization, a page on a leaf node is required to contain at least two records (otherwise it degrades the linked list).

So a record can’t exceed 8K at most. In addition, due to InnoDB’s clustered index structure, a secondary index must contain a primary key index, so each single index cannot exceed 4K (in extreme cases, PK and a secondary index both reach this limit).

Due to the need for reserved and auxiliary space, the deduction cannot exceed 3500, an “integer” is (1024*3).

Solution 2

When creating the 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;
Copy the code

This parameter does the following

MySQL index only supports 767 bytes, utF8MB4 takes up 4 bytes per character, so the maximum length of 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 property row_format = {DYNAMIC | COMPRESSED}

II. The other

1. A gray Blog:liuyueyi.github.io/hexblog

A gray personal blog, recording all the study and work in the blog, welcome everyone to go to stroll

2. Statement

As far as the letter is not as good as, has been the content, purely one’s own words, because of the limited personal ability, it is hard to avoid omissions and mistakes, such as finding bugs or better suggestions, welcome criticism and correction, not grudging gratitude

  • Micro Blog address: Small Gray Blog
  • QQ: a gray /3302797840