How to handle auto increment ID column fragmentation in MySQL

Question

I have a table with an auto_increment field, and sometimes rows are deleted, so auto_increment leaves spaces. Is there a way to avoid this or not, at least how to write an SQL query that:

• Changes the value of auto_increment as max (current value) + 1
• Returns a new value auto_increment ?

I know how to write parts 1 and 2 , but can I put them in the same query?

If this is not possible:

How can I "select" (return) the value of auto_increment or auto_increment value + 1?

Answer 1

Renumbering will lead to confusion. Existing reports will refer to record 99, and yet, if system reviewers can move this record to 98, now all reports (and completed user interfaces) are erroneous. After allocating a unique identifier, it must remain fixed.

Using ID fields for anything other than simple unique numbering will be problematic. The “no space” requirement simply contradicts the delta capability requirement. Perhaps you could mark the entries as deleted rather than deleting them. Then there really are no spaces. Suppose you create counting accounts: you have a zero value that cancels the invoice with this number, but does not delete it.

Answer 2

There is a way to manually insert the identifier even in the autoinc table. All you have to do is identify the missing identifier.

However, do not do this. This can be very dangerous if your database is relational. The remote identifier may have been used elsewhere. When it is removed, it does not present a big problem, perhaps it will lead to an orphan recording. If it is replaced, it will cause a huge problem because the wrong attitude will be present.

Please note that I have a car table and a people table

 car carid ownerid name person personid name 

And that there is some simple data

 car 1 1 Van 2 1 Truck 3 2 Car 4 3 Ferrari 5 4 Pinto person 1 Mike 2 Joe 3 John 4 Steve 

and now I delete the man John.

 person 1 Mike 2 Joe 4 Steve 

If I added a new person, Jim, to the table, and he received an identifier that filled in the blank, then he would finish getting id 3

 1 Mike 2 Joe 3 Jim 4 Steve 

and by ratio, will be the owner of a Ferrari.

Answer 3

I generally agree with the wise people on this page (and duplicate questions), we recommend not to use mnogointensivnye identifiers. This is good advice, but I don’t think it’s up to us to decide what rights or errors ask the question, let the developer know what they want to do and why.

The answer is that, as Travis J mentioned, you can reuse the auto-increment identifier by including the id column in the insert statement and assigning the value you need.

Here's the point of putting a wrench into operation: MySQL itself (at least 5.6 InnoDB) will reuse the auto-increment identifier in the following circumstances:

• delete rows with highest auto increment id
• Stop and start MySQL
• insert new line

The entered line will have an identifier calculated as max (id) +1, it will not continue with the identifier being deleted.

Answer 4

As djna said in his answer, it is not a good practice to modify database tables this way, nor is it necessary if you have chosen the correct schema and data types. By the way, according to part of your question:

I have a table with an auto_increment field, and sometimes rows are deleted, so auto_increment leaves spaces. Is there any way to avoid this?

• If your table has too many spaces in the auto-increment column, probably due to the large number of INSERT test queries
• And if you want to prevent id values ​​from being exceeded by removing spaces
• And also, if the id column is just a counter and has nothing to do with any other column in your database

this may be what you (or any other person looking for such a thing) are looking for:

Decision

• remove the original id column
• add it again using auto_increment on

But if you just want to reset auto_increment to get the first available value:

 ALTER TABLE `table_name` AUTO_INCREMENT=1 

Answer 5

Not sure if this will help, but in sql server you can update authentication fields. It seems mySql has an ALTER TABLE statement. For example, to set the id to continue at step 59446.

 ALTER TABLE table_name AUTO_INCREMENT = 59446; 

I think you should combine the query to get the largest value of the auto_increment field, and then use the alter table to update as needed.