mysql rand_mysql rand()产生随机整数范围及方法

根据官方文档，rand()的取值范围为[0,1)

若要在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.00sec)

mysql> SELECT round(-0.4);

+-------------+

| round(-0.4) |

+-------------+

| 0 |

+-------------+

1 row in set (0.00 sec)

可以在产生正整数的情况下再减去一个相应的值，得到一个负整数范围，不过用floor函数可以避免上面的问题。

在Mysql中可以执行如下命令查看某个主题的说明文档，即? 主题，如下：

mysql> ? rand

Name: 'RAND'

Description:

Syntax:

RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If a

constant integer argument N is specified, it is used as the seed value,

which produces a repeatable sequence of column values. In the following

example, note that the sequences of values produced by RAND(3) is the

same both places where it occurs.

URL: http://dev.mysql.com/doc/refman/5.6/en/mathematical-functions.html

Examples:

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: 0

mysql> 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)