首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >而不是MySQL ORDER BY RAND

问而不是MySQL ORDER BY RAND
EN

Stack Overflow用户
提问于 2018-02-23 03:17:58
回答 2查看 65关注 0票数 1

我使用这个查询:

代码语言:javascript
复制
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行。

EN

回答 2

Stack Overflow用户

发布于 2018-02-23 03:37:33

您的查询可能存在的一个问题是,它只随机选择一条记录,其余的记录是连续的。如果你想真正随机选择所有5个,你必须逐个选择它们:

代码语言:javascript
复制
(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是连续的,没有间隙,则此方法有效。如果有差距,则必须将相等更改为小于,并将其限制为一条记录:

代码语言:javascript
复制
(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中提到的那样。经典的解决方案要简单得多,它适用于小型表:

代码语言:javascript
复制
SELECT f.id
FROM articles f
ORDER BY RAND()
LIMIT 5
票数 2
EN

Stack Overflow用户

发布于 2018-02-24 05:34:02

你的问题看起来像是MySQL select 10 random rows from 600K rows fast的副本。

根据您的表中有多少行和您愿意接受的响应时间,您可以首先尝试经典变体:

代码语言:javascript
复制
SELECT f.id FROM articles f
ORDER BY RAND()
LIMIT 5

如果不到一百万,这可能就足够了。

否则,您可以应用链接文章中描述的技巧,并使用UNION ALL 5这类查询,大致如下:

代码语言:javascript
复制
(SELECT r1.id
  FROM articles AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM articles)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1)
 UNION ALL
 (SELECT r1.id
  FROM articles AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM articles)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1)
 UNION ALL
 ... (three more queries to retrieve 5 rows)

如果您的行数很少,则此解决方案中可能存在重复项。但是,如果您的行很少,则可以使用上面更简单的解决方案:)

我为第二个变体创建了一个SQL Fiddle。哈!

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/48935541

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档