图片 1

MySQL小记——数据格式化

1.数值四舍五入,小数点后保留2位

  记录下今天在项目中出现的一个小问题。

round()
函数是四舍五入用,第一个参数是我们要被操作的数据,第二个参数是设置我们四舍五入之后小数点后显示几位。

  将通过除运算获得的结果数据进行保留两位小数的处理时,我用了MySQL 的
FORMAT(X,
D)函数,之前一直没有出现问题,但是由于周期性更新的数据库中突然出现了一个绝对值很大的负数(如下异常中的-58535.53),用基于spring 的
Java 代码将查询结果展示在jsp页面时发生如下异常:

numeric 函数的2个参数,第一个表示数据长度,第二个参数表示小数点后位数。

图片 1

示例如下:

很明显是因为数据格式转换的问题,仔细看发现小数点向前3位处使用了逗号进行了分割。之前一直以为
FORMAT(X, D) 
函数只是用来处理小数位取舍的,由于在绝对值较小的时候不会出现逗号进行分割,所以并没有去深究其用法。今天突然出现的问题让我有点儿懵,在Navicat客户端查询也显示有逗号分割,思前想后却始终没想到这个函数其实是格式化函数,最终还是请教项目组长帮我看出来的,他直接来一句“你把它格式化干嘛!”。一语惊醒梦中人,遂立刻百度,发现FORMAT(X,
D) 其实是按 ‘ #,###,###.##
‘ 这样的格式对数据进行格式化的函数,函数参数 X
就是要格式化的数据,D 表示经过四舍五入后小数点后保留几位;如果数值的整数部分超过3位,则会从小数点往前每3位以逗号进行分割,而且数值本身也会被格式化为字符串类型。所以,在我的程序中,参数D的值为2,所以才会得到
-58,535.53 的结果,而在程序中我是通过结果集对象rs.getDouble() 来获取数据的,所以会出现类型转换错误。

-- 4.56
select cast(round(4.564,2) as numeric(5,2));
-- 4.57
select cast(round(4.565,2) as numeric(5,2));
-- 4.57
select cast(round(4.566,2) as numeric(5,2));
-- 将 numeric 转换为数据类型 numeric 时出现算术溢出错误。1234.567 长度为7,大于5,故报错。
select cast(round(1234.567,2)  as numeric(5,2));
-- 1234.57
select cast(round(1234.567,2)  as numeric(18,2));

  我的解决方法是将FORMAT(X,D) 函数替换为 ROUND(X,
D),二者在数值处理上是一样的,即都会按照“四舍五入”对小数位进行处理,但ROUND不会将数值的整数部分格式化为固定类型的字符串,而是保留纯数值形式。

 

  此外,在小数位处理上,除了FORMAT(X, D) 和 ROUND(X, D)
外,与它们相近的还有TRUNCATE(X, D) 函数,只是该函数会直接按指定的
D 对数值进行截取,而不会进行“四舍五入”处理。例如,TRUNCATE(30.8923,
1) 的结果为30.8,而不是30.9。