use empty ID with AUTO_INCREMENT

Question

I use AUTO_INCREMENT for my PK and I want to reuse ids when I remove a record from the table.

For example, if I delete the record with id=5, I want the next insert of a new record to use the empty id=5.

So how can I fill the unused IDs with AUTO_INCREMENT?

Very bad idea. Re-using primary keys will get you into trouble if you decide to archive your data. — PM 77-1, Jul 06 '14 at 21:59
What are you trying to achieve anyway? Are you afraid to run out of numbers? — PM 77-1, Jul 07 '14 at 01:34
The maximum value for [`int`](http://dev.mysql.com/doc/refman/5.7/en/integer-types.html) in MySQL is **2,147,483,647**. Not enough? — PM 77-1, Jul 07 '14 at 02:08
yes good news but for my question there is any solution just for fun ? — Hayi, Jul 07 '14 at 09:13
Use a generated ID. Take a look on the UUID function on mysql. It will generate enough random keys that will allow you to store `N` amount of records without having problems. I'm not sure as to why you want to reuse ID's though. Like they said above, that's a terrible idea. — CodeTrooper, Jul 07 '14 at 09:15

score 2 · Accepted Answer · answered Jul 07 '14 at 10:22

The notion of reusing auto_increments is a horrible idea. Biggest reason for this is concurrency, among others. You can (and will) make your entire database and data model unstable.

Using a proper data type for the id will rectify the issue of you running out of numbers, proper data type being bigint in this case.

The bigint allows you to store up to the size of 18446744073709551615.

If your inserts are 1000 a second, that means during 1 day you will perform 1000 * 3600 * 24 records, or 86 400 000 records a day. During the course of a year, that will be 31,536,000,000 records. Using simple maths, you can calculate that you'll exceed the bigint from above in some 58 000 years if you are able to produce 1000 inserts a second during that time, which should convince you to use proper data types for handling records rather than trying to be smart about that will only result in failure.

use empty ID with AUTO_INCREMENT

1 Answers1