Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Summary of some minor problems about self-increasing ID of MySQL

2025-01-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

The following minor issues are based on the InnoDB storage engine.

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

For example, there are three records in the current table where ID is 1, 2, and 3. If you delete 3, where does the ID of the newly inserted record start?

Answer: start at 4.

Experiment

Create table tb0,ID self-increment:

Create table tb0 (id int unsigned auto_increment primary key)

Insert 3 records:

Insert into tb0 values (null)

Delete a record with an ID of 3:

Delete from tb0 where id=3

View the current self-added value:

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

When the ID is increased to 4, deleting the record with the largest ID does not affect the value of the self-incremented ID.

2. Where does the added ID start after MySQL restart

For example, there are three records in the current table where ID is 1, 2, and 3, delete 3, restart MySQL, where does the ID of the newly inserted record start?

Many people will think that it starts with 4, but actually starts with 3.

Because the self-increment of InnoDB is recorded in memory, not in data files.

After reboot, the current maximum ID + 1 will be 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 a record with an ID of 3:

Delete from tb1 where id=3

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

Restart MySQL.

View current self-increment:

Show create table tb1;# result CREATE 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 ID, what is the self-increment next time you insert it?

For example, the current self-incrementing ID is 4, when you insert a new record, you manually specify ID as 10, and the next time you insert a record with self-increment, 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)

Specify ID manually:

Insert into tb2 values (4294967000)

View current self-increment:

Show create table tb2;# result CREATE 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 self-increment becomes 4294967001.

4. What should we do after the added value runs out?

The maximum value of unsigned int is 4294967295, which remains unchanged since the increment reaches this value, 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 be used up quickly, even if the total number of records in the table is not very large, which may require the use of bigint.

Value range of int:

Int is 4 byte, and the first bit is used to represent symbols

Signed range:

From-to-1

(- 2147483648 to 2147483647)

Unsigned range:

0 to-1

(0 to 4294967295)

Value range of bigint:

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 you insert a new record, the new self-increment (maximum ID+1) is calculated, whether using automatic ID or manually specifying an ID.

Deleting the maximum ID value has no effect on the self-increment ID value, but it will do so after the MySQL restart. The previous self-increment ID value will not be used, but the maximum ID+1 value will be used, because the self-increment ID value is stored in memory and needs to be recalculated after restart.

It hasn't changed since the added ID has been used up.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.

Views: 254

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report