MySQL Error #1071: How to Resolve 'Specified key was too long; max key length is 767 bytes'?

MySQL Error #1071: Understanding Key Length Constraints

While attempting to create a unique key for a database table, you encounter the following error:

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

The error message indicates that the specified key exceeds the maximum allowed length for InnoDB tables, which is 767 bytes. This behavior is often seen in earlier versions of MySQL, such as 5.6 and prior.

Bytes Calculations for VARCHAR Columns

You have columns column1 of type VARCHAR(20) and column2 of type VARCHAR(500). Initially, you might assume that the key would require 21 501 = 522 bytes, which is less than 767. However, this assumption is incorrect.

Understanding Prefix Length Limitations

The MySQL documentation states that the maximum prefix length for keys is 767 bytes. This length limit applies to the combined length of the columns included in the key. In this case, the total key length is 21 500 = 521 bytes, which is less than 767.

However, it's important to note that the prefix length limitation is actually dependent on the character encoding used by the columns. For non-Unicode columns like VARCHAR(20), each character requires one byte. However, for Unicode columns, such as VARCHAR(500) utf8_general_ci, each character can require multiple bytes.

Bytes Calculation for Unicode Columns

In this case, column2 is a Unicode column. The utf8_general_ci character set uses up to 3 bytes per character. Therefore, the actual key length for Unicode columns is calculated by multiplying the character length by the number of bytes per character:

20 bytes (column1) + (500 bytes * 3 bytes per character) = 1501 bytes

Solution: Shortening the Key Prefix

Since the key length exceeds the maximum limit, you can resolve the error by shortening the key prefix. This can be achieved by limiting the number of characters indexed for column2. For example:

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

This adjustment reduces the total key length to 21 (200 * 3) = 621 bytes, which now complies with the 767-byte prefix length limitation.

The above is the detailed content of MySQL Error #1071: How to Resolve 'Specified key was too long; max key length is 767 bytes'?. For more information, please follow other related articles on the PHP Chinese website!