j3ssi3ftw j3ssi3ftw - 2 months ago 13
MySQL Question

Why do my sql tables maintain deleted id's?

When I insert data into a brand new table, it will assign a new id via AUTO_INCREMENT. So the first time I perform an insert I get an id of 1. However, if I delete the row and insert new data, the table acts as if there is still a preceding row (the new row will have an id of 2). This behavior concerns me because I feel like the data is still persisting somewhere. Any ideas of what it could be?

Answer

Your data is not persisting. MySql maintains a separate table about your table containing, among other things, the next auto-increment value for your table. You can reset this with:

ALTER TABLE tablename AUTO_INCREMENT = 1

However, be aware that if you are resetting to a value below another valid value in the table, you're asking for trouble.