1. After the record with the largest ID is deleted, what is the ID of the newly inserted record

For example, if there are three records (ID 1,2, and 3) in the current table, delete 3. Where do I start with the ID of the newly inserted record?

Answer: Start at 4.

The experiment

Create table tb0 where ID = 0;

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

Insert 3 records:

insert into tb0 values(null);Copy the code

Delete the record whose ID is 3:

delete from tb0 where id=3Copy the code

To view the current self-value-added:

show create table tb0;

# the results
CREATE TABLE `tb0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1Copy the code

If the increment ID is 4, deleting the record with the largest ID does not affect the value of the increment ID.

2. Where do I start from after MySQL restarts

MySQL > insert ID (1,2, and 3);

A lot of people think we’re starting at 4, but we’re actually starting at 3.

Because InnoDB’s increments are recorded in memory, not in data files.

After the restart, the current maximum ID + 1 is used as the starting value.

The experiment

Create table tb1 where ID = 0;

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

Add 3 data records:

insert into tb1 values(null);Copy the code

Delete the record whose ID is 3:

delete from tb1 where id=3Copy the code

From the last question, we know that the increment ID is 4.

Restart the MySQL.

To view the current self-increment:

show create table tb1;

# the results
CREATE TABLE `tb1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1Copy the code

3. After the ID is manually inserted, what is the value of auto-increment during the next insertion

For example, if the current auto-add ID is 4, manually set the ID to 10 when you insert a new record. The next time you insert a record in auto-add mode, the ID is 11.

ID Auto-increment = Current maximum ID + 1

The new ID value is calculated when the new record is inserted

The experiment

Create table tb2 where ID = 0;

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

Add a record:

insert into tb2 values(null);Copy the code

Manually specify ID:

insert into tb2 values(4294967000);Copy the code

To view the current self-increment:

show create table tb2;

# the results
CREATE TABLE `tb2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967001 DEFAULT CHARSET=latin1Copy the code

You can see that the value increases to 4294967001.

4. What should I do after I run out of self-value-added

The maximum value of an unsigned int is 4294967295. If the value of an unsigned int is 4294967295, the value will not change.

Duplicate entry ‘4294967295’ for key ‘PRIMARY’

If a table is frequently inserted or deleted, ids may run out quickly, even if the total number of records in the table is not very large. In this case, you can use BigINT.

Int Value range:

Int is 4 bytes, and the first byte is used for symbols

Signed range: – to – 1

(-2147483648 to 2147483647)

Unsigned range: 0 to -1

(0 to 4294967295)

Bigint Value range:

Int is 8 byte

Signed range: – to – 1

(-9223372036854775808 to 9223372036854775807)

Unsigned range: 0 to -1

(0 to 18446744073709551615)

summary

Through experiments, we can find some features of InnoDB auto-increment ID:

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

Deleting the maximum ID has no effect on the self-increasing ID value. However, after MySQL restarts, the system does not use the original self-increasing ID value. Instead, the system uses the maximum ID+1 because the self-increasing ID value exists in the memory and needs to be recalculated after the restart.

The increment ID will not change after it runs out.

Article published simultaneously: www.geek-share.com/detail/2785…