Summary of some small problems about MySQL self increasing ID

The following small problems are all based on InnoDB storage engine.

1. What is the newly inserted record ID after the largest record of ID is deleted

For example, if there are 1, 2, 33 ID records in the current table, delete 3. Where does the ID of the newly inserted record start?

Answer: Start at 4.

Experiment

Create the table tb0, ID self-increment:

create table tb0(id int unsigned auto_increment primary key);

Insert 3 records:

insert into tb0 values(null);

Delete records with ID of 3:

delete from tb0 where id=3

View the current self-added value:

show create table tb0;

#  Results 
CREATE TABLE `tb0` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Self-increasing ID is 4, and deleting the record with the largest ID does not affect the value of self-increasing ID.

2. Where does the self-increasing ID start after the restart of MySQL

For example, if there are 1, 2 and 33 ID records in the current table, delete 3 and restart MySQL. Where does ID of newly inserted records start?

Many people will think that starting from 4 is actually starting from 3.

Because InnoDB's self-increment is recorded in memory, not in data files.

After rebooting, the current maximum ID + 1 is taken as the starting value.

Experiment

Create table tb1, ID self-increment:

create table tb1(id int unsigned auto_increment primary key);

Add 3 data records:

insert into tb1 values(null);

Delete records with ID 3:

delete from tb1 where id=3

From the previous question, we know that the self-increasing ID value is 4 at this time.

Restart MySQL.

View the current self-added value:

show create table tb1;

#  Results 
CREATE TABLE `tb1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

3. After inserting ID manually, what is the self-increment when inserting it next time

For example, the current self-increasing ID is 4, when a new record is inserted, the ID is manually specified as 10, and the next time a self-increasing insertion is used, the ID is 11.

ID self-increment = current maximum ID + 1

When a new record is inserted, the new ID value has been calculated

Experiment

Create table tb2, ID self-increment:

create table tb2(id int unsigned auto_increment primary key);

Add a record:

insert into tb2 values(null);

Manually specify ID:

insert into tb0 values(null);

0

View the current self-added value:

insert into tb0 values(null);

1

You can see that the self-increment becomes 4294967001.

4. What to do after the value-added is used up

The maximum value of unsigned int is 4294967295. After the increment reaches this value, it remains unchanged and an error will be reported when a new record is inserted:

Duplicate entry '4294967295' for key 'PRIMARY'

If table records are frequently inserted and deleted, ID may run out quickly even if the total number of records in the table is not very large, which may require bigint.

int value range:

int is 4 byte, and the first bit is used to indicate the symbol

Signed range:
From-to-1

(-2147483648 to 2147483647)

Unsigned range:
0 to-1

(0 to 4294967295)

bigint value range:

int is 8 byte

Signed range:
From-to-1

(-9223372036854775808 to 9223372036854775807)

Unsigned range:
0 to-1

(0 to 18446744073709551615)

Summary

Through experiments, we can find some characteristics of self-increasing ID in InnoDB:

When a new record is inserted, a new self-increment (ID+1 max) is calculated, either using an automatic ID or manually specifying one ID.

Deleting the maximum ID value has no effect on the self-increasing ID value, but it has effect after the restart of MySQL. Instead of using the previous self-increasing ID value, the maximum ID+1 is used, because the self-increasing ID value exists in memory and needs to be recalculated after restart.

Since the increase ID is used up, it will remain unchanged.

Summarize