mysql8.0新特性-自增变量的持久化

在mysql8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primay key)+1，在mysql重启后，会重置AUTO_INCREMENT=max(primay key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

下面我们通过示例来简单看下；

一、5.7版本的mysql数据库

首先我们在5.7的mysql数据库中操作下；

1、新建一个数据表；

mysql> create table test_1 (id int auto_increment primary key, name varchar(50));
Query OK, 0 rows affected (0.00 sec)

mysql> 

2、查看表结构；

mysql> desc test_1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 

3、然后我们新增3条数据；

mysql> insert into test_1 (name) values ('zhangsan'), ('lisi'), ('wangwu');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

4、查看下插入数据之后的表数据内容；

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
+----+----------+
3 rows in set (0.00 sec)

mysql> 

5、我们删除id为3的数据记录；

mysql> delete from test_1 where id = '3';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> 

6、再次插入一条数据；

mysql> insert into test_1 (name) values ('zhaoliu');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  4 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)

mysql> 

可以看到在mysql5.7中，他的自增id，变成了4，并没有使用删除的3的id；

7、删除我们刚刚创建的id为4的那条数据；

mysql> delete from test_1 where id = '4';
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
+----+----------+
2 rows in set (0.00 sec)

mysql> 

8、重启数据库；

9、再次插入一条数据；

mysql> insert into test_1 (name) values ('xiaoqi');
Query OK, 1 row affected (0.00 sec)

mysql> 

10、查看自增id；

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | xiaoqi   |
+----+----------+
3 rows in set (0.00 sec)

mysql> 

可以看到重启之后，新插入的数据会再次重置AUTO_INCREMENT=max(primary key)+1。因为按照之前的测试来说，应该是出现id为5的数据才对。

在mysql5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会通过下面这种方式初始化；

select max(ai_col) from table_name for update;

二、当我们使用mysql8.0版本的数据库

当使用mysql8.0版本的数据库来测试之后，发现最后数据库中数据为下：

mysql> select * from test_1;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  5 | xiaoqi   |
+----+----------+
3 rows in set (0.00 sec)

mysql> 

从上面测试的结果来看，自mysql8.0开始，已经将自增变量持久化了，并不会由于数据库的重启而重置该值；

mysql8.0将自增主键的计数器持久化到重做日志中。每次计数器发生变化，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入到重做日志时并不会马上刷新数据库系统。

至此，本文结束。

更多内容请转至VX公众号 “运维家” ，获取最新文章。

------ “运维家” ------

------ “运维家” ------

------ “运维家” ------

系统运维工程师面试，运维工程师优秀员工提名词，tr运维工程师，特来电运维工程师工作日常，IT运维工程师高级；
智能制造运维工程师培训课程，远程办公的运维工程师，迈瑞医疗运维工程师工资待遇，后台运维工程师是做什么的；
风力运维工程师怎样，浪潮云运维工程师，医疗设备运维工程师证书样本，运维工程师男朋友，运维工程师暴躁。