Shulou(Shulou.com)06/02 Report--

This article mainly introduces "why the self-increasing primary key in mysql is not continuous". In daily operation, I believe that many people have doubts about why the self-increasing primary key in mysql is not continuous. The editor consulted all kinds of data and sorted out a simple and easy-to-use operation method. I hope it will be helpful to answer the question of "why the self-increasing primary key in mysql is not continuous". Next, please follow the editor to study!

1 self-increasing primary key cannot guarantee continuous increment

Before version 2 MYSQL 5.7, the self-increment is stored in memory without persistence, and the current max (id) + 1 is used as the latest primary key after restart. In version 8, the self-increment changes are recorded in redo log, and the value before restart is restored by redo log.

3 self-value-added modification mechanism

3.1 if the id field is specified as zero zero null when inserting data, or if no value is specified, the current auto_ increment value of the table is added to the self-increment field

3.2 if the id field specifies a specific value, use the value specified in the statement directly

3.3 according to the relationship between the insertion value and the current self-increment, the result of the self-increment change will also be different. A secondary insertion value is X, and the current self-increment is Y.

1. If you are Xeroy, you should modify the current self-increment to a new self-increment.

The two important parameters are self-increasing initial value and step size. The default is 1.

Reason:

1 unique key conflict is the first cause of id discontinuity of self-increasing primary key

2 transaction rollback will also cause primary key id discontinuity

3 during batch insertion, the batch application for primary key id is not used up, resulting in discontinuous primary key id

Eg:

CREATE TABLE `t` (

`id`int (11) NOT NULL AUTO_INCREMENT

`c`int (11) DEFAULT NULL

`d`int (11) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `c` (`c`)

) ENGINE=InnoDB

Insert into t values (null, 1par 1)

Insert into t values (null, 2jue 2)

Insert into t values (null, 3pr. 3)

Insert into t values (null, 4pd4)

Create table t2 like t

Insert into T2 (cMagna d) select cpene d from t

Insert into T2 values (null, 5penny 5)

At this point, the study on "why the self-increasing primary keys in mysql are not continuous" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.