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來處理
  }
}
           

結果在此處一直提示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 is

, 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_names

system 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(, );
        -> '12,332.1235'
mysql> SELECT FORMAT(,);
        -> '12,332.1000'
mysql> SELECT FORMAT(,);
        -> '12,332'
mysql> SELECT FORMAT(,,'de_DE');
        -> '12.332,20'
           

該函數作用将一個浮點數根據第二個參數進行四舍五入的儲存小數點位的格式化,格式化之後傳回的是一個字元串類型，而且該字元串，每個三個數加上了一個逗号，看起來像是Decimal類型，但實際上是Decimal類型的一個字元串，是以在Java代碼中轉化錯誤。要解決該問題，其實也很簡單，在代碼中将所有的逗号替換為空字元串就好了，然後對替換好的字元串進行轉化為浮點數類型即可。

try{ // 當年累計收入總和累加
  String income = p.getString("HJTR").replaceAll(",", "");//替換到','後可以轉化為double
  totalIncomSum += Double.parseDouble(income);
}catch(Exception e2){
  totalIncomSum += ;
}
           

Cast和Convert函數

一開始沒找到問題所在，使用

CAST(expr AS type)

和

CONVERT(expr USING transcoding_name)

兩個函數進行字元串到Decimal的轉化，但是一直損失精度，反複試驗發現該兩個方法不能講帶逗号的的字元串轉化為期望的格式。

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

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

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

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

可發現這兩種方法轉化一個浮點數的時候回将精度損失掉，隻保留整數部分。下面這種情況也是容易出錯的。

SELECT FORMAT(,)  FROM DUAL -- 

SELECT CAST(FORMAT(,) AS DECIMAL)   FROM DUAL -- 

SELECT CONVERT(FORMAT(,) ,DECIMAL)  FROM DUAL -- 
           

如果背景的某個查詢值式計算出來的，那麼盡可能的使用

CAST

和

Convert

直接處理，而不要使用

Format

處理過之後在使用前者，否則會損失精度。

另外多看官方文檔，能學到很多東西，–官方文檔傳送門–