Why Is My MySQL Unique Index Too Long, and How Can I Fix It?

MySQL Key Length Limitation and its Fix

When attempting to create a unique index on two VARCHAR columns, users may encounter the error "#1071 - Specified key was too long; max key length is 767 bytes". This error stems from MySQL's limitations on the length of keys.

Understanding the Key Length Limit

In MySQL versions 5.6 and prior, there is a maximum key length of 767 bytes for InnoDB tables. This limit applies to the combination of all indexed fields within a unique key. For MyISAM tables, this limit is 1,000 bytes.

UTF-8 Character Encoding Factor

When using UTF-8 character encoding (utf8mb4), it's important to note that it uses up to four bytes to represent a single character. As a result, the max index prefix length of 767 bytes is effectively reduced to 191 characters for utf8mb4 encoded fields.

Possible Solutions

• Reduce VARCHAR field size: Lowering the size of the VARCHAR fields can reduce the total key length.
• Partition the index: Create a subset of the column to be indexed, as seen in the example:

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

• Review data model: It may be beneficial to review the data model to implement business rules without exceeding MySQL's limitations.

In MySQL version 5.7 and later, the max key length has been increased to 3072 bytes, providing more flexibility when dealing with large keys. However, it's still important to consider character encoding factors and data model optimization to avoid key length errors.

The above is the detailed content of Why Is My MySQL Unique Index Too Long, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!