mysql - 代替MySQL ORDER BY RAND

我使用此查询：

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排。

最佳答案

您的查询可能存在的一个问题是，它只随机选择一条记录，而其余的记录是连续的。如果你想真正随机选择所有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)

然而，就像dnswlt在他的answer中提到的那样。更简单的经典解决方案适用于小桌子：

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

关于mysql - 代替MySQL ORDER BY RAND，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48935541/