MySQL的数值转化问题

MySQL的数值转化问题

由于业务需要,在查询表的时候有一些值是需要使用函数计算出来的带精度的Decimal类型，但是这些值到Java代码中发现不能使用Deciaml类型来接受，提示cast Exception。思来想去，百度，博客，查看官方文档终于解决该问题；

场景

try{
  totalIncomNum += Double.parseDouble(p.getString("HJTR"));//转换失败
}catch(Exception e2){
  try{//转化为BigDecimal
    totalIncomNum += ((BigDecimal)p.get("HJTR")).doubleValue();
  }catch(Exception e3){
    totalIncomNum += 0;//还会转化失败就按照0来处理
  }
}

结果在此处一直提示String Can Not Cast to double ….，结果查看MyBatis的XML中的SQL语句发现，是因为SQL中使用了一个Formate(exp,num)函数，在本地做实验发现该函数是将一个浮点数进行格式化。

Format函数

该函数一般有两个参数，第三个参数是指明本地语言的，默认是en_US ,查看官方文档可知：

FORMAT(X,D[,locale])

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is0, the result has no decimal point or fractional part.

The optional third parameter enables a locale to be specified to be used for the result number’s decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_namessystem variable (see Section 10.15, “MySQL Server Locale Support”. If no locale is specified, the default is 'en_US'.

注意：

官方文档中说了and returns the result as a string.返回的是一个String类型,所以导致前台无法直接用Decimal来接收。

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'

该函数作用将一个浮点数根据第二个参数进行四舍五入的保存小数点位的格式化,格式化之后返回的是一个字符串类型，而且该字符串，每个三个数加上了一个逗号，看起来像是Decimal类型，但实际上是Decimal类型的一个字符串，所以在Java代码中转化错误。要解决该问题，其实也很简单，在代码中将所有的逗号替换为空字符串就好了，然后对替换好的字符串进行转化为浮点数类型即可。

try{ // 当年累计收入总和累加
  String income = p.getString("HJTR").replaceAll(",", "");//替换到','后可以转化为double
  totalIncomSum += Double.parseDouble(income);
}catch(Exception e2){
  totalIncomSum += 0;
}

Cast和Convert函数

一开始没找到问题所在，使用CAST(expr AS type)和CONVERT(expr USING transcoding_name) 两个函数进行字符串到Decimal的转化，但是一直损失精度，反复试验发现该两个方法不能讲带逗号的的字符串转化为期望的格式。

SELECT CONVERT('123.456',DECIMAL)  FROM DUAL -- 123 

SELECT CONVERT('123.456',SIGNED)  FROM DUAL -- 123

SELECT CAST('123.456' AS DECIMAL) FROM DUAL -- 123

SELECT CAST('123.456' AS SIGNED) FROM DUAL -- 123

可发现这两种方法转化一个浮点数的时候回将精度损失掉，只保留整数部分。下面这种情况也是容易出错的。

SELECT FORMAT(123.678,2)  FROM DUAL -- 123.68

SELECT CAST(FORMAT(123.678,2) AS DECIMAL)   FROM DUAL -- 124

SELECT CONVERT(FORMAT(123.678,2) ,DECIMAL)  FROM DUAL -- 124

如果后台的某个查询值式计算出来的，那么尽可能的使用CAST和Convert直接处理，而不要使用Format处理过之后在使用前者，否则会损失精度。

另外多看官方文档，能学到很多东西，–官方文档传送门–