PostgreSQL分组内元素自增序号的Spring/Hibernate实现方案咨询

方案1：PostgreSQL触发器实现（最推荐，完全数据库层处理，无并发问题） #

建议避免使用group作为字段名，它是PostgreSQL保留关键字，以下示例改用group_name指代分组字段

步骤1：建表示例 #

CREATE TABLE group_items (
    id SERIAL PRIMARY KEY,
    group_name VARCHAR(32) NOT NULL,
    sequence INT NOT NULL
);

步骤2：创建触发器函数 #

CREATE OR REPLACE FUNCTION generate_group_sequence()
RETURNS TRIGGER AS $$
BEGIN
    -- 加FOR UPDATE锁避免并发场景下重复生成序列号
    SELECT COALESCE(MAX(sequence), 0) + 1 INTO NEW.sequence
    FROM group_items
    WHERE group_name = NEW.group_name
    FOR UPDATE;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤3：绑定触发器到表的插入事件 #

CREATE TRIGGER trg_group_sequence_generate
BEFORE INSERT ON group_items
FOR EACH ROW
EXECUTE FUNCTION generate_group_sequence();

插入数据时不需要指定sequence字段的值，数据库会自动生成对应分组内的递增序列号，示例插入语句：

INSERT INTO group_items (group_name) VALUES ('gr1'),('gr1'),('gr2'),('gr1'),('gr2'),('gr3');

执行查询即可得到你预期的分组递增效果。

方案2：Hibernate/Spring Data JPA 适配 #

如果配合Hibernate和Spring Data JPA使用，需要在实体类的sequence字段上添加注解，标识该字段由数据库插入时生成：

@Entity
@Table(name = "group_items")
public class GroupItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "group_name", nullable = false)
    private String groupName;

    // 标识该字段在插入时由数据库生成，Hibernate会在插入后自动回查填充值
    @Generated(GenerationTime.INSERT)
    @Column(name = "sequence", nullable = false, updatable = false)
    private Integer sequence;

    // 省略getter、setter
}

Spring Data JPA层面不需要做额外修改，直接调用save()方法保存实体即可，无需手动赋值sequence字段，保存完成后实体对象会自动携带数据库生成的序列号值。

注意事项 #

• 上述触发器方案添加了FOR UPDATE行锁，完全避免并发插入同分组数据时的序列号重复问题，性能满足绝大多数业务场景
• 如果业务允许序列号存在空洞，删除数据时无需额外处理；如果需要保证分组内序列号完全连续，可额外新增删除触发器调整后续序列号
• 若需要更高的插入性能，也可针对每个分组单独创建序列，但维护成本较高，仅建议TPS极高的场景使用

内容的提问来源于stack exchange，提问作者Andriy Van Dam