mysql find smallest + unique id available

Question

i have a column ID and something like 1000 items, some of then were removed like id=90, id=127, id=326

how can i make a query to look for those available ids, so i can reuse then for another item?

its like a min(ID) but i want to find only the ids that are NOT in my database, so if i remove a item with the ID = 90, next time i click on ADD ITEM i would insert it as id = 90

Answer 1

You can get the minimum available ID using this query:

SELECT MIN(t1.ID + 1) AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL

What it does is that it joins the table with itself and checks whether the min+1 ID is null or not. If it's null, then that ID is available. Suppose you have the table where ID are:
1
2
5
6

Then, this query will give you result as 3 which is what you want.

Answer 2

Do not reuse IDs. You usually have way enough available IDs so you don't have to care about fragmentation.

For example, if you re-use IDs, links from search engines might point to something completely unrelated from whatever is in the search index - showing a "not found" error is much better in such a case.

Answer 3

It's against the concept of surrogate keys to try to reuse IDs

The surrogate key is good because it idetifies the record itself, not some object in real life. If the record is gone, the ID is gone too.

Experienced DB developers are not afraid of running out of numbers because they know how many centuries it is needed to deplete, say, long integer numbers.

BTW, you may experience locking or violating uniqueness problems in a multithreaded environment with simultaneous transactions trying to find a gap in the ID sequence. The auto increment id generators provided by DB servers usually work outside the transactions scope and thus generate good surrogate keys.

Further reading: Surrogate keys