如何用SQL关联子查询实现库存扣减与余量校验

优先用EXISTS做存在性校验，选错IN可能导致库存扣减翻车

先别急着写IN，尤其在做库存扣减这种高并发场景时，EXISTS才是更稳的选择。原因很简单：它只看有没有匹配行，不关心具体值，不受NULL干扰，而且执行计划天然倾向于走索引。而IN呢？属于小集合静态枚举的专用工具——比如预设的几个仓库编码，用IN很合适。但一旦子查询里出现NULL，整个条件就直接失效，这是很多人踩过的坑。

关联子查询里用 EXISTS 还是 IN？选错直接锁表或查不到数据

库存扣减必须做到“有货才扣”，同时余量校验还得实时反映最新状态。IN在这种场景下很容易漏掉并发更新导致的幻读——你查的时候有货，扣的时候被抢走了。EXISTS更安全，它只关心是否存在匹配行，不拉取实际值，执行计划也更倾向走索引。

一个常见的错误写法是：WHERE id IN (SELECT id FROM stock WHERE qty > 0)。表面没问题，但假如子查询中某条记录的qty为NULL，IN的结果就是全部不匹配，整个条件失效。EXISTS则完全不受NULL影响。这里有几个要点需要记住：

• 优先用EXISTS做存在性校验，尤其是在UPDATE ... WHERE EXISTS (...)场景
• IN仅适用于明确、小集合的静态枚举，比如预设的仓库编码列表
• 子查询中必须包含外层表的关联字段，否则会变成非关联子查询，逻辑全错

UPDATE 语句里嵌套关联子查询怎么写才不踩死锁？

直接写 UPDATE t1 SET qty = qty - 1 WHERE id IN (SELECT id FROM t2 WHERE t2.id = t1.id AND t2.qty >= 1) 看起来挺合理，但MySQL在某些版本下会对子查询结果集加间隙锁，再对主表加行锁，两把锁一碰，死锁就来了。正确的做法是把校验逻辑收进子查询的JOIN条件里，并且确保所有涉及字段都有索引：

UPDATE stock s
JOIN (
  SELECT id, qty 
  FROM stock 
  WHERE id = ? AND qty >= ?
) AS check_q ON s.id = check_q.id
SET s.qty = s.qty - ?
WHERE s.id = ?;

这里有几个细节需要注意：

• 参数占位符（?）必须一一对应，硬编码数字会引发执行计划失效
• stock(id) 和 stock(id, qty) 复合索引缺一不可，否则子查询走全表扫描
• 不要在子查询里用ORDER BY或LIMIT——MySQL 8.0+虽然支持，但会禁用部分优化，而且业务上也没有意义

扣减前查余量、扣减后校验是否为负——为什么不能分两条 SQL？

很多人习惯先执行SELECT qty FROM stock WHERE id = ?，拿到结果再UPDATE。但这两步之间，其他事务完全可能修改数据，导致超扣。看似“先查后改”逻辑清晰，实际上违反了原子性。

真正可靠的方案是在单条UPDATE中完成“读-算-写-验”闭环：

UPDATE stock 
SET qty = CASE WHEN qty >= ? THEN qty - ? ELSE qty END
WHERE id = ? AND qty >=? ;

执行后检查ROW_COUNT()：等于1表示扣减成功且未超限；等于0表示库存不足或记录不存在。这里的关键点：

• WHERE qty >= ? 是最后一道防线，必须和SET中的判断条件一致，否则会出现负数余量
• 不要依赖SELECT ... FOR UPDATE后再UPDATE——它会延长锁持有时间，导致吞吐量明显下降
• 应用层需要捕获ROW_COUNT() == 0并抛出明确的业务异常（比如InsufficientStockException），而不是静默失败

PostgreSQL 和 MySQL 在关联子查询扣减上的关键差异

MySQL的UPDATE ... JOIN直接支持关联子查询，而PostgreSQL必须用FROM子句或CTE。写法不同，但核心约束一致：所有校验必须在单条语句内完成。下面是一个PostgreSQL的示例：

UPDATE stock 
SET qty = qty - $2
FROM (SELECT id FROM stock WHERE id = $1 AND qty >= $2) AS check_ok
WHERE stock.id = check_ok.id;

这里有两个重要的差异点：

• PostgreSQL不允许在UPDATE的WHERE中直接写子查询引用目标表（会报relation "stock" does not exist），必须通过FROM拉进来
• MySQL 8.0+支持CTE，但CTE在UPDATE中不能直接更新，仍需配合JOIN
• 两者都要求qty字段为NOT NULL，否则qty >= ?判断在NULL时恒为FALSE，导致扣减永远失败

并发场景下最容易被忽略的其实是索引失效导致的全表扫描——哪怕逻辑完全正确，一次慢查询就能拖垮整个库存服务。所以别只盯着SQL写法，先检查执行计划里有没有type=ALL。