约定

操作系统：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的连续性具有强依赖，需要思考如何应对该情况。

顾乔芝士网

持续更新的前后端开发技术栈

不连续的自增id（不连续加不连续一定连续吗）

约定

现象

总结