约定
- 操作系统：CentOS release 6.8（64位）
- 数据库版本：5.7.44
- 数据库引擎：InnoDB
现象
建表的时候一般会设置一个主键id，并让其自动增长。通常id都是具有连续性的，但是实践过程中数据入库异常时会导致id出现断层现象。
建立测试表：
mysql> create table test(id int not null auto_increment,col1 int not null,col2 varchar(10),primary key(id)) engine=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)插入一条数据：
mysql> insert into test values(null,1,'1');
Query OK, 1 row affected (0.00 sec)查看当前自增值：
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) NOT NULL,
`col2` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)当前值是2。
下面准备制造异常模拟现实情况：给col2添加唯一索引，并插入重复数据
添加唯一索引：
mysql> alter table test add unique index idx_col2(col2);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0再次添加重复col2列数据：
mysql> insert into test values(null,1,'1');
ERROR 1062 (23000): Duplicate entry '1' for key 'idx_col2'再次查看自增值：
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col1` int(11) NOT NULL,
`col2` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_col2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)发现它的值已经变成3了，这时我们插入一条正常的数据并查询记录，
mysql> insert into test values(null,1,'2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 1 | 1 |
| 3 | 1 | 2 |
+----+------+------+
2 rows in set (0.00 sec)总结
如果业务对主键id的连续性具有强依赖，需要思考如何应对该情况。