往mysql中插入 自增id不连续（曾经的坑）

merphix（CSDN UID：u014567183） 给博主提供了莫大的帮助

问题描述

事情是这样的，博主用 Mybatis 往 mysql 做批量插入，结果发现特定的 SQL 语句导致 自增int型主键不连续，多方求证便有了这篇博文。

还原问题

建表语句

CREATE TABLE versionfileinfo (  id INT NOT NULL PRIMARY KEY auto_increment,  vid INT NOT NULL COMMENT '版本信息id' ,  buildNo INT NOT NULL COMMENT '版本号' ,  displayVersion VARCHAR (50) NOT NULL COMMENT '显示版本' ,  fileKey VARCHAR (100) NOT NULL COMMENT '文件标识' ,  fileSize BIGINT NOT NULL COMMENT '文件大小' ,  fileUrl VARCHAR (1000) NOT NULL COMMENT '文件存放url' ,  description VARCHAR (500) COMMENT '描述' ,  checkType VARCHAR (50) COMMENT '校验方式' ,  checkCode VARCHAR (500) COMMENT '校验码' ,  createTime BIGINT NOT NULL COMMENT '秒级时间戳' ,  lastUpdateTime BIGINT NOT NULL COMMENT '秒级时间戳' ,  isActive bit NOT NULL COMMENT '删除标识' ,  ts BIGINT NOT NULL COMMENT '毫秒级时间戳') ENGINE = INNODB DEFAULT CHARSET utf8;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

批量插入方案一

INSERT INTO `versionfileinfo` (    vid,    buildNo,    displayVersion,    fileKey,    fileSize,    fileUrl,    description,    checkType,    checkCode,    createTime,    lastUpdateTime,    isActive,    ts)(    SELECT        1,        2,        '显示版本',        NULL,        NULL,        'www.asdasd.com',        '描述',        "a",        "b",        123,        123123,        1,        123123)UNION    (        SELECT            1,            2,            '显示版本2',            NULL,            NULL,            'www.asdasd.com',            '描述',            "a",            "b",            123,            123123,            1,            123123    )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

批量插入方案二

INSERT INTO `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    )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

结果发现方案一会导致自增id不连续,二方案二id是连续的 
举例：id为自增主键 
先清空数据表 
上面提供的批量插入sql语句一次插入2条记录 
第一次运行 第一条id =1 ，第二条id=2 
第二次运行 第三条id =4 ，第四条id=5 
第三次运行 第五条id =7 ，第六条id=8

问题实质

和 MySQL 的 innodb 数据库引擎相关，据说是 MyISAM 引擎 不会有这种问题

innodb 自增列 锁机制简述

解决方案

1. 换掉 innodb 数据库引擎
2. 用上提到的方案二SQL
3. 配置 innodb_autoinc_lock_mode 参数

MySQL 官方 innodb_autoinc_lock_mode 文档

innodb_autoinc_lock_mode 类型简述

innodb_autoinc_lock_mode 配置方法

转载自http://blog.csdn.net/u014430366/article/details/70267601?utm_source=itdadao&utm_medium=referral