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

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

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

 

The actionstatistics table structure is defined as following:

So the length of above index key I'm trying to create 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 exactly greater than 3072 bytes.

 

You may notice when calculating the bytes length, we multiply 3 for the varchar type, that's because charset of actionstatistics table is UTF8, so every character will occupy 3 bytes.

 

Solution

One solution is to decrease length of each key. Now I'm using 255 as key length for each varchar column, but that's wasting of disk space. Since columns like userPhone, userName, etc. don't need occupy 255 bytes space.

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

 

Another solution is to increase this 3072 bytes max key length limit.