Solution to "Specialized key was too long" in MySQL

When creating a table, I encountered an interesting problem. The message "Specified key was too long; max key length is 767 bytes" appeared. From the description, it seems that the key is too long and exceeds the specified limit of 767 bytes.

Below is the table structure that causes 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. Because utf8mb4 encoding is used, its size becomes 1000 * 4 > 767
Therefore, without changing other configurations, the length of varchar should be 767 / 4 = 191

Interested students can test it Specified key was too long; max key length is 767 bytes

Solution 1

• Using the innodb engine
• Enable the innodb_large_prefix option and modify the constraint to expand 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 reason for the above 3072 bytes is as follows

We know that the default size of an InnoDB page is 16k. Because it is a Btree organization, a page on a leaf node is required to contain at least two records (otherwise it will degenerate into a linked list).
Therefore, a record cannot exceed 8k at most. Due to the clustered index structure of InnoDB, a secondary index must include the primary key index, so each single index cannot exceed 4k (in extreme cases, both pk and a secondary index reach this limit).
Due to the need for reserved and auxiliary space, the number after deduction cannot exceed 3500, so the "integer" is (1024*3).

Solution 2

When creating 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 function of this parameter is as follows

MySQL indexes only support 767 bytes, and each character in utf8mb4 takes up 4 bytes, so the maximum length of the index can only be 191 characters, that is, varchar(191). If you want to use a larger field, MySQL needs to be set to support data compression and modify the table attribute row_format ={DYNAMIC|COMPRESSED}

This is the end of this article about the solution to the problem of MySQL index being too long. For more information about MySQL index being too long, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!