获取整数函数

1、返回大于或等于数值x的最小整数

语法：ceil(x);

mysql> select ceil(4.3),ceil(-2.5);
+-----------+------------+
| ceil(4.3) | ceil(-2.5) |
+-----------+------------+
| 5 | -2 |
+-----------+------------+
1 row in set (0.01 sec)

2、返回小于或等于数值x的最大整数

语法：floor(x);

mysql> select floor(4.3),floor(-2.5);
+------------+-------------+
| floor(4.3) | floor(-2.5) |
+------------+-------------+
| 4 | -3 |
+------------+-------------+
1 row in set (0.00 sec)

3、截取数值函数

语法：truncate(x,y);

返回数值x，保留小数点后y位。

mysql> select truncate(999.345,2),truncate(999.345,-1);/*不四舍五入*/
+---------------------+----------------------+
| truncate(999.345,2) | truncate(999.345,-1) |
+---------------------+----------------------+
| 999.34 | 990 |
+---------------------+----------------------+
1 row in set (0.00 sec)

mysql> select truncate(999.345,2),truncate(999.999,-1);
+---------------------+----------------------+
| truncate(999.345,2) | truncate(999.999,-1) |
+---------------------+----------------------+
| 999.34 | 990 |
+---------------------+----------------------+
1 row in set (0.00 sec)

4、四舍五入函数

语法：round(x);

函数返回值x经过四舍五入操作后的数值。

mysql> select round(999.999);/*取整*/
+----------------+
| round(999.999) |
+----------------+
| 1000 |
+----------------+
1 row in set (0.00 sec)

mysql> select round(903.5),round(-903.4);/*取整*/
+--------------+---------------+
| round(903.5) | round(-903.4) |
+--------------+---------------+
| 904 | -903 |
+--------------+---------------+
1 row in set (0.00 sec)

语法：round(x,y);

返回数据x保留到小数点后y位的值，在具体截取数据时需要进行四舍五入的操作。

mysql> select round(903.53,2),round(903.54,-1);
+-----------------+------------------+
| round(903.53,2) | round(903.54,-1) |
+-----------------+------------------+
| 903.53 | 900 |
+-----------------+------------------+
1 row in set (0.00 sec)

mysql> select round(903.54,0);
+-----------------+
| round(903.54,0) |
+-----------------+
| 904 |
+-----------------+
1 row in set (0.00 sec)

mysql> select truncate(903.54,0);
+--------------------+
| truncate(903.54,0) |
+--------------------+
| 903 |
+--------------------+
1 row in set (0.00 sec)