Spring Boot+SQL场景下数组属性持久化与更新最佳实践咨询

原有简单属性结构为property1{id: 1, value: 'simplevalue'}，value为字符串类型，SQL操作逻辑简单。现在需支持数组类型属性，示例为property2{id:1, value:['value1', 'value2']}，要求数据库按单值行存储（即一个属性对应多行，每行存一个value），且持久化实体类的value必须保持字符串类型，不可使用数组类型存储。

两种方案优劣对比 #

方案1：全删全插 #

• 逻辑：检测到属性变更后，先删除该property_id对应的所有旧记录，再批量插入新数组对应的所有单值记录
• 优点：代码实现简单，无需做新旧值比对，不会出现比对遗漏导致的脏数据
• 缺点：当数组元素量大时，会产生不必要的IO操作，数据库行主键频繁变动，若有其他表关联该属性行主键会出现异常

方案2：增量差量更新 #

• 逻辑：先查询该property_id对应的所有旧值集合，和新提交的数组值做差集比对，分别得到待删除的旧值（旧有但新数组不存在）和待新增的新值（新数组有但旧值没有），仅执行这两部分的删改操作，重叠部分的原值不做处理
• 优点：写入操作量最小，数据库行主键稳定，适合元素变更少、存在外键关联的场景，性能更优
• 缺点：需要额外做集合比对，逻辑稍复杂，无事务控制时可能出现删增不同步的脏数据

最优方案选择建议 #

根据实际业务场景二选一即可：

• 如果单属性对应的数组元素量少于20个、无其他表关联该属性行主键：优先选择全删全插方案，代码维护成本极低，出错概率小，性能损耗完全可以忽略
• 如果单属性对应的数组元素量大、存在外键关联该属性行、更新频率高且每次仅变更少量元素：优先选择增量更新方案，注意要将删除和新增操作放在同一个数据库事务中，避免并发更新导致的数据不一致

实现注意事项 #

• 无论选哪种方案，所有更新操作都必须加事务控制，避免中途执行失败导致属性值不全
• 建议给数据库表的(property_id, value)字段加联合唯一索引，既可以避免同一属性下出现重复值，也能提升差量比对的查询效率
• 可以在实体类中新增一个非持久化的数组类型字段接收前端传参，持久化处理时再拆分为单值字符串的实体对象，符合「实体类持久化value保持字符串类型」的约束

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