Mysql8.0 new feature - persistence of self increasing variables

stay mysql8.0 Before , Since the primary key AUTO_INCREMENT If the value of is greater than max(primay key)+1, stay mysql After restart , Reset AUTO_INCREMENT=max(primay key)+1, In some cases, this phenomenon may lead to business primary key conflict or other difficult to find problems .

Let's take a brief look at... Through examples ;

One 、5.7 Version of mysql database

First of all we have 5.7 Of mysql Operation in database ;

1、 Create a new data table ;

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

mysql> 

2、 View table structure ;

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、 And then we add 3 Data ;

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、 Check the table data content after inserting data ;

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

mysql> 

5、 We delete id by 3 Data record of ;

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、 Insert a piece of data again ;

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> 

You can see in the mysql5.7 in , His self increase id, Turned into 4, Not using deleted 3 Of id;

7、 Delete what we just created id by 4 The data of ;

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、 Restart the database ;

9、 Insert a piece of data again ;

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

mysql> 

10、 View Auto increment id;

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

mysql> 

You can see that after the restart , The newly inserted data will be reset again AUTO_INCREMENT=max(primary key)+1. Because according to the previous test , It should be id by 5 That's right .

stay mysql5.7 In the system , For the allocation rule of self increasing primary key , By InnoDB The data dictionary is determined by a counter inside , This counter is maintained only in memory , It doesn't persist to disk . When the database restarts , The counter is initialized in the following way ;

select max(ai_col) from table_name for update;

Two 、 When we use mysql8.0 Version database

When using mysql8.0 Version of the database to test , It is found that the data in the database is the following ：

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

mysql> 

From the results of the above tests , since mysql8.0 Start , The self increasing variable has been persisted , This value will not be reset due to the restart of the database ;

mysql8.0 Persist the counter of the self incrementing primary key to the redo log . Every time the counter changes , Will be written to the redo log . If the database restarts ,InnoDB The memory value of the counter will be initialized according to the information in the redo log . In order to minimize the impact on system performance , When the counter writes to the redo log, it does not immediately refresh the database system .

thus , In this paper, the end .

For more information, go to VX official account “ Operation and maintenance home ” , Get the latest article .

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

------ “ Operation and maintenance home ” ------

Interview with system operation and maintenance engineer , Nominations for excellent staff of operation and maintenance engineer ,tr O & M Engineer , I'm calling the operation and maintenance engineer for daily work ,IT Senior operation and maintenance engineer ;
Intelligent manufacturing operation and maintenance engineer training course , Remote office operation and maintenance engineer , Salary of Mindray medical operation and maintenance engineer , What does the backstage O & M engineer do ;
How about the wind operation and maintenance engineer , Inspur cloud operation and maintenance engineer , Sample certificate of medical equipment operation and maintenance engineer , Boyfriend of operation and maintenance engineer , The O & M engineer is grumpy .