mysql 表复制含自增_Mysql Insert into进行表之间数据复制的时候出现自增错误

表的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