PostgreSQL一对多关系下的复杂批量更新与新增查询实现

我来给你梳理下怎么处理这个场景——既要更新已有书籍的库存，又要新增用户没有的书籍，还得保证操作的原子性。

核心思路 #

咱们可以用PostgreSQL的jsonb类型接收请求体的完整数据，分两步处理：先批量更新已有书籍的库存，再插入那些不带book_id的新增书籍。而且PostgreSQL的PL/pgSQL函数天生自带事务性，完全不用额外操心。

步骤1：编写处理函数 #

下面是完整的PL/pgSQL函数实现，它会自动区分需要更新的书籍和需要新增的书籍：

CREATE OR REPLACE FUNCTION update_user_books(user_data jsonb)
RETURNS void AS $$
DECLARE
    v_user_id INT := (user_data->>'user_id')::INT;
BEGIN
    -- 批量更新已有书籍的库存（带book_id的条目）
    UPDATE users_books ub
    SET stock = b.stock
    FROM (
        SELECT
            (book->>'book_id')::INT AS book_id,
            (book->>'stock')::INT AS stock
        FROM jsonb_array_elements(user_data->'books') AS book
        WHERE book ? 'book_id' -- 筛选出带book_id的更新条目
    ) b
    WHERE ub.user_id = v_user_id AND ub.book_id = b.book_id;

    -- 插入新增书籍（不带book_id的条目）
    -- 先往books表插新书拿到book_id，再关联到users_books
    INSERT INTO users_books(user_id, book_id, stock)
    SELECT
        v_user_id,
        (INSERT INTO books(name) VALUES ((book->>'name')::TEXT) RETURNING book_id),
        (book->>'stock')::INT
    FROM jsonb_array_elements(user_data->'books') AS book
    WHERE NOT book ? 'book_id'; -- 筛选出不带book_id的新增条目

END;
$$ LANGUAGE plpgsql VOLATILE;

步骤2：如何判断用户是否拥有某本书？ #

在更新逻辑里，我们通过WHERE ub.user_id = v_user_id AND ub.book_id = b.book_id来匹配用户已有的书籍：

• 如果用户确实拥有这本书，就会更新库存；
• 如果用户没有这本书，这条UPDATE语句不会有任何影响（不会报错，也不会创建新记录），完全符合咱们的需求。

要是你想对“用户未拥有的book_id”做提示，可以在更新后加个行数检查：

-- 在批量更新后添加这段代码
DECLARE
    v_updated_rows INT;
BEGIN
    -- ... 批量更新代码 ...
    GET DIAGNOSTICS v_updated_rows = ROW_COUNT;
    IF v_updated_rows < (SELECT COUNT(*) FROM jsonb_array_elements(user_data->'books') WHERE book ? 'book_id') THEN
        RAISE NOTICE '部分book_id对应用户未拥有，未执行更新';
        -- 也可以抛出中断事务的异常：RAISE EXCEPTION '存在未匹配的book_id';
    END IF;

步骤3：事务性保障 #

PL/pgSQL函数默认是原子性事务：函数里的所有SQL语句要么全部执行成功，要么全部回滚。比如如果插入新书时违反了books表的约束，或者类型转换出错，整个函数的操作都会被回滚，不会出现“更新了部分书籍，新增失败”的半完成状态，完全满足你的事务性要求。

调用函数的方式 #

直接把请求体转成jsonb传入函数就行：

SELECT update_user_books('{
  "user_id": 1,
  "name": "Ryan",
  "books": [
    {"book_id": 1, "stock": 500},
    {"book_id": 2, "stock": 500},
    {"name": "My new book 1", "stock": 500}
  ]
}'::jsonb);

性能优化建议 #

如果需要处理大量书籍，上面的批量更新比循环处理高效得多。如果你的业务场景里新增书籍的频率很高，还可以考虑把书籍名称的查重逻辑加进去（避免重复插入同名书籍），比如在插入前检查books表是否已存在同名书籍：

-- 修改新增书籍的查询逻辑，先查是否已有同名书
INSERT INTO users_books(user_id, book_id, stock)
SELECT
    v_user_id,
    COALESCE(
        (SELECT book_id FROM books WHERE name = (book->>'name')::TEXT),
        (INSERT INTO books(name) VALUES ((book->>'name')::TEXT) RETURNING book_id)
    ),
    (book->>'stock')::INT
FROM jsonb_array_elements(user_data->'books') AS book
WHERE NOT book ? 'book_id';

内容的提问来源于stack exchange，提问作者Élisa Plessis