在PostgreSQL中基于用户输入生成自定义ID的实现咨询

要生成格式为M-YYYY-XX-XXXX的自定义ID（其中YYYY是用户出生年份，XX是省份缩写，XXXX是带前导零的随机四位数字），可以通过自定义PL/pgSQL函数实现，以下是两种场景的方案：

1. 基础随机ID生成 #

如果不需要严格保证ID唯一性，直接拼接固定前缀、用户输入数据和随机数字即可：

CREATE OR REPLACE FUNCTION generate_custom_id(p_birth_year INT, p_province_code VARCHAR(2))
RETURNS VARCHAR(15) AS $$
DECLARE
    random_digits VARCHAR(4);
BEGIN
    -- 生成0000-9999的随机数字，格式化为四位带前导零的字符串
    random_digits := LPAD(FLOOR(RANDOM() * 10000)::VARCHAR, 4, '0');
    -- 拼接所有部分，统一省份代码为大写
    RETURN CONCAT('M-', p_birth_year, '-', UPPER(p_province_code), '-', random_digits);
END;
$$ LANGUAGE plpgsql VOLATILE;

调用示例：

-- 生成出生年份1990、省份为ON的自定义ID
SELECT generate_custom_id(1990, 'ON');

返回结果示例：M-1990-ON-7241

2. 生成唯一自定义ID #

如果需要确保ID在目标表中唯一（避免重复），可以在函数中加入重复检查逻辑，直到生成未使用的ID：

假设目标表为users，其中custom_id字段存储该自定义ID：

CREATE OR REPLACE FUNCTION generate_unique_custom_id(p_birth_year INT, p_province_code VARCHAR(2))
RETURNS VARCHAR(15) AS $$
DECLARE
    new_id VARCHAR(15);
    id_exists BOOLEAN;
BEGIN
    LOOP
        -- 生成候选ID
        new_id := CONCAT(
            'M-', 
            p_birth_year, 
            '-', 
            UPPER(p_province_code), 
            '-', 
            LPAD(FLOOR(RANDOM() * 10000)::VARCHAR, 4, '0')
        );
        -- 检查ID是否已存在于users表
        SELECT EXISTS(SELECT 1 FROM users WHERE custom_id = new_id) INTO id_exists;
        -- 找到未使用的ID则退出循环
        IF NOT id_exists THEN
            EXIT;
        END IF;
    END LOOP;
    RETURN new_id;
END;
$$ LANGUAGE plpgsql VOLATILE;

插入数据时调用示例：

INSERT INTO users (birth_year, province_code, custom_id, username)
VALUES (1990, 'ON', generate_unique_custom_id(1990, 'ON'), 'melika02');

关键注意点 #

• 用UPPER()统一省份代码的大小写，避免因输入小写导致格式不一致
• 若需要调整末尾随机数字的位数，修改LPAD函数的第二个参数即可（比如要6位就改为LPAD(..., 6, '0')）
• 唯一ID生成逻辑在同一前缀下记录超过10000条时会进入死循环，此时需增加随机数字的位数

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