mysql文档中 是这么说的 :
RAND() RAND(N)
返回一个随机浮点值 v ,范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。 给一个固定的参数 rand()的值固定 如:
select rand(1),rand(1),rand(1)
union all
select rand(1),rand(1),rand(1);
输出没有问题但当我用这个时select rand(1) from staff;(staff是一个包含多条记录的表),
输出的每个结果都是不一样的 这是怎么回事呢?
每个结果按说都该是rand(1)算出来的 但怎么会不一样呢?求指导……
RAND() RAND(N)
返回一个随机浮点值 v ,范围在 0 到1 之间 (即, 其范围为 0 ≤ v ≤ 1.0)。若已指定一个整数参数 N ,则它被用作种子值,用来产生重复序列。 给一个固定的参数 rand()的值固定 如:
select rand(1),rand(1),rand(1)
union all
select rand(1),rand(1),rand(1);
输出没有问题但当我用这个时select rand(1) from staff;(staff是一个包含多条记录的表),
输出的每个结果都是不一样的 这是怎么回事呢?
每个结果按说都该是rand(1)算出来的 但怎么会不一样呢?求指导……
mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i | RAND(3) |
+------+------------------+
| 1 | 0.90576975597606 |
| 2 | 0.37307905813035 |
| 3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.01 sec)
为什么rand(3)重复算后会出现不同的值
SELECT i FROM table会有3个值
2楼举例子时加的
mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 0 rows affected (0.42 sec)mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> SELECT i, RAND() FROM t;
+------+------------------+
| i | RAND() |
+------+------------------+
| 1 | 0.61914388706828 |
| 2 | 0.93845168309142 |
| 3 | 0.83482678498591 |
+------+------------------+
3 rows in set (0.00 sec)mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i | RAND(3) |
+------+------------------+
| 1 | 0.90576975597606 |
| 2 | 0.37307905813035 |
| 3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.00 sec)mysql> SELECT i, RAND() FROM t;
+------+------------------+
| i | RAND() |
+------+------------------+
| 1 | 0.35877890638893 |
| 2 | 0.28941420772058 |
| 3 | 0.37073435016976 |
+------+------------------+
3 rows in set (0.00 sec)mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i | RAND(3) |
+------+------------------+
| 1 | 0.90576975597606 |
| 2 | 0.37307905813035 |
| 3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.01 sec)
With a constant initializer, the seed is initialized once when the statement is compiled, prior to execution.
mysql> select rand(2) from courier_t where courier_id<2;
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
+-------------------+
1 row in set (0.00 sec)mysql> select rand(2) from courier_t where courier_id<3;
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
| 0.122346619258026 |
+-------------------+
2 rows in set (0.00 sec)mysql> select rand(2) from courier_t where courier_id<4;
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
| 0.122346619258026 |
| 0.64497318737672 |
+-------------------+
3 rows in set (0.00 sec)mysql> select rand(2) from courier_t where courier_id<5;
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
| 0.122346619258026 |
| 0.64497318737672 |
| 0.857826109843167 |
+-------------------+
4 rows in set (0.00 sec)mysql> select rand(2) from courier_t where courier_id<6;
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
| 0.122346619258026 |
| 0.64497318737672 |
| 0.857826109843167 |
| 0.354211017819318 |
+-------------------+
5 rows in set (0.00 sec)