金额敏感表行全版本存储的最优数据库结构设计咨询

针对金额敏感数据的全版本存储需求，我在类似金融审计场景里做过不少实践，给你梳理几个可行的方案和关键细节：

首先要锚定核心目标：所有金额变更操作必须保留历史痕迹，绝不修改原有记录，这是合规性和审计追溯的核心要求，所有设计都要围绕这个点展开。

方案1：复合主键（业务ID + 版本号） #

这是你提到的初始方案，直接把业务ID和版本号组合成主键，结构示例：

CREATE TABLE amount_sensitive (
    id INT NOT NULL, -- 原业务主键
    version INT NOT NULL, -- 版本号，从1开始递增
    amount DECIMAL(19,4) NOT NULL, -- 敏感金额字段
    other_columns VARCHAR(255), -- 其他业务字段
    change_reason VARCHAR(255) NOT NULL, -- 变更原因，审计必备
    operated_by VARCHAR(50) NOT NULL, -- 操作人
    operated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 操作时间
    PRIMARY KEY (id, version)
);

优点 #

• 逻辑直观：直接绑定业务主体和版本，查询历史版本时用id + version就能精准定位
• 无冗余字段：复合主键天然保证同一业务ID下版本号唯一，不需要额外的唯一约束

缺点 #

• 外键关联麻烦：如果其他表需要关联这条记录，必须同时存储id和version两个字段，增加关联复杂度
• 版本号需手动维护：插入新版本时要先查询当前最大版本号再加1，高并发下要注意加锁避免冲突

方案2：独立自增主键 + 业务ID + 版本号 #

给每条历史记录分配独立的存储主键，同时保留业务ID和版本号的唯一约束，结构示例：

CREATE TABLE amount_sensitive (
    record_id INT AUTO_INCREMENT PRIMARY KEY, -- 独立存储主键
    id INT NOT NULL, -- 原业务主键
    version INT NOT NULL, -- 版本号
    amount DECIMAL(19,4) NOT NULL,
    other_columns VARCHAR(255),
    change_reason VARCHAR(255) NOT NULL,
    operated_by VARCHAR(50) NOT NULL,
    operated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_current BOOLEAN DEFAULT false, -- 标记是否为当前生效版本
    UNIQUE KEY uk_id_version (id, version) -- 保证同一业务ID下版本唯一
);

优点 #

• 关联灵活：用record_id作为外键关联其他表更简单，不需要传递多个字段
• 扩展性强：后续新增索引、扩展字段时，独立主键不会限制业务逻辑
• 快速查当前版本：通过is_current字段可以直接定位最新生效的记录，无需每次查最大版本号

缺点 #

• 略有冗余：多了一个自增主键字段，但在现代数据库中对性能影响可以忽略

不管选哪种方案，这些细节直接决定方案的可用性：

• 版本号生成策略：
  • 推荐用整数递增（1,2,3...），直观易追溯
  • 高并发下不要在应用层查询版本号，建议用数据库原子操作：INSERT ... SELECT MAX(version)+1 FROM ... WHERE id=? FOR UPDATE，或者用触发器自动生成
• 事务保证原子性：
  • 如果用is_current标记当前版本，更新时必须在事务中完成：先把旧版本的is_current设为false，再插入新版本并设为true，避免出现多个"当前版本"
• 索引优化：
  • 给id单独建索引，方便按业务ID查询所有历史版本
  • 给operated_at建索引，支持按时间范围追溯变更记录
• 合规性要求：
  • change_reason、operated_by、operated_at这三个字段必须强制非空，这是审计的核心依据，缺一不可

• 如果你的系统很少需要跨表关联，或者关联场景只需要业务ID+版本号，选方案1更简洁
• 如果你的系统有较多跨表关联需求，或者后续可能有扩展计划，选方案2更灵活
• 高并发场景下，优先用数据库的原子操作生成版本号，避免应用层并发导致的版本冲突

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