随机 MySQL 行,同时避免漏洞
我有一个很大的 MySQL 用户表,需要从中获取六个随机行(我使用的是 PHP)。该表有一个自动递增的索引列。唯一的问题是某些行被标记为非活动状态,因为某些用户已禁用其帐户或其他原因。这意味着我不能只计算行数,然后从该范围中获取随机数,因为其中一些行将处于非活动状态。
如何在不使用 RAND() 且不尝试查询非活动用户的情况下有效地获取随机行?
谢谢!
I have a big MySQL users table and need to get six random rows from it (I'm using PHP). The table has an index column that is auto incremented. The only problem is that some rows are marked as inactive, because some users have disabled their accounts or whatever. That means I can't just count the rows and then grab a random number from that range because some of them will be inactive.
How can I efficiently get a random row without using RAND() and without trying to query an inactive user?
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
其中
random_offset
是在 PHP 中预先计算的(从 1 到COUNT
的活跃用户中随机计算)。结果由 6 个 UNION 查询组成。where
random_offset
is precalculated in PHP (as random from 1 toCOUNT
of active users). And the result consists of 6UNION
ed queries.如果您希望避免通过 RAND() 方法进行非常慢的排序,这里有各种替代方案,其中包含各种选项来管理漏洞
从 mysql 中的大表中快速选择随机行
If you wish to avoid the very slow order by RAND() method, here are various alternatives, with various options to manage holes
quick selection of a random row from a large table in mysql