PostgreSQL：生成表中未存在的范围内唯一随机字符串

核心思路 #

先确定指定字符串区间内的所有候选值，排除tableA表id字段中已存在的值，再从剩余可用值里随机选取一个作为新ID。下面提供两种无需额外存储过程的实现方法，分别适配不同场景：

方法一：递归CTE生成候选值（适合小范围场景） #

当指定的字符串范围不大时，用递归CTE生成区间内的所有候选ID，再通过左连接排除已存在的记录，最后随机取一个。

示例代码（对应范围'abcdef'到'abcdej'） #

WITH RECURSIVE candidate_ids AS (
    -- 起始ID
    SELECT 'abcdef'::varchar(6) AS id
    UNION ALL
    -- 递归生成下一个ID
    SELECT 
        CASE
            -- 处理最后一位字符到顶的情况（比如小写z，根据实际字符集调整）
            WHEN RIGHT(id, 1) = 'z' THEN
                CONCAT(LEFT(id, length(id)-1), chr(ascii(LEFT(id, length(id)-1)) + 1))
            ELSE
                CONCAT(LEFT(id, length(id)-1), chr(ascii(RIGHT(id, 1)) + 1))
        END AS id
    FROM candidate_ids
    -- 终止条件：生成的ID不超过结束值
    WHERE id < 'abcdej'
), available_ids AS (
    -- 筛选出未在tableA中存在的ID
    SELECT c.id
    FROM candidate_ids c
    LEFT JOIN tableA a ON c.id = a.id
    WHERE a.id IS NULL
)
-- 随机选取一个可用ID
SELECT id FROM available_ids ORDER BY random() LIMIT 1;

注意事项 #

• 需根据实际使用的字符大小写调整判断条件（比如大写字符就把'z'换成'Z'）；
• 如果字符串的递增逻辑涉及更复杂的字符集（比如包含数字），需要修改递归分支的字符转换逻辑。

方法二：随机生成+存在性检查（适合大范围场景） #

当指定范围极大（比如8位或15位字母组合，候选值数量众多），递归生成所有候选值会占用大量资源，这时可以直接随机生成区间内的字符串，再检查是否已存在，直到找到可用值。

示例代码（对应范围'FOOBARA'到'FOOBARZ'） #

DO $$
DECLARE
    start_str varchar(15) := 'FOOBARA'; -- 范围起始字符串
    end_str varchar(15) := 'FOOBARZ';   -- 范围结束字符串
    new_id varchar(15);
    str_len integer := length(start_str);
    id_exists boolean;
BEGIN
    LOOP
        -- 逐位生成随机字符，确保整体在指定区间内
        new_id := '';
        FOR i IN 1..str_len LOOP
            new_id := new_id || chr(
                ascii(substring(start_str, i, 1)) + 
                floor(random() * (ascii(substring(end_str, i, 1)) - ascii(substring(start_str, i, 1)) + 1))
            );
        END LOOP;
        -- 检查生成的ID是否已存在
        SELECT EXISTS(SELECT 1 FROM tableA WHERE id = new_id) INTO id_exists;
        -- 找到未存在的ID就退出循环
        IF NOT id_exists THEN
            EXIT;
        END IF;
    END LOOP;
    -- 输出结果（也可以直接插入到表中，比如 INSERT INTO tableA(id) VALUES(new_id);）
    RAISE NOTICE '生成的新ID：%', new_id;
END $$;

注意事项 #

• 这个DO块是一次性执行的匿名代码块，无需创建存储过程；如果需要重复调用，可以封装成函数，但依然无需额外存储过程；
• 如果可用候选值极少，可能会触发多次循环，但在ID范围足够大的实际场景中，这种情况概率极低；
• 确保start_str和end_str长度一致（符合你的场景要求），否则需要额外的长度校验逻辑。

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