如何使用SELECT语句生成数据表中未存在的3字符随机ID？

针对你的需求，这里提供不同数据库环境下的实现方案，核心思路是生成符合字符集规则的随机3字符组合，再过滤掉已存在于数据表中的ID：

通用逻辑说明 #

字符集包含26个大写字母+10个数字，共36个字符，3字符ID总共有36^3=46656种可能。我们通过随机选取字符拼接成ID，再用NOT EXISTS校验该ID是否未被使用。

MySQL 实现方案 #

基础版（单次生成，若生成的ID已存在则返回空） #

SELECT CONCAT(
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1)
) AS unused_random_id
WHERE NOT EXISTS (
    SELECT 1 FROM your_table WHERE id = unused_random_id
)
LIMIT 1;

递归版（确保返回有效ID，直到找到未使用的） #

如果担心单次生成的ID已存在，可使用递归CTE循环生成，直到找到可用ID：

WITH RECURSIVE random_ids AS (
    SELECT CONCAT(
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1)
    ) AS id
    UNION ALL
    SELECT CONCAT(
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RAND() * 36) + 1, 1)
    ) AS id
    FROM random_ids
    WHERE EXISTS (SELECT 1 FROM your_table WHERE id = random_ids.id)
)
SELECT id AS unused_random_id
FROM random_ids
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE id = random_ids.id)
LIMIT 1;

PostgreSQL 实现方案 #

基础版 #

SELECT CONCAT(
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
    SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1)
) AS unused_random_id
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE id = unused_random_id)
LIMIT 1;

递归版（确保返回有效ID） #

WITH RECURSIVE random_ids AS (
    SELECT CONCAT(
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1)
    ) AS id
    UNION ALL
    SELECT CONCAT(
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1),
        SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', FLOOR(RANDOM() * 36) + 1, 1)
    ) AS id
    FROM random_ids
    WHERE EXISTS (SELECT 1 FROM your_table WHERE id = random_ids.id)
)
SELECT id AS unused_random_id
FROM random_ids
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE id = random_ids.id)
LIMIT 1;

注意事项 #

• 将代码中的your_table替换为你的数据表名，id替换为表中存储ID的列名
• 建议给ID列添加唯一索引，能大幅提升NOT EXISTS的校验速度
• 当数据表中ID接近46656条上限时，递归可能会多执行几次，但这种场景极少出现

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