Use lower unused numbers in auto increment column

I would like to reuse numbers that have been deleted.

[itledi]

I have a MySQL table where new rows are being created all the time. In this table, I have one column called "id" that serves as my index and is set to auto_increment as well as one column called expiration, letting me know how long to keep the row.

I created a script that will look at my table daily to delete records past the expiration date.

Let's say I have five records, ids: 1, 2, 3, 4, 5.

If records 1, 2, and 4 or deleted, I've noticed that if three new records are added, it will be placed in slots 6, 7, and 8. However, I would like to "recycle" lower unused numbers 1, 2, and 4.

How can I do this?

[lammert]

You can change the auto increment counter with the following MySQL statement:

ALTER TABLE `table_name` AUTO_INCREMENT = new_value;
where new_value is the value you want to assign to the counter.

It is not possible to use a new auto_increment value lower than currently exists in the table. In your example table with ID 1,2,3,4,5 where 1,2 and 4 are deleted, the remaining values will be 3 and 5. The lowest AUTO_INCREMENT value you can set in that case is 6.