如何实现PostgreSQL复合主键中按note_id自动递增version列？

核心需求：构建note_history表，以note_id和version作为复合主键，确保每个note_id对应的version从1开始自动递增，每次新增该笔记的历史记录时version自动+1。

PostgreSQL 实现步骤 #

1. 创建基础表结构 #

CREATE TABLE note_history (
    note_id INT NOT NULL,
    version INT NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (note_id, version)
);

2. 创建触发器函数 #

该函数负责为新插入的记录计算对应的version值：

CREATE OR REPLACE FUNCTION set_note_version()
RETURNS TRIGGER AS $$
BEGIN
    -- 若该note_id已有记录，取最大version+1；否则设为1
    NEW.version = COALESCE((SELECT MAX(version) + 1 FROM note_history WHERE note_id = NEW.note_id), 1);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 创建绑定触发器 #

让触发器在插入记录前自动调用上述函数：

CREATE TRIGGER trigger_set_note_version
BEFORE INSERT ON note_history
FOR EACH ROW
EXECUTE FUNCTION set_note_version();

测试验证 #

插入测试数据时无需手动指定version：

INSERT INTO note_history (note_id, content) VALUES (1, '初始笔记内容');
INSERT INTO note_history (note_id, content) VALUES (1, '第一次修改内容');
INSERT INTO note_history (note_id, content) VALUES (2, '新笔记初始内容');

查询表数据会看到每个note_id的version按1、2、3...依次递增。

MySQL 实现步骤 #

1. 创建基础表结构 #

CREATE TABLE note_history (
    note_id INT NOT NULL,
    version INT NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (note_id, version)
);

2. 创建自动设置version的触发器 #

DELIMITER //
CREATE TRIGGER trigger_set_note_version
BEFORE INSERT ON note_history
FOR EACH ROW
BEGIN
    SELECT COALESCE(MAX(version) + 1, 1) INTO NEW.version
    FROM note_history
    WHERE note_id = NEW.note_id;
END //
DELIMITER ;

测试验证 #

同样无需手动指定version：

INSERT INTO note_history (note_id, content) VALUES (1, 'MySQL初始笔记');
INSERT INTO note_history (note_id, content) VALUES (1, 'MySQL修改笔记');
INSERT INTO note_history (note_id, content) VALUES (3, 'MySQL新笔记');

并发场景优化 #

如果存在多请求同时插入同一note_id记录的情况，可能出现version重复问题。可以通过加锁避免：

• PostgreSQL 修改触发器函数，添加行锁：

CREATE OR REPLACE FUNCTION set_note_version()
RETURNS TRIGGER AS $$
BEGIN
    SELECT COALESCE(MAX(version) + 1, 1) INTO NEW.version
    FROM note_history
    WHERE note_id = NEW.note_id
    FOR UPDATE; -- 锁定该note_id的所有记录，防止并发修改
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• MySQL 可在事务中执行插入操作，利用InnoDB的行锁机制避免冲突。

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