更新存储Project表关联所有者信息的独立Owners SQL表的最优方法

方案1：前端做差量计算（效率最高） #

当前你的前端流程已经会先拉取指定项目的现有所有者列表，可直接在前端侧计算出两类变更集合，提交接口时只传这两个集合，无需传递全量所有者列表：

• 待删除集合：原有列表存在、新列表不存在的user_id
• 待新增集合：原有列表不存在、新列表存在的user_id

后端收到后执行两步数据库操作即可：

1. 执行删除语句：DELETE FROM Owners WHERE proj_id = <目标项目ID> AND user_id IN (<待删除user_id列表>)
2. 执行批量插入语句，注意配合唯一索引做冲突规避：
   • MySQL写法：INSERT INTO Owners (user_id, proj_id) VALUES (<user1>, <proj_id>), (<user2>, <proj_id>) ... ON DUPLICATE KEY UPDATE id = id
   • PostgreSQL写法：INSERT INTO Owners (user_id, proj_id) VALUES (<user1>, <proj_id>), (<user2>, <proj_id>) ... ON CONFLICT (user_id, proj_id) DO NOTHING

该方案的操作量只和变更的条目数挂钩，和项目总所有者数量无关，是最优解。

方案2：后端做差量处理（无需修改前端） #

如果无法调整前端逻辑，只能接收全量的最新所有者列表，也不需要全删全插，按以下两步执行即可：

1. 删除失效的旧所有者：

DELETE FROM Owners
WHERE proj_id = <目标项目ID>
AND user_id NOT IN (<最新提交的全量user_id列表>);

2. 插入新增所有者，自动跳过已存在的条目，SQL写法和方案1的插入语句一致。

注意事项 #

• 必须先给Owners表添加(proj_id, user_id)联合唯一索引，既可以避免重复的关联数据，也能大幅提升上述删除、插入语句的执行效率，即使项目有上百个所有者，操作也可以在毫秒级完成。
• 建议将删除、插入操作放在同一个数据库事务中执行，避免中间状态出错导致数据不一致。
• 相比全删全插方案，差量处理不存在项目所有者临时为空的中间态，也不会触发大量的IO操作，性能和稳定性都高很多。

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