mysql - 而不是 MySQL ORDER BY RAND

Question

我使用这个查询:

SELECT f.id FROM articles f
JOIN ( SELECT RAND() * (SELECT MAX(id) FROM articles) AS max_id ) AS m
WHERE f.id >= m.max_id
ORDER BY f.id ASC
LIMIT 5

问题在于它返回了意外的行数。它可以是 1 行或 3 行或 5 行。但我正好需要 5 行。

Answer 1

您的查询可能存在的问题是它只随机选择一条记录，其余记录是顺序的。如果您想真正随机选择所有 5 个，则必须一个一个地选择它们:

(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id)

如果 ID 是连续的且没有间隙，则此方法有效。如果有间隙，则必须将相等性更改为小于，并限制为一条记录:

(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1)

但是，像他answer中提到的dnswlt .简单得多的经典解决方案适用于小表:

SELECT f.id
FROM articles f
ORDER BY RAND()
LIMIT 5