如何查询SQL Server varchar列中未使用的ID数字（0-99999999）

我完全懂你的痛点——当ID是varchar类型、范围又大（0到99999999）时，直接用NOT IN或NOT EXISTS没效果，大概率是因为没有生成完整的目标数字范围来做对比，或者存在类型不匹配、NULL值干扰的问题。下面给你几种实用的解决方案：

1. 先排查之前失败的核心原因 #

• 如果你的ID列存在NULL值，NOT IN会直接返回空结果（因为NULL NOT IN (...)的逻辑结果是UNKNOWN），这时候NOT EXISTS本应更可靠，但前提是你有完整的对比范围。
• 类型不匹配：你的ID是varchar，如果直接和数字类型的范围对比，可能触发隐式转换错误，所以必须确保两边类型一致。

2. 生成完整数字范围的核心思路 #

要找出未使用的编号，首先得生成0到99999999的完整数字序列，再和表中的ID做对比。直接生成这么大的序列可能有性能压力，推荐用以下两种方式：

方案一：递归CTE生成范围（适合临时查询） #

递归CTE可以快速生成连续数字，再转换为varchar和你的ID列对比：

WITH RECURSIVE num_range AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM num_range WHERE num < 99999999
)
SELECT CAST(num AS varchar) AS unused_id
FROM num_range
WHERE NOT EXISTS (
    SELECT 1 FROM your_table 
    WHERE your_id_column = CAST(num AS varchar)
)
OPTION (MAXRECURSION 0); -- SQL Server需要关闭递归层数限制；MySQL无需此语句

注：不同数据库语法略有差异：MySQL需保留WITH RECURSIVE，PostgreSQL直接用WITH，Oracle则用CONNECT BY生成序列。

方案二：数字辅助表提升性能（适合频繁查询） #

如果需要经常执行这个查询，建议预先创建一个数字辅助表，后续查询会快很多：

-- 以MySQL为例，先创建辅助表
CREATE TABLE number_table (num INT PRIMARY KEY AUTO_INCREMENT);

-- 用存储过程批量插入数据
DELIMITER //
CREATE PROCEDURE fill_numbers()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i <= 99999999 DO
        INSERT INTO number_table VALUES(i);
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;
CALL fill_numbers();

-- 后续查询未使用的ID
SELECT CAST(num AS varchar) AS unused_id
FROM number_table
WHERE NOT EXISTS (
    SELECT 1 FROM your_table 
    WHERE your_id_column = CAST(num AS varchar)
);

方案三：分段查询优化性能 #

如果直接生成99999999条数据太占资源，可以分段查询，比如每次处理10000个数字：

WITH RECURSIVE num_range AS (
    SELECT 0 AS start_num, 9999 AS end_num
    UNION ALL
    SELECT start_num + 10000, end_num + 10000 FROM num_range WHERE end_num < 99999999
)
SELECT CAST(n.num AS varchar) AS unused_id
FROM num_range r
JOIN (
    SELECT start_num + (t1.num + t2.num*10 + t3.num*100 + t4.num*1000) AS num
    FROM (SELECT 0 num UNION ALL SELECT 1 UNION ALL ... SELECT 9) t1
    CROSS JOIN (SELECT 0 num UNION ALL SELECT 1 UNION ALL ... SELECT 9) t2
    CROSS JOIN (SELECT 0 num UNION ALL SELECT 1 UNION ALL ... SELECT 9) t3
    CROSS JOIN (SELECT 0 num UNION ALL SELECT 1 UNION ALL ... SELECT 9) t4
) n ON n.num BETWEEN r.start_num AND r.end_num
WHERE NOT EXISTS (
    SELECT 1 FROM your_table 
    WHERE your_id_column = CAST(n.num AS varchar)
)
OPTION (MAXRECURSION 0);

这种方式通过交叉连接生成0-9999的数字，再分段扩展，大幅减少一次性生成大量数据的压力。

3. 额外注意事项 #

• 如果ID列存在非数字字符（比如前缀），需要先清洗数据，确保只对比数字部分。
• 对于超大规模范围，辅助表会占用几百MB存储空间，可以根据实际需求缩小范围，比如只查询已使用ID的最小值到最大值之间的空缺。

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