如何编写SQL关联子查询来实现复杂的库存扣减与余量校验
优先用EXISTS做存在性校验，因其只判断是否存在匹配行、不受NULL影响、执行计划更倾向走索引；IN仅适用于小集合静态枚举，且子查询含NULL会导致条件失效。
关联子查询里用 EXISTS 还是 IN？选错直接锁表或查不到数据
库存扣减必须确保“有货才扣”，而余量校验又得实时反映最新状态——这时候用 IN 容易漏掉并发更新导致的幻读，EXISTS 更安全。它只关心是否存在匹配行，不拉取实际值，执行计划也更倾向走索引。
常见错误是写成：WHERE id IN (SELECT id FROM stock WHERE qty > 0)，但若子查询返回 NULL（比如某条记录 qty 为 NULL），整个条件就失效；而 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 sJOIN ( SELECT id, qty FROM stock WHERE id = ? AND qty >= ?) AS check_q ON s.id = check_q.idSET 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 ENDWHERE 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 - $2FROM (SELECT id FROM stock WHERE id = $1 AND qty >= $2) AS check_okWHERE 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。
