Summary of some small issues about MySQL auto-increment ID

The following questions are all based on the InnoDB storage engine.

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 with IDs 1, 2, and 3 in the current table, and 3 is deleted, where do the IDs of the newly inserted records start?

Answer: Start from 4.

experiment

Create table tb0 with auto-increment ID:

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

Insert 3 records:

insert into tb0 values(null);

Delete the record with ID 3:

delete from tb0 where id=3

View the current auto-increment value:

show create table tb0;

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

The auto-increment ID is 4, and deleting the record with the largest ID does not affect the value of the auto-increment ID.

2. Where does the auto-increment ID start after MySQL restarts?

For example, if there are three records with IDs 1, 2, and 3 in the current table, delete 3, restart MySQL, where do the IDs of the newly inserted records start?

Many people think it starts from 4, but it actually starts from 3.

Because InnoDB's auto-increment value is recorded in memory, not in the data file.

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

experiment

Create table tb1 with auto-increment ID:

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

Add 3 data records:

insert into tb1 values(null);

Delete the record with ID 3:

delete from tb1 where id=3

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

Restart MySQL.

View the current auto-increment value:

show create table tb1;

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

3. After manually inserting the ID, what is the auto-increment value the next time you insert it?

For example, if the current auto-increment ID is 4 and you manually specify the ID as 10 when inserting a new record, the next time you insert using the auto-increment method, the ID will be 11.

ID auto-increment = current maximum ID + 1

When inserting a new record, the new ID value is calculated

experiment

Create table tb2 with auto-increment ID:

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

Add a record:

insert into tb2 values(null);

Manually specify the ID:

insert into tb2 values(4294967000);

View the current auto-increment value:

show create table tb2;

# ResultCREATE TABLE `tb2` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967001 DEFAULT CHARSET=latin1

You can see that the auto-increment value becomes 4294967001.

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

The maximum value of an unsigned int is 4294967295. When the auto-increment reaches this value, it will not change, 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, IDs may be used up quickly even if the total number of records in the table is not large. In this case, bigint may be needed.

int value range:

int is 4 bytes, the first digit is used to indicate the sign

Signed range:
From - To - 1

(-2147483648 to 2147483647)

Unsigned range:
0 to -1

(0 to 4294967295)

bigint value range:

int is 8 bytes

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 the self-increment ID in InnoDB:

When inserting a new record, a new auto-increment value (maximum ID + 1) is calculated, regardless of whether automatic ID is used or an ID is manually specified.

Deleting the maximum ID value has no effect on the auto-increment ID value, but it will have an impact after MySQL restarts. The previous auto-increment ID value will not be used, but the maximum ID+1 will be used. This is because the auto-increment ID value is stored in memory and needs to be recalculated after restarting.

The auto-increment ID will not change after it is used up.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.