PostgreSQL中按用户生成独立自增ID序列的实现问询

原触发器的问题 #

你之前的触发器函数有三个核心问题导致未达预期：

1. 未返回NEW值：PL/pgSQL的BEFORE INSERT触发器必须返回NEW才能让插入操作生效，原函数无返回值，相当于直接取消了插入。
2. 空值处理缺失：当用户没有任何笔记时，MAX(id)返回NULL，加1后还是NULL，违反id NOT NULL约束。
3. 并发冲突风险：多个请求同时插入同一用户的笔记时，会同时读取到相同的MAX(id)，导致生成重复ID触发主键冲突。

解决方案一：修复触发器函数（适合小数据量场景） #

修改触发器函数，补充返回值、空值处理，并添加行级锁避免并发问题：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION note_id() RETURNS trigger AS
$$
BEGIN
    -- 用COALESCE处理用户无记录的情况，将NULL转为0后加1
    NEW.id = COALESCE((SELECT MAX(id) FROM notes WHERE user_id = NEW.user_id), 0) + 1;
    -- 锁定该用户的所有笔记，避免并发插入时重复生成ID
    PERFORM 1 FROM notes WHERE user_id = NEW.user_id FOR UPDATE;
    -- 必须返回NEW，否则插入操作会被取消
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

CREATE TRIGGER trigger_note_id
BEFORE INSERT ON notes
FOR EACH ROW
EXECUTE FUNCTION note_id();

解决方案二：用单独序列表优化（适合大数据量场景） #

当用户笔记数量较多时，MAX(id)查询效率会下降，建议用单独的表存储每个用户的当前最大ID，利用UPDATE的原子性避免并发问题：

1. 创建序列记录表 #

CREATE TABLE user_note_seq (
    user_id INT PRIMARY KEY,
    current_id INT NOT NULL DEFAULT 0
);

2. 创建触发器函数 #

CREATE OR REPLACE FUNCTION note_id() RETURNS trigger AS
$$
BEGIN
    -- 尝试更新用户的当前ID，原子操作自动锁行
    UPDATE user_note_seq 
    SET current_id = current_id + 1 
    WHERE user_id = NEW.user_id;
    
    -- 如果用户不存在，则插入初始值1
    IF NOT FOUND THEN
        INSERT INTO user_note_seq (user_id, current_id)
        VALUES (NEW.user_id, 1);
        NEW.id = 1;
    ELSE
        -- 获取更新后的当前ID
        SELECT current_id INTO NEW.id FROM user_note_seq WHERE user_id = NEW.user_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建触发器 #

CREATE TRIGGER trigger_note_id
BEFORE INSERT ON notes
FOR EACH ROW
EXECUTE FUNCTION note_id();

解决方案三：查询时生成展示ID（无需修改存储逻辑） #

如果只是在展示给用户时需要从1开始的编号，完全不需要存储这个专属ID，直接在查询时用窗口函数生成即可，这样既避免了并发问题，又简化了存储逻辑：

修改表结构（用全局自增ID） #

-- 先删除原表（若已有数据需先备份）
DROP TABLE notes;

-- 创建新表，用SERIAL生成全局唯一ID
CREATE TABLE notes (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    text VARCHAR NOT NULL,
    -- 为user_id建索引，提升查询效率
    INDEX idx_notes_user_id (user_id)
);

查询时生成用户专属编号 #

SELECT 
    id AS global_id, -- 全局唯一ID
    user_id,
    text,
    -- 按用户分组，按全局ID排序生成专属编号
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS user_note_id
FROM notes
WHERE user_id = 你的用户ID;

这种方法最适合Telegram机器人的展示场景，既保证了数据存储的简洁性，又能满足用户看到专属编号的需求。

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