从MySQL随机选取数据

原创 luxiaoyan 随笔 mysql 102阅读 2018-07-18 11:05:53 举报

1:SELECT FROM users AS t1 JOIN (SELECT ROUND(RAND() ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users))+(SELECT MIN(userId) FROM users)) AS userId) AS t2 WHERE t1.userId >= t2.userId ORDER BY t1.userId LIMIT 1

执行该sql语句,用时0.031s,效率没说的,相当的给力!心里那个爽啊,紧接着,我把”LIMIT 1“改为了”LIMIT 100“ 随机取一百条记录,用时0.048,给力吧。可是就在此时问题出现了,发现结果好像不是随机的?为了验证结果,又执行了N次,真不是随机的, 问题出现在”ORDER BY t1.userId“这里,按userId排序了。随机取一条记录还是不错的选择,多条就不行了啊!

2:SELECT FROM users WHERE userId >= ((SELECT MAX(userId) FROM users)-(SELECT MIN(userId) FROM users)) RAND() + (SELECT MIN(userId) FROM users) LIMIT 1
执行该sql语句,用时0.039s,效率太给力了!接着我就把”LIMIT 1“改为了”LIMIT 10000“,用时0.063s。经过多次验证,哥对灯发誓,结果肯定是随机的!
结论:随机取一条或多条记录,方法都不错!

评论 ( 1 )
最新评论
lawrence 2018-07-18 13:47:49 1F

mysql很多内置函数和其他数据库不通用吧?