如何使用MySQL查找数字序列中的下一个可用编号

MySQL查询实现方案 #

你可以直接使用以下语句，会自动优先返回断层处的最小可用编号，当序列完全连续时返回最大编号+1：

SELECT MIN(onu_num) AS next_available_onu
FROM (
    -- 生成现有ONU编号+1的候选列表
    SELECT CAST(substring_index(gponport, '/', -1) AS UNSIGNED) + 1 AS onu_num
    FROM pon
    WHERE he = '10.52.8.5'
      AND gponport LIKE '16/1/%'
    UNION
    -- 补充1作为候选，覆盖1未被占用的场景
    SELECT 1
) AS candidate_nums
-- 筛选出未被使用的编号，取最小值即为结果
WHERE onu_num NOT IN (
    SELECT CAST(substring_index(gponport, '/', -1) AS UNSIGNED)
    FROM pon
    WHERE he = '10.52.8.5'
      AND gponport LIKE '16/1/%'
);

逻辑说明

• 内层查询首先把所有现有ONU编号加1生成候选值，同时额外加入1作为候选，避免1本身未被占用的场景漏判
• 外层查询筛选出所有不在现有ONU列表中的候选值，取最小的就是你需要的结果

场景验证

1. 当现有ONU为1-10完全连续时，筛选出的最小未使用候选值为11，符合预期
2. 当现有ONU缺失5时，候选值中会包含4+1=5，且5不在现有列表中，直接返回5，符合预期
3. 当1本身未被占用时，直接返回1，覆盖边界场景

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