WHERE子查询获取随机Quote偶无结果，JOIN方式却正常

问题根源 #

你遇到的这个问题，核心是两种查询写法的事务快照使用逻辑和优化器执行策略差异导致的：

• 对于WHERE id = (SELECT id FROM quote ORDER BY RAND() LIMIT 1)的写法：
  子查询和主查询可能处于不同的事务快照上下文（比如在可重复读隔离级别下）。如果子查询获取到id后，主查询执行前有其他事务删除了该id，或者主查询读取的是更早的数据快照，就会出现主查询找不到对应id的情况。部分数据库的优化器还可能将这个标量子查询优化为在主查询执行过程中重复执行，进一步增加了不匹配的概率。
• 对于JOIN的写法：
  派生表（子查询）和主查询共享同一个查询上下文与事务快照，派生表获取的id必然存在于主查询读取的quote表数据中，因此不会出现无结果的问题。

可行解决方案 #

方案1：继续使用JOIN写法（推荐） #

这是最稳定可靠的方式，即使后续添加额外JOIN操作也不会受影响：

SELECT * 
FROM quote 
JOIN (SELECT id FROM quote ORDER BY RAND() LIMIT 1) q1 ON quote.id = q1.id
-- 在这里添加你的额外JOIN操作即可

方案2：使用变量存储随机ID #

如果偏好WHERE子查询的写法，可以先将随机ID存入变量，再执行主查询，避免快照不一致：

SET @random_quote_id = (SELECT id FROM quote ORDER BY RAND() LIMIT 1);
SELECT * 
FROM quote 
WHERE id = @random_quote_id;
-- 同样可以添加额外JOIN操作

方案3：强制子查询与主查询共享快照（部分数据库适用） #

比如在MySQL中，可以通过STRAIGHT_JOIN或者调整隔离级别为读已提交，但这种方式兼容性较差，不推荐作为首选。

内容的提问来源于stack exchange，提问作者DanR