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

说明

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

举例说明

例子

• 现有一个表test,有id,name,money 三列,测试数据如下

  id	name	money
  1	苹果	5.00000
  2	小米手机	1999.99000
  3	李子	3.00000

• money 列保留3位小数的同时,并计算money的总值
  1. 先格式化每列的3位小数
     代码如下
     SELECT id,NAME,FORMAT(money,3) money FROM test
     结果

     id	name	money
     1	苹果	5.000
     2	小米手机	1,999.990
     3	李子	3.000

  2. 在这个基础上算总值
     SELECT SUM(money) FROM (SELECT id,NAME,FORMAT(money,3) money FROM test ) t
     结果

     sum(money)

     9

说明

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

解决办法

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