Why Does MySQL Throw Error #1071: 'Specified key was too long'?

MySQL Error #1071: Delving into Key Length Constraints

When attempting to modify a MySQL table by adding a unique key constraint on two VARCHAR columns, a rather perplexing error message arises: "#1071 - Specified key was too long; max key length is 767 bytes". Despite both columns having sizes that appear to be well within the 767 bytes limit, understanding this error requires a deeper dive into MySQL's key length constraints.

Revisiting Key Length Limits

The maximum length for a key in MySQL version 5.6 and earlier is indeed 767 bytes for InnoDB tables and 1,000 bytes for MyISAM tables. However, in MySQL version 5.7 and above, this limit has been expanded to a much larger 3072 bytes.

Decoding VARCHAR Storage

While the VARCHAR data type suggests a maximum storage of 20 and 500 bytes for your respective columns, this calculation does not account for character encoding. If your VARCHAR fields are encoded in utf8mb4 format, the maximum index prefix length is effectively reduced by one-fourth, resulting in only 191 bytes (767 / 4).

Finding a Workaround

To resolve this issue, consider implementing one of the following strategies:

• Reduce Column Sizes: Limit the maximum length of your VARCHAR fields to meet the index prefix length constraint.
• Subset the Column: Utilize only a portion of the column for indexing, thereby reducing the key length. For instance: ALTER TABLE mytable ADD UNIQUE ( column1(15), column2(200) );
• Review Data Model: Re-evaluate your table design and consider if there are alternative approaches that can avoid encountering this MySQL limitation.

The above is the detailed content of Why Does MySQL Throw Error #1071: 'Specified key was too long'?. For more information, please follow other related articles on the PHP Chinese website!