如何查找SQLite数据库表中整数列的最小未使用正整数？

针对你的需求，先分析现有SQL的问题，再提供可行的解决方案：

现有SQL的问题 #

你尝试的子查询SELECT 1 FROM mytable WHERE colname = colname + 1永远不会返回结果——任何整数都不可能等于自身加1，这导致外层查询的NOT EXISTS条件始终为真，最终返回的是所有colname+1中的最小值（在你的示例数据中会返回2），完全偏离了找最小未用整数的需求。

正确的SQLite解决方案 #

下面的语句会覆盖所有场景：

1. 如果1未被使用，直接返回1
2. 如果存在断档（如你的示例中3之后是5），返回第一个断档的起始数
3. 如果所有数从1开始连续，返回最大数+1

SELECT 
    CASE
        -- 检查1是否存在，不存在则直接返回1
        WHEN NOT EXISTS (SELECT 1 FROM mytable WHERE colname = 1) THEN 1
        ELSE COALESCE(
            -- 找到第一个存在n但n+1不存在的情况，返回n+1
            (SELECT MIN(t1.colname + 1)
             FROM mytable t1
             WHERE NOT EXISTS (SELECT 1 FROM mytable t2 WHERE t2.colname = t1.colname + 1)),
            -- 如果所有数连续，返回最大数+1
            (SELECT MAX(colname) + 1 FROM mytable)
        )
    END AS lowest_unused;

示例验证 #

针对你的数据1、2、3、5、6、23：

• 首先确认1存在，进入分支
• 子查询会找到t1.colname=3时，t1.colname+1=4不存在，因此MIN(4,6,24)返回4，符合预期

如果数据是2、3、4：

• 检查到1不存在，直接返回1

如果数据是1、2、3：

• 子查询没有符合条件的记录（每个n+1都存在），因此返回MAX(colname)+1=4

内容的提问来源于stack exchange，提问作者GFL