mysql的数学函数
1.abs 绝对值函数,该函数支持使用BIGINT值
mysql> select abs(-36);
+----------+
| abs(-36) |
+----------+
| 36 |
+----------+
2.sign 返回参数作为-1、0或1的符号,该符号取决于X的值为负、零或正
mysql> select sign(-32),sign(32),sign(0);
+-----------+----------+---------+
| sign(-32) | sign(32) | sign(0) |
+-----------+----------+---------+
| -1 | 1 | 0 |
+-----------+----------+---------+
3.round(x),round(x,D)
返回参数X,其值接近于最近似的整数。在有两个参数的情况下,返回X,其值保留到小数点后D位,而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D位,可将D设为负值。
mysql> select round(1.25),round(-1.25),round(2.5),round(25e-1);
+-------------+--------------+------------+--------------+
| round(1.25) | round(-1.25) | round(2.5) | round(25e-1) |
+-------------+--------------+------------+--------------+
| 1 | -1 | 3 | 2 |
+-------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> select round(12.53,0),round(12.53,1),round(12.53,-1);
+----------------+----------------+-----------------+
| round(12.53,0) | round(12.53,1) | round(12.53,-1) |
+----------------+----------------+-----------------+
| 13 | 12.5 | 10 |
+----------------+----------------+-----------------+
注意:如果有警告,可以show warnings;查看。
4.FLOOR(X) 返回不大于X的最大整数值
mysql> select floor(-1.54),floor(1.55);
+--------------+-------------+
| floor(-1.54) | floor(1.55) |
+--------------+-------------+
| -2 | 1 |
+--------------+-------------+
注意:负数的大小。
5.format(x,D) 将数字X 的格式写成'#,###,###.##'格式, 即保留小数点后 D位,而第D位的保留方式为四舍五入,然后将结果以字符串的形式返回
mysql> select format(121.235498738649,-1),format(1.235498738649,5);
+-----------------------------+--------------------------+
| format(121.235498738649,-1) | format(1.235498738649,5) |
+-----------------------------+--------------------------+
| 121 | 1.23550 |
+-----------------------------+--------------------------+