Search code examples
mysqlfile-uploadauto-increment

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

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.

Solution

  • 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.