MySQL RAND()多久可以使用一次?它使用/dev/random吗? [英] MySQL RAND() how often can it be used? does it use /dev/random?

问题描述

我的桌子上有几行(前50位)，我需要从表中获取随机值，我可以通过
ORDER BY RAND() LIMIT 1
主要问题是当我在5秒内选择6k时，兰特·斯蒂尔是否可靠"? 兰特是如何计算的，我可以随着时间推移播种吗? (idk，每5秒一次).

I have a table with few rows (tops 50), I need to get random value out of table I can do that by
ORDER BY RAND() LIMIT 1
Main question is in the point when I have 6k selects in 5 seconds is rand stil 'reliable'? How is rand calculated, can I seed it over time? (idk, every 5 seconds).

推荐答案

MySQL伪随机数生成器是完全确定性的.文档说:

The MySQL pseudo-random number generator is completely deterministic. The docs say:

RAND()并不是完美的随机生成器.这是一种快速生成按需随机数的方法，该方法可在相同MySQL版本的平台之间移植.

RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

它不能使用/dev/random，因为MySQL可以在多种操作系统上运行，其中有些操作系统没有/dev/random.

It can't use /dev/random because MySQL is designed to work on a variety of operating systems, some of which don't have a /dev/random.

MySQL使用time(0)返回的整数在服务器启动时初始化默认种子. 如果您对源代码行感兴趣，请访问MySQL源代码文件sql/mysqld.cc，函数init_server_components().我认为它永远不会重新播种.

MySQL initializes a default seed at server startup, using the integer returned by time(0). If you're interested in the source line, it's in the MySQL source in file sql/mysqld.cc, function init_server_components(). I don't think it ever re-seeds itself.

然后，随后的随机"数字仅基于种子.请参见源文件mysys_ssl/my_rnd.cc，功能my_rnd().

Then the subsequent "random" numbers are based solely on the seed. See source file mysys_ssl/my_rnd.cc, function my_rnd().

就性能和随机化质量而言，随机选择任务的最佳实践解决方案是在最小主键值和最大主键值之间生成一个随机值.然后使用该随机值在表中选择一个主键:

The best practice solution to your random-selection task, for both performance and quality of randomization, is to generate a random value between the minimum primary key value and maximum primary key value. Then use that random value to select a primary key in your table:

SELECT ... FROM MyTable WHERE id > $random LIMIT 1

之所以使用>而不是=的原因是，由于行被删除或回滚，所以id可能存在间隙，或者WHERE子句中可能存在其他条件，因此行之间存在间隙符合您的条件.

The reason you'd use > instead of = is that you might have gaps in the id due to rows being deleted or rolled back, or you might have other conditions in your WHERE clause so that you have gaps in between rows that match your conditions.

这种大于方法的缺点:

• 跟随这样的间隙的行被选择的机会更高，并且间隙越大，机会越大.
• 在生成随机值之前，您需要了解MIN(id)和MAX(id).
• 如果您需要多个随机行，则效果不佳.

此方法的优点:

• 即使表大小适中，它也比ORDER BY RAND()快得多.
• 您可以在SQL之外使用随机函数.

这篇关于MySQL RAND()多久可以使用一次?它使用/dev/random吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！