mysql format函数造成的计算时的精度缺失问题

女爷i 2023-01-12 12:59 98阅读 0赞

## 说明 ##

**问题原因**:mysql formt函数会对数字进行每3位加一个逗号  
例如:一个数 `10000000` 使用format函数格式化后会变成 `10,000,000`  
**问题描述**:当某个表有一个金额列 money 类型为 decimal(12,5) , 此时页面上需要展示3位小数,合计列也是如此,这是 用 format(money,3) 格式化每一列都正常, 但是统计总计的时候就出问题了 ,sum(format 过的金额列) 算出的金额(在某一列金额超过3位数的时候format 会给其添加逗号,会出现问题 就会很离谱)

## 举例说明 ##

### 例子 ###

*  现有一个表test,有id,name,money 三列,测试数据如下
    
    <table> 
     <tbody> 
      <tr> 
       <td>id</td> 
       <td>name</td> 
       <td>money</td> 
      </tr> 
      <tr> 
       <td>1</td> 
       <td>苹果</td> 
       <td>5.00000</td> 
      </tr> 
      <tr> 
       <td>2</td> 
       <td>小米手机</td> 
       <td>1999.99000</td> 
      </tr> 
      <tr> 
       <td>3</td> 
       <td>李子</td> 
       <td>3.00000</td> 
      </tr> 
     </tbody> 
    </table>
 *  money 列保留3位小数的同时,并计算money的总值
    
    1.  先格式化每列的3位小数  
        代码如下  
        `SELECT id,NAME,FORMAT(money,3) money FROM test`  
        结果
        
        <table> 
         <tbody> 
          <tr> 
           <td>id</td> 
           <td>name</td> 
           <td>money</td> 
          </tr> 
          <tr> 
           <td>1</td> 
           <td>苹果</td> 
           <td>5.000</td> 
          </tr> 
          <tr> 
           <td>2</td> 
           <td>小米手机</td> 
           <td>1,999.990</td> 
          </tr> 
          <tr> 
           <td>3</td> 
           <td>李子</td> 
           <td>3.000</td> 
          </tr> 
         </tbody> 
        </table>
    2.  在这个基础上算总值  
        `SELECT SUM(money) FROM (SELECT id,NAME,FORMAT(money,3) money FROM test ) t`  
        结果
        
        <table> 
         <tbody> 
          <tr> 
           <td>sum(money)</td> 
          </tr> 
          <tr> 
           <td>9</td> 
          </tr> 
         </tbody> 
        </table>

### 说明 ###

通过上述例子,说明 format 函数最好只在进行合计列计算后展示的时候格式化使用,不然会出现很大的问题,直接将精度算没了

## 解决办法 ##

一般数字的转换和格式化,还是建议使用如下方法,不会对数字进行加逗号处理  
`convert(列名, 数据类型如:decimal(12,2))`  
`cast(列名 as 数据类型如:decimal(12,2))`  
`ROUND(列名或值,精度)`