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

This article introduces the knowledge of "is the self-increasing primary key of mysql continuous?". In the operation of actual cases, many people will encounter such a dilemma. Next, 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!

Catalogue

A preface

Description of self-value-added storage

Three self-value-added modification mechanism

4. Timing of self-value-added modification

Fifth, the causes of self-increment discontinuity.

5.1 unique key conflict

5.2 transaction rollback

5.3 batch write library operation

Six reference documents

A preface

This problem is raised because it is found that the id of the user table in mysql is self-increasing by default, but the results stored in the database are not contiguous.

User structure:

CREATE TABLE `user` (`id` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'increment id', `name` varchar (20), `create_ time`creation time', `update_ time`datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'update time', PRIMARY KEY (`id`), UNIQUE KEY `idx_ name` (`name`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user table'

User table storage:

Description of self-value-added storage

1.1 the self-increment of the MyISAM engine is saved in the data file.

1.2 the self-increment of the InnoDB engine is actually stored in memory, and it is not until MySQL version 8.0 that it has the capability of "self-increment persistence", that is, "if a restart occurs, the self-increment of the table can be restored to the value before MySQL restart". The details are as follows:

In MySQL version 5. 7 and earlier, self-increment is kept in memory. After each restart, when the table is opened for the first time, it will find the maximum self-added value max (id), and then take max (id) + 1 as the current self-increment of the table.

In MySQL version 8.0, self-increment changes are recorded in redo log, and the restart relies on redo log to restore the pre-restart values.

Three self-value-added modification mechanism

In MySQL, if the field id is defined as AUTO_INCREMENT, the self-increment behavior is as follows when inserting a row of data:

If the id field is specified as 0, null, or no value when inserting data, fill in the current AUTO_INCREMENT value of the table to the self-increment field

If the id field specifies a specific value when you insert the data, use the value specified in the statement directly.

The result of the self-increment change will be different depending on the value to be inserted and the size of the current self-increment. Suppose that the value of a minor insert is X and the current self-increment is Y.

If X

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.