如何获取1到n范围内未被数据库记录占用的最小可用数字

我的需求 #

给定1到上限n的数字范围，找出未被已有记录占用的最小数值。

具体场景 #

需要将客户信息上传至数据库，该库需兼容legacy特性，无权修改其结构。库中存在名为Code的INT类型UNIQUE列，已插入部分非连续数据，示例记录为(1,2,3,5,7,125,200,500,127000)。

需要为每条新记录分配未被占用的Code值，不能留下数据空洞。无法直接查询当前最大Code加1使用，因为最大值可能已接近该列的上限，但中间还有大量可用数值，且需要批量插入数千条记录。

以上述示例数据为例，首次查询应返回4，插入4后下一次应返回6，以此类推，不能直接取上次插入值加1，因为下一个数值可能已被占用。

已尝试的方案 #

目前通过如下逻辑实现，这里用pseudocode简化展示，实际查询包含大量列导致语句很长：

-- for each record
DECLARE i = 0
WHILE i < limit
    IF (COUNT(*) FROM MyTable WHERE Code = i) = 0
        INSERT INTO MyTable (Code, ...) VALUES (i, ...)
        BREAK
    SET i = i+1;

该方案可以运行，但效率极低，如果要插入1000条记录，且1到500的Code都已被占用，会产生50万次循环查询。有没有更优的实现方式？最好可以实现类似INSERT INTO MyTable (Code, ...) VALUES (SMALLEST_AVALIABLE(Code), ...)的调用方式。

单条插入场景 #

通过自连接查找第一个数值断层的起始值，一次查询即可拿到最小可用Code，无需循环：

-- 查询最小可用Code
SELECT MIN(t1.Code + 1) AS smallest_available
FROM MyTable t1
LEFT JOIN MyTable t2 ON t1.Code + 1 = t2.Code
WHERE t2.Code IS NULL AND t1.Code > 0
-- 兜底处理1未被占用的边界情况
UNION ALL
SELECT 1
WHERE NOT EXISTS (SELECT 1 FROM MyTable WHERE Code = 1)
LIMIT 1;

你可以直接把这段逻辑嵌入INSERT语句，实现你需要的调用效果：

INSERT INTO MyTable (Code, 其他列名)
SELECT (
    SELECT MIN(t1.Code + 1)
    FROM MyTable t1
    LEFT JOIN MyTable t2 ON t1.Code + 1 = t2.Code
    WHERE t2.Code IS NULL AND t1.Code > 0
    UNION ALL
    SELECT 1
    WHERE NOT EXISTS (SELECT 1 FROM MyTable WHERE Code = 1)
    LIMIT 1
), 其他列对应的值;

该查询仅需要一次Code索引扫描，性能远高于逐行循环查询。

批量插入场景 #

如果需要一次性插入数千条记录，可先批量取出指定数量的可用Code再统一插入，全程仅需2次查询：

-- 生成指定范围的连续数字序列，取未被占用的前N个值
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 2000 -- 上限根据你需要的批量插入数量调整
)
SELECT n AS available_code
FROM numbers
LEFT JOIN MyTable t ON numbers.n = t.Code
WHERE t.Code IS NULL
LIMIT 1000; -- 取你需要的可用Code数量

拿到可用Code列表后，直接拼接批量INSERT语句即可。如果你的数据库不支持递归CTE，可提前建立一张存储1到Code列上限的数字辅助表，查询效率会更高。

并发场景注意事项 #

因Code列存在UNIQUE约束，高并发插入时建议加行锁或增加唯一键冲突重试逻辑，避免多请求抢占同一个可用Code导致插入失败。

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