MySQL database auto_increment self-increment backtracking
This article introduces the relevant knowledge of "MySQL database auto_increment self-value-added backtracking". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
# create about table t, where field an is self-increasing as the primary key
Mysql > create table t (a bigint primary key auto_increment, b tinyint)
Query OK, 0 rows affected (0.03 sec)
# insert some data
Mysql > insert into t select null, 10
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Mysql > insert into t select null, 20
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Mysql > insert into t select null, 30
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Mysql > insert into t select null, 40
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
# View table records
Mysql > select * from t
+-+ +
| | a | b | |
+-+ +
| | 1 | 10 |
| | 2 | 20 |
| | 3 | 30 |
| | 4 | 40 |
+-+ +
4 rows in set (0.00 sec)
# Delete the last piece of data
Mysql > delete from t where axiom 4
Query OK, 1 row affected (0.02 sec)
# View the table creation statement and find AUTO_INCREMENT=5
Mysql > show create table t\ G
* * 1. Row *
Table: t
Create Table: CREATE TABLE `t` (
`a`bigint (20) NOT NULL AUTO_INCREMENT
`b` tinyint (4) DEFAULT NULL
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# perform primary key backtracking simulation
# restart the database
[root@mysql ~] # service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
# re-look at the table creation statement and find AUTO_INCREMENT=4
Mysql > show create table t\ G
* * 1. Row *
Table: t
Create Table: CREATE TABLE `t` (
`a`bigint (20) NOT NULL AUTO_INCREMENT
`b` tinyint (4) DEFAULT NULL
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
# continue inserting statements
Mysql > insert into t select null, 50
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
# check the data of the table and find that the above self-increasing ID=4 appears again
Mysql > select * from t
+-+ +
| | a | b | |
+-+ +
| | 1 | 10 |
| | 2 | 20 |
| | 3 | 30 |
| | 4 | 50 |
+-+ +
4 rows in set (0.00 sec)
This is because the AUTO_INCREMENT of the table in MySQL5.7 is memory-based and does not persist on disk, and each table will be max (auto_increment) + 1 again as the self-increment of the table's next primary key ID each time the database is started. This problem does not occur in MySQL8.0 because the data is persisted on disk.
This is the end of the introduction of "MySQL database auto_increment self-value-added backtracking". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
