现状描述:
一般数值我们保留两位小数或者1位小数,相当于精度为 0.1 或者0.01,那么round的时候是四舍五入 round(a, 1) or round(a,2) 就结束。
但是,我有很多很多的数据,是两位小数的,既要粒度细,又要粒度不这么细,还不要粒度粗到四舍五入怎么办?比如想要 0.2 一档,或者0.5一档,做一些数据的分组。
可以用下图的方式:
小数分离:
具体实现方法是将这个数值的整数与小数分离,小数除以档位保留两位小数然后再四舍五入保留一位小数,再承回去,如图描述和步骤。
小数整数分离不需要使用转化成varchar的形式,再搞什么截取,麻烦人,MySQL有自带函数,floor, 取整数,然后再把原值减去这个整数不就好了吗!!
select col
,floor(col)+round(round((col-floor(col))/2,2),1)*2 as new_col-- /2, *2精度归并到0.2
,floor(col)+round(round((col-floor(col))/5,2),1)*5 as new_col-- /5, *5精度归并到0.5
from some_table
潜在问题
但是,有一个问题要注意!!
字段类型是float 或者double会导致精度缺失,假设计算出来 cal_col=0.25 是float类型,
select round(0.25,1) as res 出来0.3,
select round(new_col,1) as res 出来0.2 !!!
为什么?怎么办?
根本原因
MySQL 中的 ROUND()
函数对浮点数执行四舍五入操作时,浮点数的二进制表示会导致精度误差。浮点数(FLOAT
或 DOUBLE
)在计算机中使用二进制表示,有些十进制的小数不能被精确表示,这会导致四舍五入时的结果与预期不同。
例如:
- 0.25 在二进制中无法精确表示,其存储为接近 0.249999... 的值。
- 因此,
ROUND(0.25, 1)
在某些情况下可能被解释为接近 0.2,而不是四舍五入到 0.3。
问题分析
SELECT cal_col, ROUND(cal_col, 1) AS cal_col, ROUND(0.25, 1) AS res FROM your_table;
结果:
cal_col | cal_col | res |
---|---|---|
0.25 | 0.2 | 0.3 |
-
cal_col
的来源问题:- 如果
cal_col
是从表中读取的字段,字段的类型可能是FLOAT
或DOUBLE
。 - 浮点类型会有精度误差,因此
ROUND(cal_col, 1)
可能会对接近 0.25 的值错误地处理为 0.2。
- 如果
-
直接使用常量
0.25
的问题:- 在
ROUND(0.25, 1)
的情况下,MySQL 直接使用常量进行四舍五入。 - 由于常量
0.25
在这里可能被 MySQL 解释为DECIMAL
类型(精度更高),因此结果更准确,返回0.3
。
- 在
解决方法
方法 1:将浮点类型转换为 DECIMAL
使用 CAST
或 CONVERT
将 cal_col
转换为 DECIMAL
类型,可以避免浮点数的精度误差。
SELECT cal_col, ROUND(CAST(cal_col AS DECIMAL(10, 2)), 1) AS col, ROUND(0.25, 1) AS res FROM your_table;
方法 2:修改字段类型
如果表中字段 cal_col
是 FLOAT
或 DOUBLE
类型,可以将其改为 DECIMAL
类型(精确存储数字的小数部分)。
ALTER TABLE your_table MODIFY COLUMN cal_col DECIMAL(10, 2);
方法 3:设置 SQL 模式为高精度模式
你可以尝试启用 MySQL 的高精度处理模式(比如 STRICT
模式),这有时可以改进四舍五入的行为:
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
方法 4:添加 FORMAT()
函数
如果仅仅是为了展示结果,可以使用 FORMAT()
函数,它会按照字符串格式化的方式输出精确的小数:
SELECT cal_col, FORMAT(cal_col, 1) AS cal_col, FORMAT(0.25, 1) AS res FROM your_table;
验证结果
测试数据(cal_col=0.25
),使用上述方法可以解决 ROUND()
的问题。
附加说明:浮点数的存储误差
- 浮点数的存储(
FLOAT
或DOUBLE
)存在精度问题,特别是在小数部分较多的情况下。- 例如:
0.25
在计算机的浮点表示中可能是0.249999...
。
- 例如:
DECIMAL
类型存储数字时不会有这种问题,因为它是以字符串方式存储数字值并执行精确的数学运算。- 推荐:如果你希望对小数进行精确运算,请尽量使用
DECIMAL
类型,而不是FLOAT
或DOUBLE
。