如何编写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。