场景
财务相关的数据存储通常会用到小数,那么一般使用decimal类型来存储,如计算税率、利润率等,我们一般通过程序去计算,但是在导表等某些场景下也可以通过MySQL内置函数来实现。
语法
round(x, n) 四舍五入,保留n位小数,跟php一样一样的
truncate(x, n) 保留n位小数,但是不四舍五入
使用
--如果不写小数位,那就是整数四舍五入
select round(1.09821);
+----------------+
| round(1.09821) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
select round(1.89821);
+----------------+
| round(1.89821) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)
--四舍五入
select round(1.09821, 2);
+-------------------+
| round(1.09821, 2) |
+-------------------+
| 1.10 |
+-------------------+
1 row in set (0.00 sec)
select round(1.99121, 2);
+-------------------+
| round(1.99121, 2) |
+-------------------+
| 1.99 |
+-------------------+
1 row in set (0.00 sec)
--truncate 不会四舍五入
select truncate(1.09821, 2);
+----------------------+
| truncate(1.09821, 2) |
+----------------------+
| 1.09 |
+----------------------+
1 row in set (0.00 sec)
select truncate(1.09121, 2);
+----------------------+
| truncate(1.09121, 2) |
+----------------------+
| 1.09 |
+----------------------+
1 row in set (0.00 sec)