|
1 |
alter table actionstatistics add unique(value, productId, productName, productSerialNumber, userPhone, userName, userId, type, statStartTime, statEndTime); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE <code>paymentstat</code> ( id</code> bigint(20) NOT NULL AUTO_INCREMENT, created</code> datetime DEFAULT NULL, isCalculatedReward</code> bit(1) NOT NULL, productId</code> int(11) DEFAULT NULL, productName</code> varchar(255) DEFAULT NULL, productSerialNumber</code> varchar(255) DEFAULT NULL, statEndTime</code> datetime DEFAULT NULL, statStartTime</code> datetime DEFAULT NULL, type</code> varchar(255) DEFAULT NULL, userId</code> bigint(20) DEFAULT NULL, userName</code> varchar(255) DEFAULT NULL, userPhone</code> varchar(255) DEFAULT NULL, value</code> varchar(255) DEFAULT NULL, valueDouble</code> decimal(20,2) DEFAULT NULL, actionStatisticsExpression_id</code> bigint(20) DEFAULT NULL, user_id</code> bigint(20) DEFAULT NULL, PRIMARY KEY (<code>id</code>), KEY <code>FK_d7fu0dasg9vq3w21qtvr6227o</code> (<code>actionStatisticsExpression_id</code>), KEY <code>FK_r852hjlpseh3ue8wd8gugd14d</code> (<code>user_id</code>), KEY <code>phone_idx</code> (<code>userPhone</code>) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
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.
|
1 |
alter table actionstatistics add unique(value, productId, productName(10), productSerialNumber(20), userPhone(12), userName(20), userId, type(10), statStartTime, statEndTime); |
Another solution is to increase this 3072 bytes max key length limit.