MySQL的ON DUPLICATE KEY UPDATE在PostgreSQL中的等价实现问询

直接给出结论：PostgreSQL的ON CONFLICT ... DO UPDATE就是ON DUPLICATE KEY UPDATE的直接等价实现，你觉得繁琐大概率是对语法写法存在误解，不需要重写整个INSERT语句为UPDATE语句，两者的结构几乎一致。

语法对比示例 #

MySQL原生写法 #

-- 单条插入场景
INSERT INTO users (id, name, email, login_count)
VALUES (1, '张三', 'zhangsan@example.com', 1)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email),
    login_count = login_count + 1;

PostgreSQL等价写法 #

INSERT INTO users (id, name, email, login_count)
VALUES (1, '张三', 'zhangsan@example.com', 1)
-- 仅需额外指定触发冲突的字段（要求该字段有主键/唯一约束）
ON CONFLICT (id) DO UPDATE 
SET 
    -- EXCLUDED 等价于MySQL中的VALUES()，指代原本要插入的行数据
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    login_count = users.login_count + 1;

批量插入场景兼容 #

两者的批量插入写法也几乎没有差异：

MySQL批量插入写法 #

INSERT INTO users (id, name, email, login_count)
VALUES (1, '张三', 'zhangsan@example.com', 1), (2, '李四', 'lisi@example.com', 1)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email),
    login_count = login_count + 1;

PostgreSQL批量插入等价写法 #

INSERT INTO users (id, name, email, login_count)
VALUES (1, '张三', 'zhangsan@example.com', 1), (2, '李四', 'lisi@example.com', 1)
ON CONFLICT (id) DO UPDATE 
SET 
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    login_count = users.login_count + 1;

额外说明 #

PostgreSQL要求在ON CONFLICT后显式指定冲突字段/约束名，是为了避免表存在多个唯一约束时出现逻辑歧义，实际反而提升了语句的确定性和可读性。如果是联合唯一约束的场景，也可以替换为ON CONFLICT ON CONSTRAINT 联合约束名 DO UPDATE的写法。

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