PostgreSQL如何实现每个用户独立自增的message_id功能？

PostgreSQL和Go层面都可以实现，优先推荐数据库触发器方案，天然保证数据一致性，不需要额外处理应用层并发问题。

注意前置修改 #

user是PostgreSQL的保留关键字，直接用作字段名会触发语法错误，建议你把该字段重命名为username或者user_id，下文示例统一使用username指代该字段。

方案1：PostgreSQL 触发器实现（推荐） #

步骤1：创建表（含唯一约束） #

先给表加(username, message_id)联合唯一约束，避免极端并发下出现重复序号：

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    message_id INT NOT NULL,
    message TEXT NOT NULL,
    -- 保证同一个用户的message_id不会重复
    CONSTRAINT unique_user_message_id UNIQUE (username, message_id)
);

步骤2：创建序号自动生成触发器函数 #

CREATE OR REPLACE FUNCTION generate_user_message_id()
RETURNS TRIGGER AS $$
BEGIN
    -- 取当前用户的最大序号+1，用户第一条消息默认取1
    SELECT COALESCE(MAX(message_id), 0) + 1
    INTO NEW.message_id
    FROM messages
    WHERE username = NEW.username;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;

步骤3：绑定插入前触发器 #

CREATE TRIGGER trigger_messages_before_insert
BEFORE INSERT ON messages
FOR EACH ROW
EXECUTE FUNCTION generate_user_message_id();

使用方法 #

插入数据时不需要手动传message_id，数据库会自动生成：

INSERT INTO messages (username, message)
VALUES 
('one', 'Hello'),
('two', 'Hi!'),
('one', 'How are you?'),
('one', ':)');

查询结果和你给出的示例完全一致。

方案2：Go 应用层实现 #

如果需要在业务层自定义序号生成规则，可以在Go代码中处理，注意要解决并发插入的重复问题：

步骤1：事务内查询并生成序号 #

用事务+行锁保证并发下不会拿到重复的序号：

// 开启事务
tx, err := db.Begin()
if err != nil {
    return err
}
defer tx.Rollback()

// 查询对应用户的下一个序号，加FOR UPDATE行锁避免并发冲突
var nextMsgID int
err = tx.QueryRow(`SELECT COALESCE(MAX(message_id), 0) + 1 FROM messages WHERE username = $1 FOR UPDATE`, username).Scan(&nextMsgID)
if err != nil {
    return err
}

// 插入数据
_, err = tx.Exec(`INSERT INTO messages (username, message_id, message) VALUES ($1, $2, $3)`, username, nextMsgID, message)
if err != nil {
    return err
}

// 提交事务
return tx.Commit()

高并发场景下可以配合Redis分布式锁进一步优化性能。

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