支付回调重试：增删改与 UPSERT

支付网关的回调会重试：同一笔支付可能回调多次。后端问你：「怎么保证不重复入账？」--幂等写入专场。

学 35 min

练 70 min

盘 15 min

共 120 分钟

完成这一天

学 · 35 min

01多行 INSERT、INSERT ... SELECT
场景再也不用一行一条地插了。
values (...), (...), ... 一条语句插多行（减少往返，比循环单插快一个量级）；insert into t select ... 从查询结果直接灌数--你已经在 seed.sql 里见过它几百次了。
```
insert into payments(order_id, method, amount, status, paid_at)
values ('...', 'alipay', 99.00, 'paid', now()),
       ('...', 'wechat', 59.00, 'paid', now());

insert into payments(order_id, method, amount)
select id, 'free', 0 from orders where status = 3;   -- 从查询灌
```
易错单条多行插入是一个语句：任何一行违反约束，整条全部失败（原子性）。
02ON CONFLICT DO NOTHING / DO UPDATE（PG 的 upsert）
场景同一笔回调来三次，账只能入一次--幂等的核心武器。
语法 = 冲突目标 + 动作：on conflict (唯一键列)，撞上唯一约束时执行 DO NOTHING（跳过）或 DO UPDATE（改为更新）。DO UPDATE 里用 excluded.列 引用「这次想插进去的那行」。
```
insert into payments(order_id, method, amount, status, paid_at)
values ('...', 'alipay', 99.00, 'paid', now())
on conflict (order_id, method)          -- 撞唯一约束
do update set status = 'paid',
             paid_at = excluded.paid_at;  -- 用新值更新
-- 重复执行 N 次，payments 里永远只有这一行 = 幂等
```
易错前提是先有唯一约束--没有唯一键，on conflict 不知道「冲突」看哪里。excluded 别名是固定写法，不是表名。
03RETURNING 拿回写入结果
场景插完订单要用它的自增 id 建明细--以前的写法是再查一遍？
insert / update / delete ... returning * 把受影响的行直接返回，一步拿回自增 id、默认值、生成列。应用层省一次往返，还避免「插完再按条件查」的竞态。
```
insert into orders(user_id, status, total_amount, created_at)
values ('...', 1, 128.00, now())
returning id, created_at;   -- 直接拿到生成的 uuid 和默认值

delete from payments where status = 'failed'
returning id;               -- 删了哪些，当场对账
```
易错returning 返回的是本次语句影响的行；0 行返回就是没删没改，应用层别当成功处理。
04UPDATE ... FROM 与 DELETE ... USING 关联改删
场景「把某类目下所有商品调价 10%」--更新条件在另一张表里。
PG 方言两件套：update t set ... from 参照表 where 关联条件；delete from t using 参照表 where 关联条件。等价于「join 着改 / 删」，比「先查 id 再 in (...)」少一步。
```
update products p
set price = round(p.price * 1.1, 2)
from categories c
where c.id = p.category_id
  and c.name = '手机';

delete from order_items i
using orders o
where i.order_id = o.id
  and o.status is null;   -- 脏订单的明细清掉
```
易错update ... from 里若 join 出多行，结果是「随机一行生效」--关联键不唯一时会悄悄错；先确保一对一。
05COPY 批量导入的性能优势
场景要灌 10 万行 CSV，insert 跑了一分钟。
copy 表 from ... csv 绕过 SQL 解析层，按二进制/文本协议直灌，大批量导入快一个数量级。psql 里用 \copy（读客户端本地文件）；SQL 的 COPY 读服务端文件（要权限）。
```
-- psql 客户端：导入本地 CSV
\copy products(id, name, price) from 'products.csv' with csv header

-- 先建表结构，copy 只管数据
copy products(id, name, price) from '/tmp/products.csv' with csv header;
```
易错copy 不走 on conflict / returning；要幂等或拿回结果还得 insert。它是纯粹的「快」。

练 · 70 min

写一条 upsert：商品存在则更新库存，不存在则插入

参考答案

excluded 指这次想插进去的那行（不是表名）；连跑 N 次 = 1 次插入 + N-1 次更新，行数不涨。没有唯一约束时 on conflict 无从判断「冲突」，会直接报错。

-- on conflict 的前提：唯一约束先到位（商品名当前无重复）
alter table products add constraint uq_products_name unique (name);

insert into products(name, category_id, price, stock)
values ('新品手机', null, 1999.00, 10)
on conflict (name) do update
set stock = products.stock + excluded.stock;   -- 撞了就改成累加库存

-- 同一条再跑一遍：不新增行，库存再 +10
select name, price, stock from products where name = '新品手机';

用唯一键 + ON CONFLICT DO NOTHING 实现支付回调的幂等入账，重复执行验证结果不变

参考答案

把 on conflict do nothing 去掉再跑：每执行一次多一行--这就是「回调重试导致重复入账」。幂等 = 唯一键 + upsert 两件套：唯一键定义什么叫重复，on conflict 决定重复来了怎么办。

-- 前提：payments 的 unique(order_id, method)（建表时已加）
-- 取一笔已存在的流水，模拟网关对同一笔回调的重试
with p as (
  select order_id, method, amount, status, paid_at
  from payments
  limit 1
)
insert into payments(order_id, method, amount, status, paid_at)
select order_id, method, amount, status, paid_at from p
on conflict (order_id, method) do nothing;

select count(*) from payments;   -- 连跑几次，数字都不变

插入订单并用 RETURNING 拿回自增 id

参考答案

returning 省掉「插完再按条件查」的一次往返，也避免那个查询的竞态；返回 0 行 = 语句没影响任何行，应用层别当成功处理。测试数据可随后 delete 清理。

insert into orders(user_id, status, total_amount, created_at)
values ((select id from users limit 1), 1, 128.00, now())
returning id, created_at;   -- 生成的 uuid 当场拿回

-- 实战形态：data-modifying CTE，插订单拿 id 直接建明细，一条语句完成
with new_order as (
  insert into orders(user_id, status, total_amount, created_at)
  values ((select id from users limit 1), 1, 128.00, now())
  returning id
)
insert into order_items(order_id, product_id, qty, unit_price)
select id, (select id from products limit 1), 2, 64.00
from new_order
returning order_id, qty, unit_price;

用 UPDATE ... FROM 把某类目下所有商品调价 10%

参考答案

update ... from 等价于「join 着改」；from 侧关联键不唯一时只有随机一行生效，动手前先确认一对一（这里商品对一个类目，安全）。

-- 先看这次会动多少行
select count(*) from products p
join categories c on c.id = p.category_id
where c.name = '子分类1';

update products p
set price = round(p.price * 1.1, 2)
from categories c
where c.id = p.category_id
  and c.name = '子分类1';

-- 后悔药：把 1.1 换成 / 1.1 再跑一遍（近似还原）

用 COPY 导入 10 万行 CSV，与逐条 INSERT 对比耗时

参考答案

COPY 走专用协议绕过 SQL 解析器，快一个数量级；代价是不支持 on conflict / returning，纯粹的快。\copy 是 psql 读客户端文件的版本，服务端 copy 读服务端文件、要权限。

-- ① 造一份 10 万行 CSV（写到客户端本地文件）
\copy (select g, '测试商品' || g, round((10 + random() * 990)::numeric, 2)
       from generate_series(1, 100000) g) to '/tmp/demo.csv' with csv

create table import_demo (id int, name text, price numeric);

-- ② COPY 导入，记下 \timing 给出的耗时
\timing on
\copy import_demo from '/tmp/demo.csv' with csv
-- 典型耗时：几十到几百毫秒

-- ③ 逐条 INSERT 对比：先生成 10 万条 insert 语句，再整文件执行
truncate import_demo;
\copy (select 'insert into import_demo values (' || g || ', '
             || quote_literal('测试商品' || g) || ', '
             || round((10 + random() * 990)::numeric, 2) || ');'
       from generate_series(1, 100000) g) to '/tmp/demo_inserts.sql'
\i /tmp/demo_inserts.sql
-- 典型耗时：几十秒起步（每条语句自动提交一个事务 + 一次完整解析）

drop table import_demo;

过关标准 写出一条真正幂等的 upsert（重复执行结果不变）。

学 · 35 min

01多行 INSERT、INSERT ... SELECT

02ON CONFLICT DO NOTHING / DO UPDATE（PG 的 upsert）

03RETURNING 拿回写入结果

04UPDATE ... FROM 与 DELETE ... USING 关联改删

05COPY 批量导入的性能优势

练 · 70 min

出题端点设置