When I insert some rows into tableA from tableB, it works as usual. The auto_increment key 'id' of inserted rows is normal, but the next auto_increment 'id' is an exception. In my case, the result of SQL indicated that total of rows affected is 5504 and the real rows inserted is 5504, but the next 'id' is 8192 that should be 5505.
my table struct as below:
-- ----------------------------
-- Table structure for t_ds
-- ----------------------------
DROP TABLE IF EXISTS `t_ds`;
CREATE TABLE `t_ds` (
`id` bigint(64) NOT NULL AUTO_INCREMENT,
`deviceId` varchar(50) DEFAULT NULL,
`eventId` varchar(50) DEFAULT NULL,
`eventTime` bigint(64) DEFAULT NULL,
`logInfoId` bigint(64) DEFAULT NULL,
`statusCode` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_ds` (`deviceId`,`eventId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and SQL insert statement:
INSERT INTO t_ds(deviceId, eventId, eventTime, logInfoId, statusCode)
SELECT deviceId, eventId, eventTime, id, statusCode FROM t_loginfo
WHERE (deviceId, eventId, eventTime) in (
SELECT
deviceId, eventId, MAX(eventTime)
FROM
t_loginfo
GROUP BY deviceId, eventId
)
GROUP BY deviceId, eventId;
I don't know why the increment key 'id' have been a weird num, so I do something try to find the reason. At first, I delete table 't_ds' , rebuild it and append sql 'limit *,1' to the above sql insert statement, insert the rows one by one, the weird num disappear, it's normal.
so I do some experiments for this.
1. delete table 't_ds' , rebuild it.
2. run the SQL insert statement with limit *,*.
result:
limit 0,1: insert 1 row, total rows is 1, the last id is 1, the next id is 2;
limit 1,2: insert 2 row, total rows is 3, the last id is 3, the next id is 5;
limit 3,3: insert 3 row, total rows is 6, the last id is 7, the next id is 8;
limit 6,4: insert 4 row, total rows is 10, the last id is 11, the next id is 15;
limit 10,5: insert 5 row, total rows is 15, the last id is 19, the next id is 22;
The sequence of id:
1 2 3 5 6 7 8 9 10 11 15 16 17 18 19
Another weird situation is whether the num of inserted rows is 5000+ or 6000+, both of two next id are 8192.
