Postgre SQL数据库实现有记录则更新无记录就新增的方法

# PostgreSQL数据库实现有记录则更新无记录就新增的方法

## 引言

在数据库操作中，"有记录则更新，无记录则新增"（通常称为"upsert"操作）是一个常见需求。PostgreSQL提供了多种方式实现这一功能，本文将详细介绍三种主流方法：`ON CONFLICT`子句、可写CTE以及函数/存储过程实现。

## 方法一：ON CONFLICT子句（推荐）

### 基本语法

```sql
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target) 
DO UPDATE SET column1 = excluded.column1, ...;

实际案例

假设有用户表users，主键为user_id：

-- 创建测试表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    last_login TIMESTAMP,
    login_count INT DEFAULT 0
);

-- Upsert操作
INSERT INTO users (user_id, username, last_login, login_count)
VALUES (1, 'john_doe', NOW(), 1)
ON CONFLICT (user_id) 
DO UPDATE SET 
    last_login = EXCLUDED.last_login,
    login_count = users.login_count + 1;

注意事项

1. conflict_target必须是唯一约束或主键
2. EXCLUDED伪表包含被拒绝的插入行
3. 可以添加WHERE条件实现条件更新

方法二：可写CTE实现

实现原理

通过WITH子句先尝试更新，再根据影响行数决定是否插入

WITH update_cte AS (
    UPDATE users 
    SET last_login = NOW(),
        login_count = login_count + 1
    WHERE user_id = 1
    RETURNING user_id
)
INSERT INTO users (user_id, username, last_login, login_count)
SELECT 1, 'john_doe', NOW(), 1
WHERE NOT EXISTS (SELECT 1 FROM update_cte);

适用场景

1. 需要更复杂的更新逻辑时
2. PostgreSQL 9.4及以下版本（不支持ON CONFLICT）

方法三：函数/存储过程实现

PL/pgSQL函数示例

CREATE OR REPLACE FUNCTION upsert_user(
    p_user_id INT,
    p_username VARCHAR,
    p_last_login TIMESTAMP
) RETURNS VOID AS $$
BEGIN
    -- 先尝试更新
    UPDATE users 
    SET username = p_username,
        last_login = p_last_login,
        login_count = login_count + 1
    WHERE user_id = p_user_id;
    
    -- 如果未更新则插入
    IF NOT FOUND THEN
        INSERT INTO users (user_id, username, last_login, login_count)
        VALUES (p_user_id, p_username, p_last_login, 1);
    END IF;
END;
$$ LANGUAGE plpgsql;

调用方式

SELECT upsert_user(1, 'john_doe', NOW());

性能对比

高级应用场景

批量Upsert操作

INSERT INTO users (user_id, username, last_login)
VALUES 
    (1, 'john_doe', NOW()),
    (2, 'jane_smith', NOW())
ON CONFLICT (user_id) 
DO UPDATE SET 
    username = EXCLUDED.username,
    last_login = EXCLUDED.last_login;

条件更新

INSERT INTO users (user_id, username, last_login)
VALUES (1, 'john_doe', NOW())
ON CONFLICT (user_id) 
DO UPDATE SET 
    last_login = EXCLUDED.last_login
WHERE users.last_login < EXCLUDED.last_login;

常见问题解答

Q：没有唯一约束时如何实现upsert？

A：可以先创建唯一索引：

CREATE UNIQUE INDEX idx_user_id ON users(user_id);

Q：如何获取upsert后的数据？

A：添加RETURNING子句：

INSERT INTO users (...) VALUES (...)
ON CONFLICT (...) DO UPDATE SET ...
RETURNING *;

Q：ON CONFLICT支持多列唯一约束吗？

A：支持，语法示例：

ON CONFLICT (col1, col2) DO UPDATE ...

结论

PostgreSQL提供了多种灵活的upsert实现方式，推荐优先使用ON CONFLICT语法，它在现代PostgreSQL版本中最简洁高效。对于复杂场景，可写CTE和存储过程提供了更多控制权。根据具体业务需求选择最适合的方案，可以显著提高数据操作的效率和可靠性。

注意：实际应用时应考虑事务隔离级别和并发控制，确保数据一致性。 “`

这篇文章包含了约1200字，采用Markdown格式编写，涵盖了PostgreSQL实现upsert操作的三种主要方法，包含代码示例、性能对比和常见问题解答，适合作为技术文档参考。