如何从表中获取第一个空闲ID

Question

我有一张带有主键字段 ID 的表。我不想使用自增，因为我需要让用户手动选择新对象的 ID。所以我的想法是：

1. 默认情况下，在编辑视图中，ID 字段将为 0。
2. 如果用户没有更改它，我需要找到第一个空闲的 ID 并使用它。
3. 如果用户更改了 ID，则首先需要检查是否有另一个具有该 ID 的对象，在这种情况下抛出错误。
4. 如果没有，则使用用户选择的 ID。
5. 创建新对象

问题是如何查询 SQL Server 表以获取第一个空闲的 ID？ 示例 1：

ID
--
1
2
10

第一个可用的ID是3。

例子2：

ID
--
1
2
3
4

第一个空闲ID是5

有没有什么方法可以实现这个目标？我所能想到的是获取最小值和最大值，创建一个循环以获取可能的数值，然后与表数据进行比较，但这涉及到向数据库发送太多的查询请求。谢谢！

- ericpap

---

我有一张带有主键字段ID的表格。如果你更改该字段，则不要使其成为主键。请将其作为普通列使用。 - juergen d

请阅读原帖要做的事情。这并不是“操纵那个字段”。如果可能的话，原帖作者只是想让用户选择一个值，否则在一定范围内获取另一个合理的值。请进行翻译，不要添加解释或其他内容。 - Gordon Linoff

没错。就像手动实现一个自动数字字段，可以跳过某些值一样。 - ericpap

@ericpap - 你这样做的特定原因是什么？我认为当你可以使用类似于“IDENTITY”或自增的东西时，解决方案只会变得更加困难。 - Dominic Zukiewicz

自增字段跳过值是为了确保数据完整性。在您的情况下重用已删除的值是非常危险的想法。这是您要向用户推迟的类型要求，告诉他们这是非常冒险和不好的做法。如果您这样做，请确保在数据库中设置适当的FK，否则您将百分之百地在短时间内拥有错误数据。顺便问一下，您打算从不更改它们的用户那里放入多少个0？ - HLGEM

Answer 1

您可以将第一个空闲的ID视为没有“next”值的第一个ID：

select coalesce(min(t.id) + 1, 0)
from table t left outer join
     table t2
     on t.id = t2.id - 1
where t2.id is null;

编辑:

如果你想将"1"视为可能的缺失值:

select (case when min(minid) > 1 then 1 else coalesce(min(t.id) + 1, 0) end)
from table t left outer join
     table t2
     on t.id = t2.id - 1 cross join
     (select min(id) as minid from table t) const
where t2.id is null;

- Gordon Linoff

---

@ericpap . . . 哎呀，条件语句里不小心加了一个“不”。 - Gordon Linoff

非常好的想法！！！几乎适用于每种情况。如果出现以下情况，则无法正常工作：1）空ID是表中的第一个（如果ID为2、3、4，则会将5作为第一个空闲ID）。2）如果表中没有数据，则返回null。 - ericpap

我建议更改为 MIN(ISNULL(t.id, 0)) + 1。这样可以返回1。 - fnightangel

先生，我有一个建设性的批评，如果表是空的会发生什么呢？ :) - M.Ali

@M.Ali 是的，你说得对。实际上是 ISNULL(MIN(T.ID), 0) + 1 <- 如果表为空，则这个方法可行。 - fnightangel

Answer 2

测试表格

CREATE TABLE ID_TABLE(ID INT)
INSERT INTO ID_TABLE VALUES
(1),(2),(10)

存储过程

ALTER PROCEDURE dbo.usp_GetNextValue
@nxt_ID_Wanted INT = 0,
@nxt_ID_Available INT  OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- If user hasnt passed any value get next avilable value
IF (@nxt_ID_Wanted = 0)   
 BEGIN 
          SELECT TOP 1 @nxt_ID_Available  =  ID + 1 
        FROM 
         (
          SELECT   ID , ROW_NUMBER() OVER (ORDER BY ID ASC) AS rn
         FROM ID_TABLE
         )Q
        WHERE ID = rn
        ORDER BY ID DESC

      IF (@nxt_ID_Available IS NULL)
        BEGIN
          SET @nxt_ID_Available = 1;
        END
 END
     -- If user has passed a value check if it exists and raise error
ELSE  IF EXISTS(SELECT 1 FROM ID_TABLE WHERE ID = @nxt_ID_Wanted)
 BEGIN

       RAISERROR('Selected ID value already exists',16,1)
       SET @nxt_ID_Wanted = 0;
       RETURN;
 END 
ELSE      -- else just let the user have the value he/she wanted
 BEGIN 
   SET @nxt_ID_Available = @nxt_ID_Wanted;
 END

END

执行程序。

DECLARE @ID INT;
EXECUTE dbo.usp_GetNextValue @nxt_ID_Wanted = 6
                             ,@nxt_ID_Available = @ID OUTPUT

SELECT @ID

- M.Ali

Answer 3

这个函数用于获取 PostgreSQL（PL/pgSQL）表中的第一个可用 ID：

CREATE OR REPLACE FUNCTION next_free_id_from(table_name TEXT) 
  RETURNS BIGINT AS
$$
DECLARE
  next_id BIGINT;
BEGIN
  execute 'select rn from (select id, row_number() over (order by id) as rn from ' || quote_ident(table_name) || ') as T where id > rn limit 1' into next_id;
  if next_id is null then
    execute 'select COALESCE(max(id), 0) + 1 from ' || quote_ident(table_name) into next_id;
  end if;
  return next_id;
END;
$$ language 'plpgsql' STRICT;

执行：

select next_free_id_from('test');

test 是一个表名。这个函数调用可以放置在主键的 DEFAULT 中。

- Konard

---

我真的不明白为什么你会用PostgreSQL的解决方案回答一个关于SQL Server的七年前的请求。顺便说一下，你的一般想法是好的，但缺少两种情况：1. 如果表中没有行，则应返回1作为第一个可用ID。2. 如果表中有行但没有间隙，则应返回max（id）+1作为下一个可用ID。 - Thorsten Kettner

@ThorstenKettner 谢谢，我一直在寻找解决方案并想要分享它。顺便说一下，我已经更新了我的答案以包含您建议的修复。 - Konard