MySQL & File Management: reuse deleted IDs in future auto increment

Question

I'm using the database to name files when they are uploaded, and my application uses the database to identify the file (for video streaming), so they need to be the same. But if an upload fails, or someone cancel the upload i'm left with a wasted ID.

overtime I have more wasted ID numbers then one's being used, I created a script that labels them unused after 5 days of the row is inserted, and was thinking of making another script to use those "unused" IDs and if none exists insert a new row. But I can see problems when I have multi servers doing that task.

Can I delete a row and have the ID from that row be re-used in future auto increments?

I'm open to other ways of accomplishing this task, but if I can re-use deleted ID's in future auto increment.

score 2 · Accepted Answer · answered Mar 06 '13 at 18:04

2

If you delete the row you can run

ALTER TABLE yourtable AUTO_INCREMENT = 1

to re-set the auto-incrementing to start at the last unused ID, but I don't see the point in doing this. If you're doing this for sake of the database, don't. The database doesn't care.

answered Mar 06 '13 at 18:04

Matt Busche

14,216
5
36
61

But if you do that, what happens to the UID that were made after that. – Thomas Depole Mar 06 '13 at 18:26
it won't overwrite any data. if you had IDs of 1, 2, 4, 5 and deleted 5 then ran that command it would start at 5 and ignore 3. – Matt Busche Mar 06 '13 at 19:04

score 1 · Answer 2 · answered Nov 15 '13 at 20:34

I'd +1 the "DON'T" if it's for the sake of the database, because really, it doesn't care at all. With that said, I do anyway because I'm a bit of a neat freak and I'll give you an example of why I decided to do it.

I created a service for musicians that uses an auto_increment as an index reference for songs, purchases and a few other things, and as with most apps that have registration forms and mail functions, it was bombed with bots, spammers and the like who created tons and tons of free accounts and they get deleted often (cron tab automatically cleans house about every 3 days) and what I end up getting are very inconsistent counts. 1 to 41, then 129 to 240, then 491 to 800 and so on.

It was more of a personal preference to 'close the gaps' because, as I said, i'm a bit of a neat freak and I like consistency. I also have to print out reports for managers and so on that want to track data and they like consistency, so it helps a lot.

And as for what happens when auto increment hits a number that already exits? It skips to the next available one and continues. That's why you can reset it to 1 and everything's cool. no harm done.

MySQL & File Management: reuse deleted IDs in future auto increment

2 Answers2