mysql - 为什么我的 SQL 语句有时没有返回结果？

Question

给出下表:

我们有以下返回随机行的 SQL 语句:

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2    
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null 
ORDER BY r1.id ASC 
LIMIT 1

这本来就可以正常工作，但是当我们需要按稀有度随机化时，因此需要将上面的内容更改为下面的内容:

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT (RAND() * (SELECT MAX(id) FROM items_permanent)) AS id
) AS r2 
WHERE r1.id >= r2.id AND r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1

它有时根本不返回任何行，规律性惊人(我会说，十分之一的执行)。

这是什么原因造成的？我该如何预防或修复它？

Answer 1

您的查询是在 ID 范围内选择一个随机数，然后在表中查找与条件匹配的下一行。如果随机 ID 之后的行均不符合条件，则您将一无所获。您还需要在选择随机 ID 时包含条件。

SELECT r1.id, game_desc, rarity, unlocks_prior 
FROM items_permanent AS r1 
JOIN (
    SELECT RAND() * MAX(id) AS id
    FROM items_permanent
    WHERE unlocks_prior is Null AND rarity = ? 
) AS r2 ON r1.id >= r2.id
WHERE r1.unlocks_prior is Null AND r1.rarity = ? 
ORDER BY r1.id ASC 
LIMIT 1