MySQL的rand()函数使用总结

rand函数使用

使用rand()函数，会产生 [0-1) 的随机小数。

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.08175316553726156 |
+---------------------+
1 row in set (0.00 sec)

mysql> select rand();
+---------------------+
| rand()              |
+---------------------+
| 0.13951215626626476 |
+---------------------+
1 row in set (0.00 sec)

如果使用相同的随机数种子值来生成随机数，那么结果将是相同的随机数。

mysql> select rand(10);
+--------------------+
| rand(10)           |
+--------------------+
| 0.6570515219653505 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand(10);
+--------------------+
| rand(10)           |
+--------------------+
| 0.6570515219653505 |
+--------------------+
1 row in set (0.00 sec)

mysql> select rand(5);
+---------------------+
| rand(5)             |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)

mysql> select rand(5);
+---------------------+
| rand(5)             |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)

注意：随机数种子相同，但是行不同，得到的随机数会不相同。

mysql> SELECT i, RAND(3) FROM t;
 +------+------------------+
 | i    | RAND(3)          |
 +------+------------------+
 |    1 | 0.90576975597606 |
 |    2 | 0.37307905813035 |
 |    3 | 0.14808605345719 |
 +------+------------------+

生成[5，10)之间的随机整数

select (rand() * 5) +  5;
 # 推导过程
 # rand() --> 属于[0,1),
 # rand() * 5 --> 属于[0,5) ,达不到5，
 # (rand() * 5) +  5 --> 属于[5,10)，达不到10
 ​
 mysql> select (rand() * 5) +  5;
 +-------------------+
 | (rand() * 5) +  5 |
 +-------------------+
 | 7.455957888118883 |
 +-------------------+
 1 row in set (0.00 sec)
 ​
 mysql> select (rand() * 5) +  5;
 +-------------------+
 | (rand() * 5) +  5 |
 +-------------------+
 |  9.29574444358772 |
 +-------------------+
 1 row in set (0.00 sec)

生成[5，10]之间的随机整数

select floor(rand() * 6 + 5) 
 # 推导过程
 # rand() --> 属于[0,1),
 # rand() * 6 --> 属于[0,6) ,达不到6，可以达到5
 # rand() * 6 + 5 --> 属于[5,11) ,达不到11，可以达到10
 # floor(rand() * 6 + 5) --> 属于[5,10]
 ​
 mysql> select floor(rand() * 6 + 5);
 +-----------------------+
 | floor(rand() * 6 + 5) |
 +-----------------------+
 |                     5 |
 +-----------------------+
 1 row in set (0.00 sec)
 ​
 mysql> select floor(rand() * 6 + 5);
 +-----------------------+
 | floor(rand() * 6 + 5) |
 +-----------------------+
 |                    10 |
 +-----------------------+
 1 row in set (0.00 sec)

案例

通过数据库的RAND函数，生成一个六位数的随机验证码。

# 推导过程
# rand() * 1000000 --> 属于[0, 1000000)
# floor(rand() * 1000000) --> 属于 [0, 999999] 的整数
# lpad(floor(rand() * 1000000), 6, 0) --> 如果随机数小于1000000, 前面填充0

mysql> select lpad(floor(rand() * 1000000), 6, 0);
+---------------------------------------+
| lpad(floor(rand() * 1000000), 6, 0) |
+---------------------------------------+
| 811873                                |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select lpad(floor(rand() * 1000000), 6, 0);
+---------------------------------------+
| lpad(floor(rand() * 1000000), 6, 0) |
+---------------------------------------+
| 721657                                |
+---------------------------------------+
1 row in set (0.00 sec)