以十进制列存储货币 - 什么精度和规模？ [英] Storing money in a decimal column - what precision and scale?
问题描述
我使用十进制列在数据库上存储货币值，今天我想知道使用的精度和规模。
一个固定的宽度更有效率，我想同样可以是真正的十进制列。是吗？
我应该使用什么精度和规模？我在想精确24/8。这是多余的，不够还是确定？
这是我决定做的：
- 将转换率（如果适用）作为浮动存储在交易表中
- 将货币存储在
- 所有交易金额均为
DECIMAL（19,4）使用转化率的计算将由我的应用程序处理，因此我保持对舍入问题的控制
我不认为转换率是一个问题，因为它主要用于参考，我将把它转换为小数。
感谢您的宝贵意见。
如果你正在寻找一个适合所有的人，我建议 DECIMAL 4）是一个流行的选择（一个快速的Google承载这个）。我认为这源自旧的VBA /访问/ Jet货币数据类型，作为语言中的第一个固定点十进制类型; 十进制仅在VB6 / VBA6 / Jet 4.0中以版本1.0样式（即未完全实现）。
固定点十进制值的存储的经验法则是至少存储一个小于实际需要的小数位数，以允许舍入。将前端的 Currency 类型映射到 DECIMAL（19，4）的原因之一是后端是货币展示银行家的四舍五入性质，而 DECIMAL（p，s）舍去舍入。 / p>
DECIMAL 的存储空间中另外一个小数位可以实现自定义舍入算法，而不是采用供应商的默认对于设计师来说，期望所有以.5结尾的值都从零开始），银行家的取整是令人震惊的。
是， DECIMAL（24，8）听起来像对我太过分了。大多数货币报价为四位或五位小数。我知道需要8（或更多）的十进制比例的情况，但这是一个正常货币金额（比如四个小数位）已经按比例，这意味着小数精度应该相应地减少（在这种情况下也考虑浮点类型）。现在没有人有这么多钱要求24的十进制精度：）
然而，一个研究可能是一个适合所有的方法，为了。请向您的设计师或领域专家询问可能适用的会计规则：GAAP，EU等。我不清楚地回忆一些欧盟国内转移，明确规定四舍五入到小数点后五位，因此使用 DECIMAL ，6）用于存储。会计师通常似乎偏爱四个小数位。
PS避免SQL Server的 MONEY 数据类型，因为它在舍入时具有严重的精度问题，以及其他因素，如可移植性等。请参阅 Aaron Bertrand的博客。
微软和语言设计师选择银行家的四舍五入，因为硬件设计师选择了它[引文？]。它被包含在例如电气和电子工程师协会（IEEE）标准中。硬件设计师选择它，因为数学家喜欢它。请参阅维基百科;释义：1906版的概率和错误理论称之为计算机规则（计算机，意为执行计算的人）。
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?
And what precision and scale should I use? I was thinking precision 24/8. Is that overkill, not enough or ok?
This is what I've decided to do:
- Store the conversion rates (when applicable) in the transaction table itself, as a float
- Store the currency in the account table
- The transaction amount will be a
DECIMAL(19,4) - All calculations using a conversion rate will be handled by my application so I keep control of rounding issues
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.
Thank you all for your valuable input.
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.
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.
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).
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 :)
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 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.
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屋！
