Why am I getting MySQL Error #1071: Maximum Key Length Exceeded when creating a unique key?

MySQL Error #1071: Maximum Key Length Exceeded

When adding a unique key constraint to a MySQL table containing columns with VARCHAR data types, you may encounter the following error:

#1071 - Specified key was too long; max key length is 767 bytes

This error occurs even though the combined length of the VARCHAR columns appears to be less than the maximum key length, which is 767 bytes in MySQL versions 5.6 and prior.

Cause of the Error

The stated prefix limitation for InnoDB tables in MySQL versions 5.6 and below is 767 bytes. This means that the combined length of the key columns' prefixes cannot exceed this limit. For MyISAM tables, the max key length is 1,000 bytes.

In the provided example, the columns column1 and column2 have VARCHAR(20) and VARCHAR(500) data types respectively. This would imply a combined length of 522 bytes, leaving a comfortable margin below the 767-byte limit. However, the error message indicates that this is not the case.

Explanation

The reason for the error is that MySQL uses a variable-length multi-byte encoding for VARCHAR columns. This means that each character in a VARCHAR column can be represented by a varying number of bytes. For example, a VARCHAR(20) column can hold up to 20 characters, but each character can occupy up to 3 bytes if it is encoded in a wide-character format such as UTF-8mb4.

As a result, the actual length of the key is determined by the maximum possible length of the character strings that can be stored in each column. In this case, the maximum possible length of the character strings in column1 and column2 is 20 3 bytes = 60 bytes, and 500 3 bytes = 1500 bytes respectively. This gives a total of 60 1500 = 1560 bytes, which exceeds the maximum key length of 767 bytes.

Solutions

To resolve this issue, you can consider the following solutions:

• Reduce the maximum lengths of the VARCHAR columns. For example, you could change the data type of column1 to VARCHAR(15).
• Use a shorter key prefix. Instead of indexing the entire column2 field, you could use only a subset of it, such as column2(200).
• Use a different table engine that supports longer key lengths. MyISAM tables have a maximum key length of 1,000 bytes, which may be sufficient for your requirements.
• Upgrade to MySQL version 5.7 or later. MySQL 5.7 and later versions have a maximum key length of 3072 bytes, which provides more flexibility.

The above is the detailed content of Why am I getting MySQL Error #1071: Maximum Key Length Exceeded when creating a unique key?. For more information, please follow other related articles on the PHP Chinese website!