MySQL常用数值函数小结

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊常见的数值函数

函数名	函数用途
mod	取余
ceil	向上取整
floor	向下取整
round	四舍五入
truncate	截断函数
rand	随机数
greatest	求最大值
least	求最小值

一.mod

mod取余函数
语法:MOD(N,M), N % M, N MOD M

mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
|         1 |
+-----------+
1 row in set (0.01 sec)

mysql> select 10%3;
+------+
| 10%3 |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select 10 mod 3;
+----------+
| 10 mod 3 |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

二.ceil

CEIL向上取整函数
CEIL() 是CEILING()的同义词，两个都可以使用
ceil语法: ceil(X)

--正数向上取证
mysql> select ceil(1.1);
+-----------+
| ceil(1.1) |
+-----------+
|         2 |
+-----------+
1 row in set (0.01 sec)
--负数是找到比它大的整数,这个要注意
mysql> select ceil(-1.1);
+------------+
| ceil(-1.1) |
+------------+
|         -1 |
+------------+
1 row in set (0.00 sec)
--对于百位向上取整，可以先除再乘
mysql> select ceil(135/100)*100;
+-------------------+
| ceil(135/100)*100 |
+-------------------+
|               200 |
+-------------------+
1 row in set (0.00 sec)

三.floor

floor语句是向下取整，与ceil相反
floor语法:FLOOR(X)

--正数向下取证
mysql> select floor(1.1);
+------------+
| floor(1.1) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)
--负数是找到比它小的整数,这个要注意
mysql> select floor(-1.1);
+-------------+
| floor(-1.1) |
+-------------+
|          -2 |
+-------------+
1 row in set (0.00 sec)
--对于百位向下取整，可以先除再乘
mysql> select floor(123/100)*100;
+--------------------+
| floor(123/100)*100 |
+--------------------+
|                100 |
+--------------------+
1 row in set (0.00 sec)

四.round

round() 四舍五入
语法:ROUND(X), ROUND(X,D)

--默认四舍五入整数
mysql> select round(1.4);
+------------+
| round(1.4) |
+------------+
|          1 |
+------------+
1 row in set (0.01 sec)

mysql> select round(1.5);
+------------+
| round(1.5) |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)
--默认四舍五入整数，负数的时候注意
mysql> select round(-1.4);
+-------------+
| round(-1.4) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(-1.5);
+-------------+
| round(-1.5) |
+-------------+
|          -2 |
+-------------+
1 row in set (0.00 sec)
--还可以指定保留小数的为主
mysql> select round(1.245,2);
+----------------+
| round(1.245,2) |
+----------------+
|           1.25 |
+----------------+
1 row in set (0.00 sec)

五.truncate

truncate() 截断函数
语法:TRUNCATE(X,D)

--truncate截断指定位数之后的小数，不会四舍五入
mysql> select truncate(1.4,0);
+-----------------+
| truncate(1.4,0) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> select truncate(1.5,0);
+-----------------+
| truncate(1.5,0) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)
--truncate截断指定位数之后的小数，对负数也一样
mysql> select truncate(-1.4,0);
+------------------+
| truncate(-1.4,0) |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)

mysql> select truncate(-1.5,0);
+------------------+
| truncate(-1.5,0) |
+------------------+
|               -1 |
+------------------+
1 row in set (0.00 sec)
--truncate截断指定位数之后的小数，位数可以是负数，这样就可以截断整数部分
mysql> select truncate(123,-1);
+------------------+
| truncate(123,-1) |
+------------------+
|              120 |
+------------------+
1 row in set (0.01 sec)

mysql> select truncate(123,-2);
+------------------+
| truncate(123,-2) |
+------------------+
|              100 |
+------------------+
1 row in set (0.00 sec)

六.rand

RAND() 随机数，
语法:RAND([N])
rand返回的值 0 <= v < 1.0
如果要返回区间:
7 <= R < 12 – SELECT FLOOR(7 + (RAND() * 5));

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

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

mysql> SELECT FLOOR(7 + (RAND() * 5));
+-------------------------+
| FLOOR(7 + (RAND() * 5)) |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(7 + (RAND() * 5));
+-------------------------+
| FLOOR(7 + (RAND() * 5)) |
+-------------------------+
|                       9 |
+-------------------------+
1 row in set (0.00 sec)

七.greatest

greatest 求最大值，如有null，则返回null
greatest语法:greatest(n1,n2,n3…)

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

mysql> select greatest(1,2,3);
+-----------------+
| greatest(1,2,3) |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.00 sec)

mysql> select greatest(1,2,3,null);
+----------------------+
| greatest(1,2,3,null) |
+----------------------+
|                 NULL |
+----------------------+
1 row in set (0.00 sec)

八.least

least 求最大值，如有null，则返回null
least语法:greatest(n1,n2,n3…)

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

mysql> select least(1,2,3);
+--------------+
| least(1,2,3) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select least(1,2,3,null);
+-------------------+
| least(1,2,3,null) |
+-------------------+
|              NULL |
+-------------------+
1 row in set (0.00 sec)