How to handle fragmentation of auto_increment ID column in MySQL

Question

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

1. Alters the auto_increment value to be the max(current value) + 1
2. Return the new auto_increment value?

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

If that is not possible:

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

Answer 1

Renumbering will cause confusion. Existing reports will refer to record 99, and yet if the system renumbers it may renumber that record to 98, now all reports (and populated UIs) are wrong. Once you allocate a unique ID it's got to stay fixed.

Using ID fields for anything other than simple unique numbering is going to be problematic. Having a requirement for "no gaps" is simply inconsistent with the requirement to be able to delete. Perhaps you could mark records as deleted rather than delete them. Then there are truly no gaps. Say you are producing numbered invoices: you would have a zero value cancelled invoice with that number rather than delete it.

Answer 2

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

However, don't do this. It can be very dangerous if your database is relational. It is possible that the deleted id was used elsewhere. When removed, it would not present much of an issue, perhaps it would orphan a record. If replaced, it would present a huge issue because the wrong relation would be present.

Consider that I have a table of cars and a table of people

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 person John.

person
1 Mike
2 Joe
4 Steve

If I added a new person, Jim, into the table, and he got an id which filled the gap, then he would end up getting id 3

1 Mike
2 Joe
3 Jim
4 Steve

and by relation, would be the owner of the Ferrari.

Answer 3

I generally agree with the wise people on this page (and duplicate questions) advising against reusing auto-incremented id's. It is good advice, but I don't think it's up to us to decide the rights or wrongs of asking the question, let's assume the developer knows what they want to do and why.

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

Here is a point to put a spanner in the works: MySQL itself (at least 5.6 InnoDB) will reuse an auto-increment ID in the following circumstance:

• delete any number rows with the highest auto-increment id
• Stop and start MySQL
• insert a new row

The inserted row will have an id calculated as max(id)+1, it does not continue from the id that was deleted.

Answer 4

As djna said in her/his answer, it's not a good practice to alter database tables in such a way, also there is no need to that if you have been choosing the right scheme and data types. By the way according to part od your question:

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

1. If your table has too many gaps in its auto-increment column, probably as a result of so many test INSERT queries
2. And if you want to prevent overwhelming id values by removing the gaps
3. And also if the id column is just a counter and has no relation to any other column in your database

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

SOLUTION

1. remove the original id column
2. add it again using auto_increment on

But if you just want to reset the auto_increment to 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 reseed the identity fields. It seems there's an ALTER TABLE statement in mySql to acheive this. Eg to set the id to continue at 59446.

ALTER TABLE table_name AUTO_INCREMENT = 59446; 

I'm thinking you should be able to combine a query to get the largest value of auto_increment field, and then use the alter table to update as needed.