MySQL：选择大于随机数的行会产生奇怪的结果

Suppose one has a table of 1000 entries. We want to pick a random number between 1 and 1000 and return all rows with an id greater than or equal to that value. It appears to work just fine when entering a static value. For example

假设有一个包含1000个条目的表。我们想要选择1到1000之间的随机数,并返回id大于或等于该值的所有行。输入静态值时似乎工作得很好。例如

Select * From myTable
Where id >= 500
Order by id Limit 10;

This returns the expected values of 500 through 509. However, as soon as I try to replace that fixed value of 500 with a randomly generated one, things become very strange.

这会返回500到509的预期值。但是,一旦我尝试用随机生成的值替换500的固定值,事情就会变得非常奇怪。

Select * From myTable
Where id >= floor(1 + RAND() * (1000 - 1))
Order by id Limit 10;

Suddenly, it no longer returns sequential values. The values returned are also extremely biased towards the low end. I get results like this:

突然间,它不再返回顺序值。返回的值也极度偏向低端。我得到这样的结果:

65, 80, 96, 98, 112, 114, 115, 116, 130, 131

Why does this happen?

为什么会这样?

3 个解决方案

#1

This is why your random changes....

这就是你随机改变的原因....

  mysql> select id,rand() from test_big;
    +-------------+----------------------+
    | id          | rand()               |
    +-------------+----------------------+
    |          10 |   0.9478371384999129 |
    |          11 | 0.024267499357711057 |
    |   123456789 |   0.2778261120224615 |
    |  1234567890 |   0.3163280927728192 |
    | 12345678901 |   0.7481621585303565 |
    +-------------+----------------------+
    5 rows in set (0.00 sec)

the random is execute for each row.....but you can use a variable to solve your problems....

随机执行每一行.....但你可以使用一个变量来解决你的问题....

mysql> set @random:= rand();
Query OK, 0 rows affected (0.00 sec)

mysql> select @random;
+--------------------+
| @random            |
+--------------------+
| 0.7918265450669699 |
+--------------------+
1 row in set (0.00 sec)

mysql> select id,@random:=@random+1 from test_big;
+-------------+--------------------+
| id          | @random:=@random+1 |
+-------------+--------------------+
|          10 |   1.79182654506697 |
|          11 | 2.7918265450669697 |
|   123456789 | 3.7918265450669697 |
|  1234567890 |   4.79182654506697 |
| 12345678901 |   5.79182654506697 |
+-------------+--------------------+
5 rows in set (0.00 sec)

in your case make

在你的情况下,使

set @random:=floor(1 + RAND() * (1000 - 1));

before the select function

在选择功能之前

#2

The random number in the WHERE clause is calculated separately for each row. So you don't have one random number to filter your results, but 1000 random numbers - one for each entry.

WHERE子句中的随机数是针对每一行单独计算的。因此,您没有一个随机数来过滤结果,而是1000个随机数 - 每个条目一个。

#3

Because the random function in the where clause gets evaluated every time and thus generating a new random number each time.

因为where子句中的随机函数每次都被评估,因此每次都会生成一个新的随机数。

#1