MySQL社区

 找回密码
 注册

QQ登录

只需一步,快速开始

扫一扫,访问微社区

搜索
查看: 2087|回复: 1

[SQL类] Mysql Insert into进行表之间数据复制的时候出现自增错误

[复制链接]
发表于 2016-9-5 10:22:15 | 显示全部楼层 |阅读模式
表的SQL结构
  1. -- ----------------------------
  2. -- Table structure for t_ds
  3. -- ----------------------------
  4. DROP TABLE IF EXISTS `t_ds`;
  5. CREATE TABLE `t_ds` (
  6.   `id` bigint(64) NOT NULL AUTO_INCREMENT,
  7.   `deviceId` varchar(50) DEFAULT NULL,
  8.   `eventId` varchar(50) DEFAULT NULL,
  9.   `eventTime` bigint(64) DEFAULT NULL,
  10.   `logInfoId` bigint(64) DEFAULT NULL,
  11.   `statusCode` tinyint(1) DEFAULT NULL,
  12.   PRIMARY KEY (`id`),
  13.   UNIQUE KEY `index_ds` (`deviceId`,`eventId`)
  14. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
复制代码
插入语句
  1. INSERT INTO t_ds(deviceId, eventId, eventTime, logInfoId, statusCode)
  2. SELECT deviceId, eventId, eventTime, id, statusCode FROM t_loginfo
  3. WHERE (deviceId, eventId, eventTime) in (
  4.   SELECT
  5.                 deviceId, eventId, MAX(eventTime)
  6.         FROM
  7.                 t_loginfo
  8.         GROUP BY deviceId, eventId
  9. )
复制代码
执行结果
  1. [SQL]INSERT INTO t_ds(deviceId, eventId, eventTime, logInfoId, statusCode)
  2. SELECT deviceId, eventId, eventTime, id, statusCode FROM t_loginfo
  3. WHERE (deviceId, eventId, eventTime) in (
  4.   SELECT
  5.                 deviceId, eventId, MAX(eventTime)
  6.         FROM
  7.                 t_loginfo
  8.         GROUP BY deviceId, eventId
  9. )

  10. 受影响的行: 5504
  11. 时间: 2.882s
复制代码
查看数据库发现插入成功,一共5504条记录,所有行的序列ID也是正常,但是这时候的自增已经到了8192, 这时候插入一条新纪录的ID为8192。不知道为啥出现了这样的问题,加个 limit x,1 的话 insert 一条一条的插入又不会出现错误,但是这样把多条查询结果一起插进去的话就会出现。我做了一些小测试,不知道是否有用。
我把表清空,自增恢复到1,然后运行下面语句
  1. INSERT INTO t_ds(deviceId, eventId, eventTime, logInfoId, statusCode)
  2. SELECT deviceId, eventId, eventTime, id, statusCode FROM t_loginfo
  3. WHERE (deviceId, eventId, eventTime) in (
  4.   SELECT
  5.                 deviceId, eventId, MAX(eventTime)
  6.         FROM
  7.                 t_loginfo
  8.         GROUP BY deviceId, eventId
  9. )
  10. limit 0,1
复制代码

这时候 AUTO_INCREMENT=2,
再修改为 limit 1,2,这时候插入2条,实际记录为3条,最后一条记录ID为3,但是 AUTO_INCREMENT=5
再修改为 limit 3,3,这时候插入3条,实际记录为6条,最后一条记录ID为7,但是 AUTO_INCREMENT=8
再修改为 limit 6,4,这时候插入4条,实际记录为10条,最后一条记录ID为11,但是 AUTO_INCREMENT=15
再修改为 limit 10,5,这时候插入5条,实际记录为15条,最后一条记录ID为19,但是 AUTO_INCREMENT=22
运行下来整个ID自增序列是这样的
  1. 1 2 3 5 6 7 8 9 10 11 15 16 17 18 19
复制代码
另外,在插入数量大的时候,一次插入5000条和一次插入6000条,AUTO_INCREMENT竟然都是8192,感觉好诡异了

发表于 2017-4-6 17:52:55 | 显示全部楼层
这是正常的,默认的innodb自增字段值是预分配的,通过记录数量决定是分配2的N次值
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|申请友链|小黑屋|Archiver|手机版|MySQL社区 ( 京ICP备07012489号   
联系人:周生; 联系电话:13911732319

GMT+8, 2024-3-29 02:23 , Processed in 0.066486 second(s), 24 queries , Gzip On.

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表