我使用这个查询:
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行。
发布于 2018-02-23 03:37:33
您的查询可能存在的一个问题是,它只随机选择一条记录,其余的记录是连续的。如果你想真正随机选择所有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发布于 2018-02-24 05:34:02
你的问题看起来像是MySQL select 10 random rows from 600K rows fast的副本。
根据您的表中有多少行和您愿意接受的响应时间,您可以首先尝试经典变体:
SELECT f.id FROM articles f
ORDER BY RAND()
LIMIT 5如果不到一百万,这可能就足够了。
否则,您可以应用链接文章中描述的技巧,并使用UNION ALL 5这类查询,大致如下:
(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。哈!
https://stackoverflow.com/questions/48935541
复制相似问题