MySQL找到最小的+唯一的ID可用 [英] mysql find smallest + unique id available

问题描述

我有一个列ID，大约有1000个项目，然后其中的一些已被删除，例如id=90, id=127, id=326

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

我如何进行查询以查找那些可用的ID，以便可以将其重新用于其他项目?

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

就像min(ID)一样，但是我只想查找不在数据库中的id，因此，如果我删除带有ID = 90的项目，则下次我单击添加项目时，我会将其插入为

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

推荐答案

您可以使用以下查询获取最小可用ID:

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

它的作用是将表与自身连接在一起，并检查min+1 ID是否为null.如果为空，则该ID可用.假设您有ID所在的表:
1
2
5
6

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

然后，此查询将为您提供所需的结果3.

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

这篇关于MySQL找到最小的+唯一的ID可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！