MySQL 8中文档多版本管理：当前活跃版本的最优判定方案？

问题背景 #

我们需要存储文档的所有版本，核心要求如下：

• 支持乱序接收版本（如先接收版本3再接收版本2）
• 支持并发插入，且(identifier, version)组合不可重复
• 需快速检索每个文档的最后/当前/活跃版本
  当前使用MySQL 8数据库。

两种候选方案 #

方案1：新增active标记列 #

核心设计

为表添加唯一约束：unique constraint (identifier, CASE WHEN active = 1 THEN 1 ELSE NULL END)，确保同一identifier下仅存在1条活跃记录。示例数据如下：

active列的管理方式

• 应用层处理：插入前判断待插入版本是否为当前活跃版本，若是则先将同identifier下现有活跃记录置为非活跃
• 数据库触发器（after insert）：自动执行上述置非活跃操作

弊端

并发插入同一identifier的不同版本时，可能出现两条记录同时被标记为活跃，触发唯一约束报错。应用层处理该并发冲突难度大，会影响插入稳定性。

方案2：查询时动态判定活跃版本 #

通过SQL逻辑动态识别每个identifier的最高版本（即活跃版本），两种实现方式如下：

窗口函数实现

SELECT ... 
FROM (
    SELECT ..., 
           CASE WHEN version = MAX(version) OVER (PARTITION BY identifier) THEN 1 ELSE 0 END AS active 
    FROM Document
) d 
WHERE d.active = 1

NOT EXISTS子句实现

SELECT ... 
FROM Document d 
WHERE NOT EXISTS (
    SELECT 1 FROM Document x 
    WHERE d.identifier = x.identifier AND x.version > d.version
)

顾虑

• 性能问题：动态计算会增加查询开销
• 代码冗余：查询活跃版本的逻辑需重复编写，即使使用视图也需评估其效率
• 典型查询复杂度高：例如查询特定客户的所有活跃文档，方案1只需简单过滤active = 1 and customer_id = 123，方案2的实现逻辑会复杂很多。

困惑与需求 #

• 该场景是否有通用技术术语，便于搜索更多方案及权衡分析？
• 业内针对此类需求是否有普遍推荐的解决方案？

方案对比总结 #

1. 方案1：查询活跃版本便捷高效，但active列的并发管理复杂，唯一约束易引发插入失败
2. 方案2：插入无冲突风险，但查询性能差、逻辑冗余，复杂查询实现难度高

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