将钱存入小数列 - 什么精度和比例? [英] Storing money in a decimal column - what precision and scale?

问题描述

我使用十进制列在数据库中存储货币值，今天我想知道要使用什么精度和比例.

I'm using a decimal column to store money values on a database, and today I was wondering what precision and scale to use.

由于假定固定宽度的字符列更有效，我认为十进制列也是如此.是吗?

Since supposedly char columns of a fixed width are more efficient, I was thinking the same could be true for decimal columns. Is it?

我应该使用什么样的精度和比例?我在想 24/8 的精确度.这是矫枉过正，还不够还是还可以?

And what precision and scale should I use? I was thinking precision 24/8. Is that overkill, not enough or ok?

这是我决定做的:

• 将兑换率(如果适用)存储在交易表本身中，作为浮点数
• 将货币存储在帐户表中
• 交易金额将为 DECIMAL(19,4)
• 所有使用转换率的计算都将由我的应用程序处理，因此我可以控制舍入问题

我认为转换率的浮点数不是问题，因为它主要用于参考，无论如何我都会将其转换为小数.

I don't think a float for the conversion rate is an issue, since it's mostly for reference, and I'll be casting it to a decimal anyway.

感谢大家的宝贵意见.

推荐答案

如果您正在寻找一种万能的，我建议 DECIMAL(19, 4) 是一个流行的选择(一个快速的谷歌证实了这一点).我认为这源于旧的 VBA/Access/Jet Currency 数据类型，它是该语言中的第一个定点十进制类型；Decimal 仅在 VB6/VBA6/Jet 4.0 中以1.0 版"样式出现(即未完全实现).

If you are looking for a one-size-fits-all, I'd suggest DECIMAL(19, 4) is a popular choice (a quick Google bears this out). I think this originates from the old VBA/Access/Jet Currency data type, being the first fixed point decimal type in the language; Decimal only came in 'version 1.0' style (i.e. not fully implemented) in VB6/VBA6/Jet 4.0.

存储定点十进制值的经验法则是存储至少比您实际需要的小数位多一位以允许四舍五入.将前端的旧 Currency 类型映射到后端的 DECIMAL(19, 4) 类型的原因之一是 Currency表现出银行家的四舍五入本质，而 DECIMAL(p, s) 被截断四舍五入.

The rule of thumb for storage of fixed point decimal values is to store at least one more decimal place than you actually require to allow for rounding. One of the reasons for mapping the old Currency type in the front end to DECIMAL(19, 4) type in the back end was that Currency exhibited bankers' rounding by nature, whereas DECIMAL(p, s) rounded by truncation.

DECIMAL 存储中的额外小数位允许实施自定义舍入算法而不是采用供应商的默认值(银行家的舍入至少可以说是令人担忧的，对于期望所有以 .5 结尾的值从零舍入).

An extra decimal place in storage for DECIMAL allows a custom rounding algorithm to be implemented rather than taking the vendor's default (and bankers' rounding is alarming, to say the least, for a designer expecting all values ending in .5 to round away from zero).

是的，DECIMAL(24, 8) 对我来说听起来有点矫枉过正.大多数货币的报价都精确到小数点后四位或五位.我知道需要 8(或更多)十进制小数的情况，但这是按比例分配正常"货币金额(例如四位小数)的情况，这意味着小数精度应该相应地减少(在这种情况下也考虑浮点类型).现在没有人有那么多钱来要求小数精度为 24 :)

Yes, DECIMAL(24, 8) sounds like overkill to me. Most currencies are quoted to four or five decimal places. I know of situations where a decimal scale of 8 (or more) is required but this is where a 'normal' monetary amount (say four decimal places) has been pro rata'd, implying the decimal precision should be reduced accordingly (also consider a floating point type in such circumstances). And no one has that much money nowadays to require a decimal precision of 24 :)

但是，可能需要进行一些研究，而不是一刀切的方法.向您的设计师或领域专家询问可能适用的会计规则:GAAP、EU 等.我依稀记得一些欧盟国家内部转移，其中包含四舍五入到小数点后五位的明确规则，因此使用 DECIMAL(p, 6) 用于存储.会计师通常似乎喜欢小数点后四位.

However, rather than a one-size-fits-all approach, some research may be in order. Ask your designer or domain expert about accounting rules which may be applicable: GAAP, EU, etc. I vaguely recall some EU intra-state transfers with explicit rules for rounding to five decimal places, therefore using DECIMAL(p, 6) for storage. Accountants generally seem to favour four decimal places.

PS 避免 SQL Server 的 MONEY 数据类型，因为它在舍入时存在严重的准确性问题，以及可移植性等其他考虑因素.请参阅 Aaron Bertrand 的博客.

PS Avoid SQL Server's MONEY data type because it has serious issues with accuracy when rounding, among other considerations such as portability etc. See Aaron Bertrand's blog.

微软和语言设计师选择银行家的舍入是因为硬件设计师选择了它[引文?].例如，它被载入电气和电子工程师协会 (IEEE) 标准.硬件设计师选择它是因为数学家更喜欢它.参见维基百科；释义:1906 年版的《概率与误差理论》称此为计算机规则"(计算机"是指执行计算的人).

Microsoft and language designers chose banker's rounding because hardware designers chose it [citation?]. It is enshrined in the Institute of Electrical and Electronics Engineers (IEEE) standards, for example. And hardware designers chose it because mathematicians prefer it. See Wikipedia; to paraphrase: The 1906 edition of Probability and Theory of Errors called this 'the computer's rule' ("computers" meaning humans who perform computations).

这篇关于将钱存入小数列 - 什么精度和比例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！