表的SQL结构

-- ----------------------------
-- 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 AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入语句

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
)

执行结果

[SQL]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
)

受影响的行: 5504
时间: 2.882s

查看数据库发现插入成功，一共5504条记录，所有行的序列ID也是正常，但是这时候的自增已经到了8192， 这时候插入一条新纪录的ID为8192