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

一. 数字函数

函数名称	描述
abs()	绝对值函数
bin()	十进制转换二进制
hex()	转换成十六进制
conv(number,from_base,to_base)	将number从from_base转换成to_base进制
ceiling()	向上取整
floor()	向下取整
format(number,decimal_places)	格式化，保留decimal_placese位小数
rand()	返回随机浮点数，范围[0.0 , 1.0)
mod(number,denominator)	取模，求余

---

• 绝对值——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 |
+--------------+

---

• 十进制转换二进制——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             |
+----------------+

---

精度控制——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             |
+-------------------+

---

产生随机数——rand()

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

rand()配合format()使用

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

二. 其他函数

函数名称	描述
user()	查询当前用户
md5()	对字符串形成md5摘要
database()	显示当前使用的数据库
password()	MySQL使用该函数对用户加密
ifnull(val1,val2)	如果val1是NULL，则返回val2，否则返回val1

---

• 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 |
+------------------+

---

• 形成md5摘要——md5()

md5摘要是HTTPS协议的一部分

mysql> select md5('abc');
+----------------------------------+
| md5('abc')                       |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+

• 给用户加密——password()

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+