Problems encountered when updating the auto-increment primary key id in Mysql

This is a problem I know about, but I almost fell into the trap (I almost forgot it, but fortunately I remembered it when I sorted out the online points before going online), so I specially recorded it here

Why update the auto-increment id?

I had to update a batch of IDs because of historical business problems, and in order to avoid conflicts, I needed to expand the IDs by several times for update. Because the amount of data in my table is not high, it is a high-read and low-write situation, so I simply expanded it by 1000.

question

In MySQL, if we update the auto-increment primary key to a larger value (for example, the maximum value of the auto-increment id is 1000, and you update the record with id=49 to id=1049), MySQL will not modify the auto-increment value of the table to the updated value. In some cases, such as after DDL, restart, etc., the business starts to report errors. At this time, if you don’t know the current operation, you may mistakenly think that it is a problem with the current business operation. In fact, it is because of the pit buried by the update id (primary key conflict)
As shown below:

Figure 1: Original data before update

Execute update statement

update test set id = 10 where id = 2;

Figure 2: Updated data

Execute a new insert statement

insert test (name) values ​​('dddd')

Figure 3: New data inserted

I believe everyone has seen the problem at this time. There may be no problem at the beginning after the update, but when the self-incrementing id catches up with the maximum value you updated, id conflict is inevitable. . .

How to solve it

1. If it is a personal test library, it is not important, you can restart the database
2. Of course, the online database cannot be set up in this way, unless you are very willful (and you need a DBA to accompany you in being willful). At this time, you can try to specify an id to insert a piece of data that is meaningless in the business, such as soft deleted data. (My case list does not have a soft delete mark, you can understand it.)

insert test (id,name) values ​​(20,'eeee');

After operation, as shown in the figure:

Execute the following SQL statement and compare the results

insert test (name) values ​​('ffff');

At this point, the auto-increment id has started to increase from the maximum value.

After searching for information, I found that this BUG was raised in 2005, but it was not fixed due to performance and few scenarios; this problem behaves normally in MySQL 8.0.11.

This is the end of this article about the problems encountered in Mysql updating the auto-increment primary key id. For more relevant content about Mysql updating the auto-increment primary key id, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!