如何高效查找MySQL指定范围内未存储的BIGINT类型ID？

方法1：递归CTE生成连续ID + 左连接（MySQL 8.0+推荐） #

直接在数据库层面生成目标区间的所有连续ID，通过左连接筛选未匹配的未使用ID，避免PHP端循环查询，性能最优。

WITH RECURSIVE id_range AS (
    SELECT 201100 AS id
    UNION ALL
    SELECT id + 1 FROM id_range WHERE id < 201600
)
SELECT r.id AS unused_id
FROM id_range r
LEFT JOIN your_table t ON r.id = t.id
WHERE t.id IS NULL
ORDER BY r.id;

• 逻辑：递归CTE先构建201100到201600的连续ID序列，左连接业务表后，无匹配的ID即为未使用项。
• 优势：全程在数据库内计算，无需传输大量数据到PHP，区间越大效率提升越明显。

方法2：数字辅助表（兼容MySQL 5.x） #

若MySQL版本不支持CTE，可预先创建数字辅助表生成连续ID序列：

1. 新建并填充数字表（仅需执行一次）：

CREATE TABLE numbers (n INT UNSIGNED NOT NULL PRIMARY KEY);
-- 批量插入0~999的数字（可根据需求扩展范围）
INSERT INTO numbers VALUES (0),(1),(2),...,(999);

2. 生成目标区间并筛选未使用ID：

SELECT (201100 + n) AS unused_id
FROM numbers
WHERE (201100 + n) <= 201600
LEFT JOIN your_table t ON (201100 + n) = t.id
WHERE t.id IS NULL
ORDER BY unused_id;

方法3：间隙查找法（适合超大区间+稀疏数据） #

若目标区间极大（如跨度10万+）且已有ID分布稀疏，无需生成全量连续ID，通过分析现有ID的间隙定位未使用项：

WITH existing_ids AS (
    -- 获取区间内已有的ID并排序
    SELECT id FROM your_table WHERE id BETWEEN 201100 AND 201600 ORDER BY id
),
gaps AS (
    -- 计算每个已用ID与下一个已用ID的间隙范围
    SELECT 
        id + 1 AS start_gap,
        LEAD(id) OVER (ORDER BY id) - 1 AS end_gap
    FROM existing_ids
)
-- 整合所有未使用ID：区间开头间隙、中间间隙、区间结尾间隙
SELECT unused_id
FROM (
    -- 处理区间开头到第一个已用ID的间隙
    SELECT seq AS unused_id
    FROM (
        SELECT 201100 AS seq
        UNION ALL
        SELECT seq + 1 FROM seq_start WHERE seq < (SELECT MIN(id) FROM existing_ids) - 1
    ) AS seq_start
    UNION ALL
    -- 处理中间间隙
    SELECT seq AS unused_id
    FROM gaps
    CROSS JOIN (
        SELECT 1 AS seq UNION ALL SELECT 2 UNION ALL ... SELECT 10000 -- 生成覆盖最大间隙长度的序列
    ) AS seq_list
    WHERE start_gap <= end_gap AND seq BETWEEN start_gap AND end_gap
    UNION ALL
    -- 处理最后一个已用ID到区间结尾的间隙
    SELECT seq AS unused_id
    FROM (
        SELECT (SELECT MAX(id) FROM existing_ids) + 1 AS seq
        UNION ALL
        SELECT seq + 1 FROM seq_end WHERE seq < 201600
    ) AS seq_end
) AS all_unused
WHERE unused_id BETWEEN 201100 AND 201600
ORDER BY unused_id;

• 逻辑：仅针对已用ID之间的空白区间生成未使用ID，避免全量序列生成，大幅减少计算量。

PHP端优化方案（若必须在PHP处理） #

不要循环逐个查询数据库，改为一次批量获取区间内已用ID，再在内存中比对：

$start = 201100;
$end = 201600;

// 一次查询区间内所有已用ID
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
$stmt = $pdo->prepare("SELECT id FROM your_table WHERE id BETWEEN ? AND ?");
$stmt->execute([$start, $end]);
$usedIds = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'id');
$usedIds = array_flip($usedIds); // 转成键为ID的数组，isset检查比in_array效率高10倍以上

// 遍历区间筛选未使用ID
$unusedIds = [];
for ($id = $start; $id <= $end; $id++) {
    if (!isset($usedIds[$id])) {
        $unusedIds[] = $id;
    }
}

// 输出结果
print_r($unusedIds);

• 优势：仅一次数据库查询，后续操作在内存完成，比循环查库效率提升数十倍。

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