Specified key was too long; max key length is 767 bytes 有大用

When I executed the following command:

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

I got this error message:

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

Information about column1 and column2:

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

I think varchar(20) only requires 21 bytes while varchar(500) only requires 501 bytes. So the total bytes are 522, less than 767. So why did I get the error message?

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

正确答案
767 bytes is the stated prefix limitation for InnoDB tables  767 bytes  - its   1,000 bytes long for MyISAM tables.

According to the response to this issue, you can get the key to apply by specifying a subset of the column rather than the entire amount. IE:

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

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.

来自 http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes