Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.