金额字段该怎么设计？——给小白的超详细指南（含示例 SQL）
做业务系统时，金额字段（如 purchase_price 采购价、sale_price 销售价、min_price 最低价）到底该用什么类型、保留几位小数、要不要加约束？这篇一文讲清楚，读完你就能自己定规范了。
一、结论先行
-
金额一律用
DECIMAL(M,D)（定点数），不要用FLOAT/DOUBLE（浮点数）。 -
常用推荐：
- 价格类：
DECIMAL(10,2)（最多 99999999.99 元） - 价格（需要更高精度/跨币种）：
DECIMAL(12,4)或DECIMAL(14,4) - 费率/税率（0~1）：
DECIMAL(6,5)（如0.12345） - 折扣（0~100%）：
DECIMAL(5,2)（如85.50表示 85.5 折）
- 价格类：
-
你的现有设计
DECIMAL(24,6)非常保守且偏大（可到百万亿级、精确到 0.000001 元）。如果业务金额没那么夸张，可收缩到DECIMAL(10,2)/DECIMAL(12,4)，既够用又省存储、算得更快。
二、为什么金额必须用 DECIMAL
- 浮点误差：
FLOAT/DOUBLE以二进制近似存储，0.1 + 0.2可能不等于0.3，用在金额上会出现分毫差错，汇总后就离谱。 DECIMAL是定点十进制：按十进制位存储与计算，无二进制小数误差，适合财务场景。
小贴士：MySQL 中
DECIMAL(M,D)表示 总位数 M，小数位 D。
例如DECIMAL(24,6)：整数位最多 18 位，小数 6 位，最大约为999,999,999,999,999,999.999999。
三、M 和 D 怎么选？
- 看金额上限（历史+未来）：最大订单总额、外汇结算、大客户年框等。
- 看精度需求：电商多数到 分（2 位小数）；有些涉及多币种/加权平均/手续费时需到 厘或万分位（3~4 位或更多）。
- 建议：能小就小。位数越大，存储、索引、计算成本越高。
常见组合速查表
| 场景 | 字段示例 | 推荐类型 |
|---|---|---|
| 标价/成交价/进货价 | sale_price, purchase_price, min_price | DECIMAL(10,2)；如需高精度可 DECIMAL(12,4) |
| 税率/手续费率（0~1） | tax_rate, fee_rate | DECIMAL(6,5) |
| 折扣（0~100） | discount_percent | DECIMAL(5,2) |
| 汇率 | exchange_rate | DECIMAL(14,6) 或 DECIMAL(18,8)（看业务） |
| 数量（支持小数） | qty | DECIMAL(12,4)（或按行业习惯） |
| 总金额/累计金额 | amount_total | 与价格相同或略大一档 |
四、和业务字段的对应关系
你给的三列：
purchase_price DECIMAL(24,6) -- 采购价格
sale_price DECIMAL(24,6) -- 销售价格
min_price DECIMAL(24,6) -- 最低价格
如果你的业务没有超大金额且结算精度不需要到 6 位小数，建议改为：
- 通用：
DECIMAL(10,2) - 要更高精度：
DECIMAL(12,4)
五、显示 2 位 ≠ 存储 2 位
很多场景 存储更高精度（如 4 位），展示/结算按 2 位四舍五入。
这样能避免多次加总后再舍入造成的累计误差。
示例：存 4 位、显 2 位
SELECTROUND(sale_price, 2) AS sale_price_show
FROM product;
六、约束、默认值与负数
- NOT NULL + DEFAULT 0.00：避免空值参与计算。
- UNSIGNED：如果你的业务不允许负价，可加上（退款/调价若需负数则不要）。
- CHECK 约束（MySQL 8.0.16+ 才真正生效）：限制范围更严谨。
- 不建议
ZEROFILL：仅影响显示，且会自动变为无符号，容易误导。
七、索引与查询
-
金额字段常用于范围查询（如「100~200 元」），BTree 索引有效。
-
避免在 WHERE 里对列做函数运算（会失去索引），而是在比较值上动手脚。
- ✅
WHERE sale_price >= 100.00 AND sale_price < 200.00 - ❌
WHERE ROUND(sale_price, 2) BETWEEN 100 AND 200
- ✅
八、计算与舍入的“坑”
- 统一舍入策略：建议全局使用
ROUND(x, 2)；对业务要件明确四舍五入、银行家舍入还是向上/向下取整。 - 计算顺序：尽量先在更高精度下计算，到最后一步再
ROUND到展示/结算精度。 - SUM/AVG：对
DECIMAL聚合通常仍返回DECIMAL。但为保险起见，在关键结算点显式 ROUND。
示例：订单行 -> 订单汇总
-- 行金额（内部保留 4 位）
unit_price DECIMAL(12,4),
qty DECIMAL(12,4),
line_amt DECIMAL(16,6) GENERATED ALWAYS AS (unit_price * qty) VIRTUAL,-- 订单总额（最终结算 2 位）
SELECT order_id, ROUND(SUM(line_amt), 2) AS order_total
FROM order_item
GROUP BY order_id;
九、与后端语言的配合
-
Java：用
BigDecimal，禁用double。- 金额字段用
BigDecimal映射；@Column(precision=12, scale=4)。
- 金额字段用
-
JavaScript/Node：用
decimal.js、big.js等库，或**用整数“分/厘”**做计算。 -
Go：使用
shopspring/decimal或以整数存分。 -
无论何种语言，不要在金额计算上用浮点类型。
十、迁移缩型的安全做法（从 24,6 → 12,4/10,2）
-
盘点现有数据范围
SELECTMAX(sale_price) AS max_price,MIN(sale_price) AS min_price,MAX(ABS(sale_price - ROUND(sale_price, 4))) AS max_scale_loss FROM product; -
评估是否会溢出/丢精度（比如超过
99999999.9999就不能降到12,4）。 -
灰度迁移：先加新列，写入转换后的值，对读写做双写/读新列验证无误，再切表/换列。
-
最后清理旧列。
十一、完整示例（价格 4 位精度，展示 2 位）
CREATE TABLE product (id BIGINT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,-- 存储更高精度purchase_price DECIMAL(12,4) NOT NULL DEFAULT 0.0000 COMMENT '采购价格，元',sale_price DECIMAL(12,4) NOT NULL DEFAULT 0.0000 COMMENT '销售价格，元',min_price DECIMAL(12,4) NOT NULL DEFAULT 0.0000 COMMENT '最低价格，元',-- 生成列：含税价（例如默认税率 13%）tax_rate DECIMAL(6,5) NOT NULL DEFAULT 0.13000 COMMENT '税率',price_with_tax DECIMAL(14,6) GENERATED ALWAYS AS (sale_price * (1 + tax_rate)) VIRTUAL,-- 约束（MySQL 8.0.16+）CONSTRAINT chk_prices_nonneg CHECK (purchase_price >= 0 AND sale_price >= 0 AND min_price >= 0),CONSTRAINT chk_min_le_sale CHECK (min_price <= sale_price),KEY idx_sale_price (sale_price)
) COMMENT='商品表，价格用 DECIMAL，内部高精度，展示再四舍五入';
查询展示给用户时：
SELECTid, name,ROUND(purchase_price, 2) AS purchase_price,ROUND(sale_price, 2) AS sale_price,ROUND(min_price, 2) AS min_price
FROM product;
十二、实战建议清单（可直接抄到规范里）
- 金额字段一律
DECIMAL，禁止FLOAT/DOUBLE。 - 价格用
DECIMAL(10,2)；若需更高精度/跨币种计算，用DECIMAL(12,4)或更高。 - 汇率、费率、折扣等字段按场景单独设定精度，避免混用。
- 存储精度 ≥ 计算精度 ≥ 展示精度，最终展示/结算统一
ROUND。 - 金额列
NOT NULL DEFAULT 0.00，是否UNSIGNED由业务是否允许负值决定。 - 关键结算点显式
ROUND，并在代码中统一封装。 - 避免在 WHERE 里对金额列做函数运算，保留索引可用。
- 版本是 MySQL 8.0.16+ 的话，善用
CHECK约束；否则在应用层校验。 - 若极致性能需求，可并行存“分”的整数列（如
sale_price_cents BIGINT），但要确保和DECIMAL列单一事实来源、一致性。 - 变更精度前必须做数据范围审计与灰度迁移。