MySQL 常用函数

RAND() 函数

MySQL RAND() 函数可以被调用，产生一个在 0 和 1 之间的浮点数

mysql> SELECT RAND(), RAND(), RAND();
+--------------------+--------------------+--------------------+
| RAND()             | RAND()             | RAND()             |
+--------------------+--------------------+--------------------+
| 0.5983982343211753 | 0.8651665978740589 | 0.5306383171404138 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

当使用整数作为参数调用时，RAND() 使用该值作为随机数的种子发生器。每次种子使用给定值生成，RAND() 将产生一个可重复的系列数字

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() 语句还可以对表中的数据进行随机读取

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() 函数

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)

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)

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() 函数

mysql> SELECT MD5(NULL), MD5('123456');
+-----------+----------------------------------+
| MD5(NULL) | MD5('123456')                    |
+-----------+----------------------------------+
| NULL      | e10adc3949ba59abbe56e057f20f883e |
+-----------+----------------------------------+
1 row in set (0.00 sec)

生成一到一百的随机数字

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 生成随机字符串

mysql> SELECT SUBSTRING(MD5(RAND()), 1, 24);
+-------------------------------+
| SUBSTRING(MD5(RAND()), 1, 24) |
+-------------------------------+
| 46939819a45d1166e4e8f184      |
+-------------------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME() 函数

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 范围内的随机日期

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