mysql 最小id,MySQL找到最小的+唯一的ID可用

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

解决方案

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.