现在有个需求是从表中随机取出一条数据，除了全部拿出数据用程序来随机外，还可以使用数据库的 RAND() 函数。
RAND() 函数可以返回一个 0~1 的随机数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| > select RAND();
+--------------------+
| RAND() |
+--------------------+
| 0.8503168074882745 |
+--------------------+
1 row in set (0.00 sec)
> select RAND();
+--------------------+
| RAND() |
+--------------------+
| 0.6016649581507453 |
+--------------------+
1 row in set (0.00 sec)
|
RAND() 还可以给一个数字参数，以该数字作为基准来生成一个可重复的随机数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| > select RAND(5);
+---------------------+
| RAND(5) |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)
> select RAND(5);
+---------------------+
| RAND(5) |
+---------------------+
| 0.40613597483014313 |
+---------------------+
1 row in set (0.00 sec)
|
官方定义的该参数的取值范围是 0~1，但实测超过该范围也可以得到结果。
如果想要得到一个随机的整数，则可以运用数学运算和 FLOOR() 函数，比如想要一个 [10,20] 区间的随机数
1
2
3
4
5
6
7
| > select FLOOR(10 + (RAND() * 11));
+---------------------------+
| FLOOR(10 + (RAND() * 11)) |
+---------------------------+
| 18 |
+---------------------------+
1 row in set (0.00 sec)
|
最后回来点一下题，如何获取表中的随机数据？
这里需要配合排序语句 ORDER BY RAND() 来使用，可以获取一个按照随机排序的列表。
1
2
3
4
5
6
7
8
9
10
11
| > SELECT * FROM USER ORDER BY RAND();
+----+--------+----------+--------------+--------------+---------------------+---------------------+
| id | name | password | ext_property | is_available | create_ts | update_ts |
+----+--------+----------+--------------+--------------+---------------------+---------------------+
| 4 | spring | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
| 1 | wxnacy | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
| 2 | win | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
| 3 | xiao | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
| 5 | 温 | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
+----+--------+----------+--------------+--------------+---------------------+---------------------+
5 rows in set (0.00 sec)
|
最后再配合 LIMIT 来获取随机的一条数据
1
2
3
4
5
6
7
| > SELECT * FROM USER ORDER BY RAND() LIMIT 1;
+----+--------+----------+--------------+--------------+---------------------+---------------------+
| id | name | password | ext_property | is_available | create_ts | update_ts |
+----+--------+----------+--------------+--------------+---------------------+---------------------+
| 2 | win | | {} | 1 | 2018-03-02 11:04:11 | 2018-03-02 11:04:11 |
+----+--------+----------+--------------+--------------+---------------------+---------------------+
1 rows in set (0.00 sec)
|