基于object_id生成分组序列列（PostgreSQL+Spring Data JPA）

一、数据库迁移脚本（PostgreSQL） #

1. 新增number列 #

先给目标表添加number字段：

ALTER TABLE your_table_name ADD COLUMN number INTEGER;

2. 批量填充现有数据 #

利用PostgreSQL窗口函数ROW_NUMBER()，按object_id分组、指定排序规则生成序列号，批量更新现有数据，这比逐行更新效率高几个量级：

WITH ranked_docs AS (
    SELECT 
        id,
        -- 按object_id分组，按id排序生成组内序列号（如需按document_name排序，替换ORDER BY id即可）
        ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY id) AS rn
    FROM your_table_name
)
UPDATE your_table_name t
SET number = rd.rn
FROM ranked_docs rd
WHERE t.id = rd.id;

二、Java代码生成新数据的number值 #

方式1：原生SQL原子插入（推荐） #

直接通过原生SQL在数据库层面完成编号计算，避免Java层的并发问题：

@Repository
public interface DocumentRepository extends JpaRepository<Document, String> {
    @Query(value = "INSERT INTO your_table_name (object_id, document_name, number) " +
                   "VALUES (:objectId, :documentName, " +
                   "(COALESCE((SELECT MAX(number) FROM your_table_name WHERE object_id = :objectId), 0) + 1)) " +
                   "RETURNING id", nativeQuery = true)
    String insertDocument(@Param("objectId") Long objectId, @Param("documentName") String documentName);
}

COALESCE用于处理该object_id无数据的场景，默认返回0，加1后得到初始编号1。

方式2：悲观锁控制并发 #

如果需要在Java层处理逻辑，可通过悲观锁避免并发插入时的编号重复：

@Repository
public interface DocumentRepository extends JpaRepository<Document, String> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("SELECT MAX(d.number) FROM Document d WHERE d.objectId = :objectId")
    Integer findMaxNumberByObjectId(@Param("objectId") Long objectId);
}

// 业务层调用
@Service
public class DocumentService {
    @Autowired
    private DocumentRepository documentRepository;

    public Document createDocument(Long objectId, String documentName) {
        Integer maxNum = documentRepository.findMaxNumberByObjectId(objectId);
        Integer newNum = maxNum == null ? 1 : maxNum + 1;
        
        Document doc = new Document();
        doc.setObjectId(objectId);
        doc.setDocumentName(documentName);
        doc.setNumber(newNum);
        return documentRepository.save(doc);
    }
}

方式3：数据库触发器（彻底解放Java层） #

如果不想在代码中处理编号逻辑，可通过PostgreSQL触发器自动生成number：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION assign_document_number()
RETURNS TRIGGER AS $$
BEGIN
    NEW.number = COALESCE((SELECT MAX(number) FROM your_table_name WHERE object_id = NEW.object_id), 0) + 1;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器：

CREATE TRIGGER trigger_assign_document_number
BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE FUNCTION assign_document_number();

之后Java代码只需插入object_id和document_name，number会由数据库自动生成，并发场景下可靠性更高。

三、注意事项 #

• 迁移脚本建议在业务低峰期执行，避免影响线上服务；
• 数据量较大时，窗口函数批量更新的效率远高于逐行更新；
• 并发场景下，优先选择数据库触发器或原生SQL原子插入方案，避免编号重复问题。

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