mysql RAND() 限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13797553/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql RAND() LIMIT
提问by FreshPro
Hello I have a query like this:
你好，我有一个这样的查询：
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE YAZILIM_menu_icerik.menu_id = 39
AND otel_id IN (
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE menu_id =$id
ORDER BY RAND()
)
LIMIT 0,20
It should display randomly 20 hotels each time it works but instead , i'm ending up getting the same 20 hotel each time. Long story short RAND()doesn't seem to work and I can't seem to find any logical error.
每次工作时它应该随机显示 20 家酒店，但相反，我每次都得到相同的 20 家酒店。长话短说RAND()似乎不起作用，我似乎找不到任何逻辑错误。
EDIT: Problem solved. Mysql doesn't see inner RAND() so here's the correct way to do it:
编辑：问题解决了。Mysql 看不到内部 RAND() 所以这是正确的方法：
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE YAZILIM_menu_icerik.menu_id = 39
AND otel_id IN (
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE menu_id =$id
)
ORDER BY RAND()
LIMIT 0,20
回答by Mark Byers
Your ORDER BY and LIMIT clauses should be together:
您的 ORDER BY 和 LIMIT 子句应该放在一起：
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE YAZILIM_menu_icerik.menu_id = 39
AND otel_id IN
(
SELECT otel_id
FROM YAZILIM_menu_icerik
WHERE menu_id = $id
)
ORDER BY RAND()
LIMIT 20
It makes no sense to have a bare ORDER BYin a subquery with no LIMITclause because the ordering is not necessarily preserved in the results of the outer query.
ORDER BY在没有LIMIT子句的子查询中使用裸是没有意义的，因为排序不一定保留在外部查询的结果中。
回答by zloctb
ORDER BY RAND() is bad solution. Your indexes are ignored. Particularly difficult situation with large amounts of data.
ORDER BY RAND() 是不好的解决方案。您的索引将被忽略。具有大量数据的特别困难的情况。
Alternative if you have primary key on id :
如果您在 id 上有主键，则替代：
SELECT b1.* FROM Bugs AS b1 JOIN
(SELECT CEIL( RAND()* (SELECT MAX(id) FROM Bugs)) AS id) AS b2
WHERE b1.id >= b2.id ORDER BY b1.id LIMIT 1
