【MySQL】内置函数——数学函数+其他函数

一. 数字函数

---

• 绝对值——abs()
• 向上取整——ceiling()
• 向下取整——floor()

mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
|       100.2 |
+-------------+
//变得更大
mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
|             24 |
+----------------+
//变得更小
mysql> select floor(22.99);
+--------------+
| floor(22.99) |
+--------------+
|           22 |
+--------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

---

• 十进制转换二进制——bin()
• 转换成十六进制——hex()
• 进制转换——conv()

//十进制转二进制
mysql> select bin(7);
+--------+
| bin(7) |
+--------+
| 111    |
+--------+
//转换成十六进制
mysql> select hex(23);
+---------+
| hex(23) |
+---------+
| 17      |
+---------+
1 row in set (0.00 sec)
//将16进制的23转换成十进制数字
mysql> select conv(23,16,10);
+----------------+
| conv(23,16,10) |
+----------------+
| 35             |
+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

---

精度控制——format()
约分按照四舍五入的规则

mysql> select format(12.3456,2);
+-------------------+
| format(12.3456,2) |
+-------------------+
| 12.35             |
+-------------------+
1 row in set (0.00 sec)

mysql> select format(12.3446,2);
+-------------------+
| format(12.3446,2) |
+-------------------+
| 12.34             |
+-------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

产生随机数——rand()

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

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6162809968146319 |
+--------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

rand()配合format()使用

mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 28                   |
+----------------------+

mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 56                   |
+----------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

二. 其他函数

---

• ifnull()

mysql> select ifnull('abc',123);
+-------------------+
| ifnull('abc',123) |
+-------------------+
| abc               |
+-------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null,123);
+------------------+
| ifnull(null,123) |
+------------------+
|              123 |
+------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14

---

• 形成md5摘要——md5()

md5摘要是HTTPS协议的一部分

mysql> select md5('abc');
+----------------------------------+
| md5('abc')                       |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+
1
2
3
4
5
6

• 给用户加密——password()

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1
2
3
4
5
6