MySQL RAND()种子值几乎重复 [英] MySQL RAND() seed values almost repeat
问题描述
在Windows 7上使用MySQL 5.6.21.
Using MySQL 5.6.21 on Windows 7.
我正在尝试从按日期设定种子的表中返回随机"行(因此同一行返回当天的日期，然后切换第二天，以此类推-如果您愿意，可以使用日期生成器的随机报价" ).
I am attempting to return a 'random' row from a table seeded by the date (so the same row returns for the current day and then switches the next day etc - a "random quote of the day generator" if you like).
我注意到不断出现相同的行，因此我简化了查询的基础，看来RAND()函数每第四个种子值生成非常个相似的数字.当四舍五入为整数时，值似乎每四分之一重复一次.此示例仅使用16行，但是您知道了.
I noticed the same rows keep coming up so I simplified the query to its basics, it appears the RAND() function generates very similar numbers every fourth seed value. When rounded to an integer the values appear to repeat every fourth seed. This example only uses 16 rows, but you get the idea.
create table t (i INT);
insert into t values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
select i, ceil(rand(i) * 16), rand(i) from t;
drop table t;
给......
0 3 0.15522042769493574
1 7 0.40540353712197724
2 11 0.6555866465490187
3 15 0.9057697559760601
4 3 0.15595286540310166
5 7 0.40613597483014313
6 11 0.6563190842571847
7 15 0.9065021936842261
8 3 0.15668530311126755
9 7 0.406868412538309
10 11 0.6570515219653505
11 15 0.907234631392392
12 3 0.15741774081943347
13 7 0.40760085024647497
14 11 0.6577839596735164
15 15 0.9079670691005579
这不是我期望的，所以我做错了什么?我希望会生成一个伪随机序列.
Not what I expected, so what am I doing wrong? I expected a pseudo-random sequence to be generated.
推荐答案
RAND()并不是完美的随机生成器，因此，如果不合适，则应使用其他生成随机数的方法.就我而言，我每天需要对行进行不同的排序，事实证明RAND(CURDATE()+0)是一个可行的解决方案.
RAND() is not meant to be a perfect random generator, so if it is not suitable, then other ways of generating random numbers should be used. In my case, I needed to order rows differently per day, and RAND(CURDATE()+0) proved to be a viable solution.
是的，似乎第一个项的周期为4，并且每次种子增加4时，都会给它一个相似的随机数.
Yes, it does seem as though the first term has a cycle of 4, and it will be given a similar random number each time the seed increments by 4.
我通过自己的随机数生成基础测试得到了以下结果，并使用种子数0、4、8和12每次将种子加4.
I got the following results from my own basic testing of random number generation and incrementing the seed by 4 each time, using the seed number 0, 4, 8, and 12.
- 第1行:大约增加0.001
- 第2行:增加约0.005
- 第3行:增加约0.02
- 第4行:增加约0.05
- 第5行:增加约0.2
- 第6行:减少约0.01(或者可能增加0.99)
- 第7行:增加约0.26
在OP的示例中，由于它们不使用恒定的种子数，因此从技术上讲，它们的样本大小为1.随着行数的增加，对重复索引的影响会减小，并且其他项目将更有可能在其他项目之前或之后出现在其他项目之前或之后.
In OP's example, because they don't use a constant seed number, they technically have a sample size of 1. The impact with recurring indexes is lessened as the row count increases, and there is a higher chance that other items will appear before or after other items when they wouldn't have previously.
我从使用RAND()得出的一些警告:
A couple of caveats I've worked out from using RAND():
如果RAND()仅用作SELECT列或WHERE条件，并且指定了LIMIT，则RAND()将仅为返回的每一行生成一次.
If RAND() is only used as a SELECT column or WHERE condition, and a LIMIT is specified, then RAND() will only be generated once for each of the returned rows.
CREATE TABLE t (i INT);
INSERT INTO t VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
SELECT i, RAND(0) FROM t LIMIT 0, 1; # 0, 0.15522042769493574
SELECT i, RAND(0) FROM t LIMIT 1, 1; # 1, 0.15522042769493574
如果在ORDER BY语句中使用了RAND()，则将为所有匹配的行计算RAND()，而不考虑任何LIMIT.
If RAND() is used in the ORDER BY statement, then RAND() will be calculated for all matching rows regardless of any LIMIT.
CREATE TABLE t (i INT);
INSERT INTO t VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
SELECT i, RAND(0) FROM t ORDER BY RAND(0) LIMIT 0, 1; # 0, 0.15522042769493574
SELECT i, RAND(0) FROM t ORDER BY RAND(0) LIMIT 1, 1; # 6, 0.2964166321758336
这篇关于MySQL RAND()种子值几乎重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
