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