如何在PostgreSQL与Node.js环境下生成带指定前缀的自增唯一ID？

这里提供3种不需要多辅助表的落地方案，你可以根据业务场景选择： #

方案1：单表统一管理所有前缀序列 #

只需要1张全局序列表即可支持任意数量的前缀，完全不需要为每个前缀单独建表，且原子操作可避免并发重复问题：

1. 建序列管理表

   CREATE TABLE prefix_sequence (
       prefix VARCHAR(32) PRIMARY KEY,
       current_seq INT NOT NULL DEFAULT 0
   );
   

2. 创建ID生成函数

   CREATE OR REPLACE FUNCTION get_next_id(p_prefix VARCHAR(32))
   RETURNS VARCHAR AS $$
   DECLARE
       next_seq INT;
   BEGIN
       -- 前缀不存在则初始化，存在则原子累加序号
       INSERT INTO prefix_sequence (prefix, current_seq)
       VALUES (p_prefix, 1)
       ON CONFLICT (prefix) DO UPDATE
       SET current_seq = prefix_sequence.current_seq + 1
       RETURNING current_seq INTO next_seq;
       
       RETURN p_prefix || '-' || next_seq::VARCHAR;
   END;
   $$ LANGUAGE plpgsql VOLATILE;
   

3. 使用方式：直接调用函数即可，Node.js侧或SQL中都可直接执行

   SELECT get_next_id('pm'); -- 首次调用返回pm-1，第二次返回pm-2
   SELECT get_next_id('ad'); -- 首次调用返回ad-1，和pm前缀的序号完全独立
   

这个方案通用性最强，单张轻量表可以支撑上万种前缀的序列管理，并发安全性高，适合90%以上的业务场景。

方案2：动态创建PostgreSQL内置序列 #

完全不需要额外建表，直接用PG原生的序列对象实现，性能比方案1更高：

CREATE OR REPLACE FUNCTION get_next_id(p_prefix VARCHAR(32))
RETURNS VARCHAR AS $$
DECLARE
    seq_name VARCHAR := 'seq_' || p_prefix;
    next_seq INT;
BEGIN
    -- 前缀对应的序列不存在则自动创建
    IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name AND relkind = 'S') THEN
        EXECUTE format('CREATE SEQUENCE %I START WITH 1 INCREMENT BY 1', seq_name);
    END IF;
    -- 取序列的下一个值
    EXECUTE format('SELECT nextval(%L)', seq_name) INTO next_seq;
    RETURN p_prefix || '-' || next_seq::VARCHAR;
END;
$$ LANGUAGE plpgsql VOLATILE;

适合前缀种类不多（小于1000种）、对性能要求极高的场景。

方案3：无额外表的Node.js事务实现 #

不需要在数据库创建任何额外表或函数，直接在业务层用事务实现，适合并发量不高的场景：

// 基于pg库实现，your_business_table替换为你存ID的业务表名
async function getNextId(prefix) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // 加排他锁避免并发事务拿到同一个最大值
    const res = await client.query(
      `SELECT MAX(CAST(split_part(id, '-', 2) AS INTEGER)) AS max_seq 
       FROM your_business_table 
       WHERE id LIKE $1 FOR UPDATE`,
      [`${prefix}-%`]
    );
    const nextSeq = (res.rows[0].max_seq || 0) + 1;
    const newId = `${prefix}-${nextSeq}`;
    // 此处可插入后续的业务数据写入逻辑
    await client.query('COMMIT');
    return newId;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

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