mysql常用函数
1
2
3
4
5
6
7
8
| 函数 | 功能
abs(x) | 返回x的绝对值
ceil(x) | x向上取整
floor(x) | 向下取整
mod(x,y) | 返回x/y的模
rand() | 返回0到1的随机值
round(x,y) | 返回四舍五入值
truncate(x,y) | 返回x截断y位小数的结果
|
ABS 返回数值的绝对值
1
2
3
4
5
6
7
| mysql> select abs(-0.8),abs(0.8);
+-----------+----------+
| abs(-0.8) | abs(0.8) |
+-----------+----------+
| 0.8 | 0.8 |
+-----------+----------+
1 row in set (0.01 sec)
|
CEIL 返回向上取整值
1
2
3
4
5
6
7
| mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.00 sec)
|
FOOLR向下取整
1
2
3
4
5
6
7
| mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)
|
MOD(x,y) 返回x/y的模
和x%y的结果相同，模数和被模数任何一个null结果都为null
1
2
3
4
5
6
7
| mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
| 5 | 1 | NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)
|
RAND() 返回0到1的随机值
1
2
3
4
5
6
7
| mysql> select rand(),rand();
+--------------------+---------------------+
| rand() | rand() |
+--------------------+---------------------+
| 0.2072962515124085 | 0.21381375492905616 |
+--------------------+---------------------+
1 row in set (0.00 sec)
|
ROUND(x,y) 返回x的四舍五入有y位小数的值
1
2
3
4
5
6
7
| mysql> select round(rand(),2),round(rand(),5);
+-----------------+-----------------+
| round(rand(),2) | round(rand(),5) |
+-----------------+-----------------+
| 0.63 | 0.37040 |
+-----------------+-----------------+
1 row in set (0.00 sec)
|
TRUNCATE(x,y) 返回截取y未的x值
1
2
3
4
5
6
7
| mysql> select round(1.2345,2),truncate(1.2345,2);
+-----------------+--------------------+
| round(1.2345,2) | truncate(1.2345,2) |
+-----------------+--------------------+
| 1.23 | 1.23 |
+-----------------+--------------------+
1 row in set (0.00 sec)
|
