mysql rand()產生隨機整數範圍及方法

標籤：

根據官方文檔，rand()的取值範圍為[0,1)

在Mysql中可以執行如下命令查看：

mysql> ? randName: ‘RAND‘Description:Syntax:RAND(), RAND(N)Returns a random floating-point value v in the range 0 <= v < 1.0. If aconstant integer argument N is specified, it is used as the seed value,which produces a repeatable sequence of column values. In the followingexample, note that the sequences of values produced by RAND(3) is thesame both places where it occurs.URL: http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.htmlExamples:mysql> CREATE TABLE t (i INT);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)

 

若要在i ≤ R ≤ j 這個範圍得到一個隨機整數R ，需要用到運算式 FLOOR(i + RAND() * (j – i + 1))
例如， 若要在7 到 12 的範圍（包括7和12）內得到一個隨機整數, 可使用以下語句：
SELECT FLOOR(7 + (RAND() * 6));

 

另外，也可以用round四捨五入函數來實現，考慮到最前與最後的取值機率會與中間的不相等，故加上0.5來消除這種機率上的差異，達到均勻分布：

產生[i,j]範圍正整數：select round(rand()*(j-i+1)+i-0.5)

若要在7 到 12 的範圍（包括7和12）內得到一個隨機整數, 可使用以下語句：
SELECT round( (RAND() * 6+6.5));

如果產生負整數的話得注意最左邊的值有可能會超過你的要求：

mysql> SELECT round(-0.5);+-------------+| round(-0.5) |+-------------+|          -1 |+-------------+1 row in set (0.00 sec)mysql> SELECT round(-0.4);+-------------+| round(-0.4) |+-------------+|           0 |+-------------+1 row in set (0.00 sec)

 

mysql rand()產生隨機整數範圍及方法