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

Note that the answers by shamittomar and Haim Evgi don't work if the lowest ID is free. To allow for the refilling the lowest ID, pre-check to see whether it is available:

SELECT TRUE FROM tablename WHERE ID = 1;

If this returns anything, then the ID of 1 is not free and you should use their answer. But if the ID of 1 is free, just use that.