RAND() 函数
MySQL RAND() 函数可以被调用，产生一个在 0 和 1 之间的浮点数
1
2
3
4
5
6
7
| mysql> SELECT RAND(), RAND(), RAND();
+
| RAND() | RAND() | RAND() |
+
| 0.5983982343211753 | 0.8651665978740589 | 0.5306383171404138 |
+
1 row in set (0.00 sec)
|
当使用整数作为参数调用时，RAND() 使用该值作为随机数的种子发生器。每次种子使用给定值生成，RAND() 将产生一个可重复的系列数字
1
2
3
4
5
6
7
| mysql> SELECT RAND(1), RAND(1), RAND();
+
| RAND(1) | RAND() | RAND() |
+
| 0.18109050223705 | 0.75023211143001 | 0.20788908117254 |
+
1 row in set (0.00 sec)
|
使用 ORDER BY RAND() 语句还可以对表中的数据进行随机读取
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
| mysql> SELECT * FROM employee_tbl ORDER BY RAND();
+
| id | name | work_date | daily_typing_pages |
+
| 5 | Zara | 2007-06-06 | 300 |
| 3 | Jack | 2007-04-06 | 100 |
| 3 | Jack | 2007-05-06 | 170 |
| 2 | Ram | 2007-05-27 | 220 |
| 4 | Jill | 2007-04-06 | 220 |
| 5 | Zara | 2007-02-06 | 350 |
| 1 | John | 2007-01-24 | 250 |
+
7 rows in set (0.01 sec)
mysql> SELECT * FROM employee_tbl ORDER BY RAND();
+
| id | name | work_date | daily_typing_pages |
+
| 5 | Zara | 2007-02-06 | 350 |
| 2 | Ram | 2007-05-27 | 220 |
| 3 | Jack | 2007-04-06 | 100 |
| 1 | John | 2007-01-24 | 250 |
| 4 | Jill | 2007-04-06 | 220 |
| 3 | Jack | 2007-05-06 | 170 |
| 5 | Zara | 2007-06-06 | 300 |
+
7 rows in set (0.00 sec)
|
FLOOR()、ROUND() 函数
1
2
3
4
5
6
7
| mysql> SELECT FLOOR(1.58), FLOOR(-1.58), FLOOR(0);
+
| FLOOR(1.58) | FLOOR(-1.58) | FLOOR(0) |
+
| 1 | -2 | 0 |
+
1 row in set (0.00 sec)
|
1
2
3
4
5
6
7
| mysql> SELECT ROUND(-1.23), ROUND(-1.58), ROUND(1.58), ROUND(1.298, 1);
+
| ROUND(-1.23) | ROUND(-1.58) | ROUND(1.58) | ROUND(1.298, 1) |
+
| -1 | -2 | 2 | 1.3 |
+
1 row in set (0.00 sec)
|
1
2
3
4
5
6
7
| mysql> SELECT ROUND(1.298, 0), ROUND(23.298, -1), ROUND(150.000,2), ROUND(150,2);
+
| ROUND(1.298, 0) | ROUND(23.298, -1) | ROUND(150.000,2) | ROUND(150,2) |
+
| 1 | 20 | 150 | 150 |
+
1 row in set (0.00 sec)
|
MD5() 函数
1
2
3
4
5
6
7
| mysql> SELECT MD5(NULL), MD5('123456');
+
| MD5(NULL) | MD5('123456') |
+
| NULL | e10adc3949ba59abbe56e057f20f883e |
+
1 row in set (0.00 sec)
|
生成一到一百的随机数字
1
2
3
4
5
6
7
| mysql> SELECT FLOOR(RAND() * 100 + 0), FLOOR(RAND() * 100 + 0);
+
| FLOOR(RAND() * 100 + 0) | FLOOR(RAND() * 100 + 0) |
+
| 48 | 81 |
+
1 row in set (0.00 sec)
|
MD5 生成随机字符串
1
2
3
4
5
6
7
| mysql> SELECT SUBSTRING(MD5(RAND()), 1, 24);
+
| SUBSTRING(MD5(RAND()), 1, 24) |
+
| 46939819a45d1166e4e8f184 |
+
1 row in set (0.00 sec)
|
FROM_UNIXTIME() 函数
1
2
3
4
5
6
7
8
9
10
| mysql> SELECT
-> FROM_UNIXTIME(-1) AS 'A',
-> FROM_UNIXTIME(0) AS 'B',
-> FROM_UNIXTIME(1483200000) AS 'C',
-> FROM_UNIXTIME(1483200000, '%Y-%m-%d') AS 'D';
+
| A | B | C | D |
+
| NULL | 1970-01-01 08:00:00 | 2017-01-01 00:00:00 | 2017-01-01 |
+
|
生成 2017-01-01 ~ 2018-01-01 范围内的随机日期
1
2
3
4
5
6
7
| mysql> SELECT FROM_UNIXTIME(1483200000 + ROUND(RAND() * 60 * 60 * 24 * 365));
+
| FROM_UNIXTIME(1483200000 + ROUND(RAND() * 60 * 60 * 24 * 365)) |
+
| 2017-12-12 07:16:57 |
+
1 row in set
|