求PostgreSQL存储过程：生成递增字母数字序列及获取下一个ID

核心思路 #

这类ID本质是36进制序列（0-9对应0-9，A-Z对应10-35），遵循从右往左的进位规则。实现逻辑分为三步：

1. 将输入ID的每个字符转换为对应数值
2. 整体视为36进制数执行加1操作
3. 将计算后的数值转回字符格式，保持原长度（若最高位进位则自动扩展长度）

实现代码 #

1. 字符与数值互转辅助函数 #

-- 字符转数值：0-9→0-9，A-Z→10-35（支持大小写输入）
CREATE OR REPLACE FUNCTION char_to_val(c CHAR) RETURNS INTEGER AS $$
BEGIN
    RETURN CASE
        WHEN c ~ '^[0-9]$' THEN c::INTEGER
        WHEN c ~ '^[A-Z]$' THEN ASCII(c) - ASCII('A') + 10
        WHEN c ~ '^[a-z]$' THEN ASCII(c) - ASCII('a') + 10
        ELSE RAISE EXCEPTION '无效字符: %', c;
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 数值转字符：0-9→0-9，10-35→A-Z
CREATE OR REPLACE FUNCTION val_to_char(v INTEGER) RETURNS CHAR AS $$
BEGIN
    RETURN CASE
        WHEN v BETWEEN 0 AND 9 THEN v::CHAR
        WHEN v BETWEEN 10 AND 35 THEN CHR(v - 10 + ASCII('A'))
        ELSE RAISE EXCEPTION '无效数值: %', v;
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

2. 生成下一个ID的主函数 #

该函数可直接接收已有ID，返回对应的下一个ID：

CREATE OR REPLACE FUNCTION next_id(current_id TEXT) RETURNS TEXT AS $$
DECLARE
    id_length INTEGER := LENGTH(current_id);
    val_array INTEGER[];
    carry INTEGER := 1; -- 初始进位为1，对应加1操作
    i INTEGER;
    current_val INTEGER;
BEGIN
    -- 将ID字符逐个转为数值，存入数组（左到右对应高位到低位）
    FOR i IN 1..id_length LOOP
        val_array[i] := char_to_val(SUBSTRING(current_id FROM i FOR 1));
    END LOOP;

    -- 从右往左处理进位
    FOR i IN REVERSE id_length..1 LOOP
        current_val := val_array[i] + carry;
        IF current_val >= 36 THEN
            val_array[i] := current_val - 36;
            carry := 1;
        ELSE
            val_array[i] := current_val;
            carry := 0;
            EXIT; -- 无进位时提前结束循环
        END IF;
    END LOOP;

    -- 最高位仍有进位时，自动扩展ID长度（如ZZZ→AAAA）
    IF carry = 1 THEN
        val_array := ARRAY[1] || val_array;
        id_length := id_length + 1;
    END IF;

    -- 将数值数组转回字符格式
    RETURN ARRAY_TO_STRING(ARRAY(SELECT val_to_char(v) FROM UNNEST(val_array) v), '');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

3. 批量生成序列的存储过程 #

如果需要批量生成连续ID，可使用该存储过程：

CREATE OR REPLACE PROCEDURE generate_id_sequence(start_id TEXT, count INTEGER, OUT id_list TEXT[]) AS $$
DECLARE
    current_id TEXT := start_id;
    i INTEGER;
BEGIN
    id_list := '{}'::TEXT[];
    FOR i IN 1..count LOOP
        id_list := id_list || current_id;
        current_id := next_id(current_id);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

使用示例 #

• 获取单个ID的下一个值：

  SELECT next_id('AXZ'); -- 返回 'AY0'
  SELECT next_id('AZZ'); -- 返回 'B00'
  SELECT next_id('AST'); -- 返回 'ASU'
  SELECT next_id('23X'); -- 返回 '23Y'
  

• 生成10个连续ID：

  CALL generate_id_sequence('AX8', 10, @ids);
  SELECT unnest(@ids);
  -- 输出：AX8, AX9, AXA, AXB, AXC, AXD, AXE, AXF, AXg, AXH
  

注意事项 #

• 支持大小写不敏感的输入（如输入'axz'也能正确返回'AY0'）
• 若输入包含非0-9/A-Z的字符，会直接抛出异常
• 当ID达到当前长度的最大值（如ZZZ）时，会自动扩展长度（返回AAAA）

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