如何在MySQL中获取指定id范围内的首个可用id

核心实现方案 #

针对你给出的场景（查询foo表50~59区间内首个未被占用的ID，示例预期返回51），可以直接用自连接匹配空缺的方案，代码如下：

SELECT t1.id + 1 AS first_available_id
FROM `foo` t1
LEFT JOIN `foo` t2 
  ON t1.id + 1 = t2.id
WHERE t1.id BETWEEN 50 AND 58
  AND t2.id IS NULL
ORDER BY t1.id ASC
LIMIT 1;

方案说明 #

• 自连接逻辑：通过t1.id +1 = t2.id匹配相邻ID，当t2.id IS NULL时，说明t1.id +1就是未被占用的ID
• 范围过滤：t1.id BETWEEN 50 AND 58限定了只扫描目标区间内的ID，最大的t1.id取58是因为我们要找的空缺ID最大为59（58+1=59），避免无效扫描
• 排序取第一条：升序排序后取第一个匹配结果，就是区间内最小的未占用ID
• 性能优势：如果id是主键或建有索引，所有查询逻辑都可以命中索引，即使表数据量很大也能保持极高的查询效率，比生成序列表、自定义变量等方案更简洁高效。

场景兼容 #

如果需要兼容「区间起始ID本身未被占用」的情况（比如示例中如果ID 50不存在，预期返回50），可以用扩展版本：

SELECT IFNULL(
  (
    SELECT IF(
      NOT EXISTS(SELECT 1 FROM `foo` WHERE id = 50),
      50,
      (
        SELECT t1.id + 1
        FROM `foo` t1
        LEFT JOIN `foo` t2 ON t1.id +1 = t2.id
        WHERE t1.id BETWEEN 50 AND 58 AND t2.id IS NULL
        ORDER BY t1.id ASC
        LIMIT 1
      )
    )
  ),
  60 -- 整个区间全满时返回的默认值，可根据业务调整
) AS first_available_id;

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