MySQL DECIMAL 聚合避免精度损失的字段设计

MySQL 做金额、计量或结算汇总时，字段类型比聚合函数本身更先决定结果是否可靠。实用做法是：原始值用 DECIMAL(M,D) 存储，M 预留足够的整数位与小数位，聚合阶段不要提前转成 DOUBLE，最后只在展示或明确的业务结算点使用 ROUND()。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

这篇只解决“DECIMAL 聚合怎样避免精度损失”的字段设计问题，示例使用订单明细金额和数量，重点看输入类型、聚合表达式与最终舍入之间的边界。

先把 DECIMAL 的精确范围设计出来

MySQL 把 DECIMAL 视为定点精确数值，DECIMAL(M,D) 中的 M 是总位数，D 是小数位数，整数部分最多占用 M-D 位。金额字段常见的错误不是少写一个聚合函数，而是字段一开始只留了“当前够用”的整数位，或者把业务金额定义成 DOUBLE 后再在查询里补救。

例如需要保存最大 9,999,999,999.9999 的单价，可考虑 DECIMAL(18,4)：整数部分 14 位，小数部分 4 位。不要只按当前样本的最大值倒推类型，还要估计未来调价、退款、汇率换算或批量汇总后的整数位。DECIMAL(10,2) 的整数部分只有 8 位，超过范围时，严格 SQL 模式应让写入失败，而不是默默把错误带入汇总。

用字段类型守住聚合表达式

表结构可以把金额和数量分开表达，金额使用定点数，数量如果允许小数也使用定点数；不要把金额先存成字符串再依赖隐式转换。

-- 金额保留 4 位小数，给未来单价和汇总预留整数位
CREATE TABLE order_item (
    id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    quantity DECIMAL(18,4) NOT NULL,
    -- 业务金额由应用计算后保存，便于和明细追溯
    line_amount DECIMAL(22,4) NOT NULL,
    created_at DATETIME NOT NULL
);

-- 聚合阶段保持 DECIMAL，结算展示时才按两位小数舍入
SELECT order_id,
       SUM(line_amount) AS amount_exact,
       ROUND(SUM(line_amount), 2) AS amount_for_display
FROM order_item
GROUP BY order_id;

这里的 amount_exact 是继续参与结算、对账或导出的中间结果，amount_for_display 才是按两位展示的结果。若业务规则要求“每行先四舍五入再汇总”，则应明确写成 SUM(ROUND(line_amount, 2))；它和 ROUND(SUM(line_amount), 2) 不是同一件事。

另外，传入表达式的值也要注意类型。精确数值与带小数点的定点字面量可以保持精确计算；带科学计数法的字面量、DOUBLE 列或不受控的字符串转换可能把表达式带入近似计算。应用参数绑定时，应让驱动按定点数或字符串的明确规则传入，并在边界处做业务层格式检查。

SUM、AVG 和除法不要提前舍入

SUM() 适合保留更高精度的中间结果，AVG() 本质上还涉及除法和结果尺度，因此“先聚合、后舍入”通常更容易解释。比如计算某个客户的平均客单价：

-- 先得到精确总额和数量，再在输出层保留 2 位
SELECT customer_id,
       SUM(line_amount) AS total_amount,
       SUM(line_amount) / NULLIF(SUM(quantity), 0) AS average_price,
       ROUND(SUM(line_amount) / NULLIF(SUM(quantity), 0), 2) AS average_price_2dp
FROM order_item
GROUP BY customer_id;

NULLIF 用来避免数量为零时直接除零；它不会替你决定“无销量时显示 0、NULL 还是不出报表”，这个结果约定应由业务确认。只有在明确的会计或计费规则要求逐行舍入时，才把 ROUND() 放到聚合之前，并为差额归属制定规则。

字段设计的四个边界要提前写清

• 整数位预算：按单笔上限、累计行数和可能的汇率/数量放大估算，不要只看今天的数据。
• 小数位规则：价格、税率、重量和汇率的小数位可能不同，先定义业务精度，再决定 D。
• 舍入位置：明确是“明细先舍入”还是“总额后舍入”，两者的差额要能解释。
• 空值与零：SUM() 的空集合、零数量和缺失金额分别代表什么，不能用一个默认值覆盖。

最后可用下面这张速查表统一评审：

常见追问

DECIMAL(18,4) 一定适合金额吗？

不一定。它只是一个示例，是否合适取决于最大整数位、小数精度和聚合规模；税率、汇率和重量可能需要不同的 D。

为什么 ROUND(SUM(x), 2) 和 SUM(ROUND(x, 2)) 不一样？

前者先把全部明细相加再舍入，后者先改变每一行的值再相加。财务规则若未明确，优先保留精确中间结果并把舍入位置写进接口或报表约定。