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

This article mainly shows you "how to solve the problem of Mysql update self-increasing primary key id", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to solve the problem of Mysql update self-increasing primary key id" this article.

Why update self-increasing id

I have to update a batch of id because of a historical business hole, and how many times I need to expand the id to update in order to avoid conflicts. Because the amount of data in my table is not high, it belongs to the case of high reading and low writing, so it is simply expanded by 1000.

problem

In MySQL, if we update the self-incrementing primary key to a larger value (for example, the maximum value of id is 1000 now, and you update the record of id=49 to id=1049), MySQL will not change the self-increment of the table to the updated value. In some cases, such as DDL, restart, etc., the business begins to report an error. If you do not know the current operation may be mistakenly regarded as the problem of the current business operation, it is actually because of updating the hole buried by id (primary key conflict).

As shown below:

Figure 1: raw data before updating

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

At this time, everyone must have seen the problem. There may be no problem at the beginning after the update, but when you add id to catch up with the maximum value of your update, id conflicts are inevitable.

How to solve

1. If it is a personal test library, it is not very important. You can restart the database.

two。 Of course, the online database cannot be done in this way, unless you are very wayward (you still need dba to accompany you). At this point, you can try to specify id to insert a piece of data that is meaningless in business, such as soft delete data. (there is no soft delete logo in my case list.)

Insert test (id,name) values (20)

After operation, as shown in the figure:

After executing the following SQL statement, compare the results

Insert test (name) values ('ffff')

At this time, the self-increasing id has started to increase from the maximum.

Looking for information, it was found that this BUG was proposed in 2005 because the performance and scenarios were rarely not fixed; this problem behaved normally in MySQL 8.0.11.

The above is all the contents of this article entitled "how to solve the problem of Mysql updating self-adding primary key id". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.