【MySQL】随机函数 rand()
1:随机函数rand()
1 2 3 4 5 6 7 8 9 10 |
## 返回 0 - 1 的随机数 mysql> select rand(); +---------------------+ | rand() | +---------------------+ | 0.26020521415416636 | +---------------------+ 1 row in set (0.00 sec) mysql> |
2:返回1 – 100的随机数
1 2 3 4 5 6 7 8 9 10 |
## MySQL只提供了这么一个随机函数,所以,想要1-100的,那么只需*100即可 mysql> select rand() * 100; +-------------------+ | rand() * 100 | +-------------------+ | 96.31548570125875 | +-------------------+ 1 row in set (0.00 sec) mysql> |
3:取整
如果我们想要取整数,可以通过函数转换一下,因为是随机数,所以,那个函数转换都是无所谓的。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
## ceil(n) 或 ceiling(n) 返回不小于n的最小整数,也就是向上取整 mysql> select ceiling(rand() * 100); +-----------------------+ | ceiling(rand() * 100) | +-----------------------+ | 96 | +-----------------------+ 1 row in set (0.00 sec) mysql> ## floor(n) 不大于n的最大整数值 mysql> select floor(rand() * 100); +---------------------+ | floor(rand() * 100) | +---------------------+ | 53 | +---------------------+ 1 row in set (0.00 sec) mysql> ## round(n) 四舍五入 mysql> select round(rand() * 100); +---------------------+ | round(rand() * 100) | +---------------------+ | 28 | +---------------------+ 1 row in set (0.16 sec) mysql> |
4:小结
1)在测试时经常会用到随机数。
2)在排序是经常会用到随机数,当查询的数据量较大时,谨慎使用,可能会带来灾难。
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from t order by rand(); +----+------------------+ | id | list | +----+------------------+ | 3 | ,1,25,33,49,56, | | 4 | ,11,12,13,14,15, | | 2 | ,9,12,13,28,32, | | 1 | ,1,2,3,4,5, | +----+------------------+ 4 rows in set (0.00 sec) mysql> |
3)两个数(x – y)之间的随机数公式:select (ceil(rand() * (y-x+1)) + (x-1));