I just did a practice insert that put in 1700 rows. When I verified that it worked, I deleted all of them. The next rID that was used, on the next insert, was still 1700 higher than I thought it should be. Won’t mySQL make use of primary row numbers that have been deleted? Otherwise I can see that primary key field getting quite high over time.
No the auto_increment just keeps on counting over time. Indeed, that value can get quite high over time but the maximum value of an int is ~2 billion (~4 billion if you make it unsigned) so that should most likely suffice.
If you’re wondering why id’s don’t get “recycled”, consider a scenario with two tables, let’s say student and grades, are linked via id’s. Let’s now say you remove a student but not the grates that belong to the student [from the grades table].
If a new student that enrolls were to get the recycled id from the student that was just deleted it would automatically get the grades of that student, and that’s not what you’d want to happen
let’s say you use INTEGER UNSIGNED and insert 10 rows per second
any idea how long it will be before you run out of numbers?
spoiler: more than 10 years