merphix(CSDN UID:u014567183) 给博主提供了莫大的帮助
问题描述
事情是这样的,博主用 Mybatis 往 mysql 做批量插入,结果发现特定的 SQL 语句导致 自增int型主键不连续,多方求证便有了这篇博文。
还原问题
建表语句
CREATETABLE versionfileinfo (
idINTNOTNULLPRIMARYKEY auto_increment,
vidINTNOTNULL COMMENT'版本信息id' ,
buildNoINTNOTNULL COMMENT'版本号' ,
displayVersionVARCHAR (50)NOTNULL COMMENT'显示版本' ,
fileKeyVARCHAR (100)NOTNULL COMMENT'文件标识' ,
fileSize BIGINTNOTNULL COMMENT'文件大小' ,
fileUrlVARCHAR (1000)NOTNULL COMMENT'文件存放url' ,
descriptionVARCHAR (500) COMMENT'描述' ,
checkTypeVARCHAR (50) COMMENT'校验方式' ,
checkCodeVARCHAR (500) COMMENT'校验码' ,
createTime BIGINTNOTNULL COMMENT'秒级时间戳' ,
lastUpdateTime BIGINTNOTNULL COMMENT'秒级时间戳' ,
isActivebitNOTNULL COMMENT'删除标识' ,
ts BIGINTNOTNULL COMMENT'毫秒级时间戳'
) ENGINE = INNODBDEFAULT CHARSET utf8;
批量插入方案一
INSERTINTO`versionfileinfo` (
vid,
buildNo,
displayVersion,
fileKey,
fileSize,
fileUrl,
description,
checkType,
checkCode,
createTime,
lastUpdateTime,
isActive,
ts
)(SELECT1,2,'显示版本',NULL,NULL,'www.asdasd.com','描述',"a","b",123,123123,1,123123
)UNION
(SELECT1,2,'显示版本2',NULL,NULL,'www.asdasd.com','描述',"a","b",123,123123,1,123123
)
批量插入方案二
INSERTINTO`versionfileinfo` (
vid,
buildNo,
displayVersion,
fileKey,
fileSize,
fileUrl,
description,
checkType,
checkCode,
createTime,
lastUpdateTime,
isActive,
ts
)VALUES
(1,1,'显示版本',NULL,NULL,'www.asdasd.com','描述',"a","b",123,123123,1,123123
),
(1,2,'显示版本',NULL,NULL,'www.asdasd.com','描述',"a","b",123,123123,1,123123
)
结果发现方案一会导致自增id不连续,二方案二id是连续的
举例:id为自增主键
先清空数据表
上面提供的批量插入sql语句一次插入2条记录
第一次运行 第一条id =1 ,第二条id=2
第二次运行 第三条id =4 ,第四条id=5
第三次运行 第五条id =7 ,第六条id=8
问题实质
和 MySQL 的 innodb 数据库引擎相关,据说是 MyISAM 引擎 不会有这种问题
解决方案
- 换掉 innodb 数据库引擎
- 用上提到的方案二SQL
- 配置 innodb_autoinc_lock_mode 参数