Specified key was too long; max key length is 3072 bytes

 

When running above statement to add unique index, following error happened.

[2018-03-28 11:38:24] [42000][1071] Specified key was too long; max key length is 3072 bytes

 

Table structure

So the above index key length is value(255*3), productId(4), productName(255*3), productSerialNumber(255*3), userPhone(255*3), userName(255*3), userId(8), type(255*3), statStartTime(8), statEndTime(8)

Total length is 255*6*3 + 4 + 8*3=4618 is greater than 3072 bytes.

 

Note that the charset used is UTF8, so every character will occupy 3 bytes.

 

Solution

Then change to following statement, and the error is fixed.

 

Another solution is to increase this 3072 bytes limit.