数据版本管理核心设计

采用双表结构设计：主表存储当前有效数据，历史表存储所有变更记录。主键采用自增ID+版本号组合，确保每条记录具有唯一标识。

// 数据库表结构示例
CREATE TABLE contract (
    id INT PRIMARY KEY AUTO_INCREMENT,
    version INT NOT NULL DEFAULT 1,
    contract_no VARCHAR(50) UNIQUE,
    content TEXT,
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE contract_history (
    history_id INT PRIMARY KEY AUTO_INCREMENT,
    contract_id INT NOT NULL,
    version INT NOT NULL,
    operation ENUM('CREATE','UPDATE','DELETE'),
    change_data JSON,
    modified_by VARCHAR(50),
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contract(id)
);

版本控制实现机制

建立触发器自动捕获变更，当主表发生INSERT/UPDATE/DELETE操作时，触发器将旧数据写入历史表。使用MySQL的JSON类型存储完整变更记录。

// Java实体类注解配置
@Entity
@Table(name = "contract")
@SQLDelete(sql = "UPDATE contract SET status = 'DELETED' WHERE id = ?")
@Where(clause = "status != 'DELETED'")
public class Contract {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Version
    private Integer version;
    
    @Column(name = "contract_no")
    private String contractNumber;
    
    @Column(columnDefinition = "TEXT")
    private String content;
    
    @Enumerated(EnumType.STRING)
    private ContractStatus status;
    
    @Column(name = "created_at", updatable = false)
    @CreationTimestamp
    private LocalDateTime createdAt;
    
    @Column(name = "updated_at")
    @UpdateTimestamp
    private LocalDateTime updatedAt;
}

历史记录查询功能

实现基于时间范围、操作类型、修改人的多维查询。使用Spring Data JPA的Specification构建动态查询条件。

public interface ContractHistoryRepository extends JpaRepository<ContractHistory, Long>, JpaSpecificationExecutor<ContractHistory> {
    
    @Query("SELECT h FROM ContractHistory h WHERE h.contractId = :contractId ORDER BY h.modifiedAt DESC")
    List<ContractHistory> findAllVersions(@Param("contractId") Long contractId);
    
    default List<ContractHistory> findHistoryByCriteria(Long contractId, String operator, LocalDateTime start, LocalDateTime end) {
        return findAll((root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            predicates.add(cb.equal(root.get("contractId"), contractId));
            
            if (operator != null) {
                predicates.add(cb.equal(root.get("modifiedBy"), operator));
            }
            if (start != null) {
                predicates.add(cb.greaterThanOrEqualTo(root.get("modifiedAt"), start));
            }
            if (end != null) {
                predicates.add(cb.lessThanOrEqualTo(root.get("modifiedAt"), end));
            }
            
            return cb.and(predicates.toArray(new Predicate[0]));
        });
    }
}

数据版本对比功能

实现差异对比算法，使用google-diff-match-patch库生成可视化差异报告。在服务层实现版本回滚逻辑。

@Service
public class ContractVersionService {
    private final DiffMatchPatch diffTool = new DiffMatchPatch();
    
    public String compareVersions(Long contractId, Integer version1, Integer version2) {
        ContractHistory v1 = historyRepo.findByContractIdAndVersion(contractId, version1);
        ContractHistory v2 = historyRepo.findByContractIdAndVersion(contractId, version2);
        
        LinkedList<DiffMatchPatch.Diff> diffs = diffTool.diffMain(
            v1.getChangeData().get("content").asText(),
            v2.getChangeData().get("content").asText()
        );
        
        diffTool.diffCleanupSemantic(diffs);
        return diffTool.diffPrettyHtml(diffs);
    }
    
    @Transactional
    public void rollbackVersion(Long contractId, Integer targetVersion) {
        ContractHistory history = historyRepo.findByContractIdAndVersion(contractId, targetVersion);
        Contract current = contractRepo.findById(contractId).orElseThrow();
        
        current.setContent(history.getChangeData().get("content").asText());
        current.setVersion(current.getVersion() + 1);
        
        contractRepo.save(current);
    }
}

性能优化策略

对历史表进行分区处理，按时间范围水平分割数据。建立复合索引提升查询效率，定期归档冷数据。

ALTER TABLE contract_history PARTITION BY RANGE (UNIX_TIMESTAMP(modified_at)) (
    PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
    PARTITION p2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

CREATE INDEX idx_contract_history ON contract_history (contract_id, version);
CREATE INDEX idx_modified_info ON contract_history (modified_by, modified_at);

事务与一致性保证

采用Spring的声明式事务管理，确保主表与历史表的操作原子性。实现乐观锁控制并发修改。

@Transactional
public Contract updateContract(Long id, ContractUpdateRequest request) {
    Contract contract = contractRepo.findById(id)
        .orElseThrow(() -> new ResourceNotFoundException("Contract not found"));
    
    // 保存当前状态到历史记录
    ContractHistory history = new ContractHistory();
    history.setContractId(contract.getId());
    history.setVersion(contract.getVersion());
    history.setOperation("UPDATE");
    history.setChangeData(convertToJson(contract));
    history.setModifiedBy(getCurrentUser());
    
    historyRepo.save(history);
    
    // 更新当前合同
    contract.setContent(request.getContent());
    contract.setStatus(request.getStatus());
    contract.setVersion(contract.getVersion() + 1);
    
    return contractRepo.save(contract);
}