PostgreSQL多对多表更新触发唯一约束冲突时自动去重的实现方法

你可以用「先插入去重后的目标关联，再删除旧关联」的逻辑实现需求，全程仅需2条SQL，无需临时表，适配所有主流数据库：

核心逻辑 #

1. 先将需要修改的permit_id对应的所有role_id，和新permit_id组成新的关联对插入表中，插入时自动跳过已存在的重复关联
2. 再删除所有旧permit_id对应的关联行即可

不同数据库实现示例 #

MySQL #

-- 插入新关联，重复自动忽略
INSERT IGNORE INTO permit_role (permit_id, role_id)
SELECT 'permit_5', role_id FROM permit_role
WHERE permit_id IN ('permit_1', 'permit_2');

-- 删除旧关联
DELETE FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');

PostgreSQL #

-- 插入新关联，冲突不处理
INSERT INTO permit_role (permit_id, role_id)
SELECT 'permit_5', role_id FROM permit_role
WHERE permit_id IN ('permit_1', 'permit_2')
ON CONFLICT (permit_id, role_id) DO NOTHING;

-- 删除旧关联
DELETE FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');

SQL Server #

-- 插入不存在的新关联
INSERT INTO permit_role (permit_id, role_id)
SELECT DISTINCT 'permit_5', role_id FROM permit_role t1
WHERE permit_id IN ('permit_1', 'permit_2')
AND NOT EXISTS (
    SELECT 1 FROM permit_role t2 
    WHERE t2.permit_id = 'permit_5' AND t2.role_id = t1.role_id
);

-- 删除旧关联
DELETE FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');

注意事项 #

生产环境操作建议包裹在事务中，验证数据无误后再提交，示例如下：

START TRANSACTION;
-- 校验需要插入的新关联是否正确
SELECT 'permit_5' AS new_permit_id, role_id FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');
-- 执行插入
INSERT IGNORE INTO permit_role (permit_id, role_id)
SELECT 'permit_5', role_id FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');
-- 执行删除
DELETE FROM permit_role WHERE permit_id IN ('permit_1', 'permit_2');
-- 确认最终结果符合预期后再执行COMMIT
-- COMMIT;

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