SQL 将钱存储在十进制列中 - 什么精度和规模？

Storing money in a decimal column - what precision and scale?

提问by Ivan

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?

我应该使用什么样的精度和规模？我在想 24/8 的精确度。这是矫枉过正，不够还是可以？

---

---

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

• 将转换率（如果适用）存储在交易表本身中，作为浮点数
• 将货币存储在帐户表中
• 交易金额将是 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.

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

Thank you all for your valuable input.

感谢大家的宝贵意见。

回答by onedaywhen

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; Decimalonly came in 'version 1.0' style (i.e. not fully implemented) in VB6/VBA6/Jet 4.0.

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

The rule of thumb for storageof 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 Currencytype in the front end to DECIMAL(19, 4)type in the back end was that Currencyexhibited bankers' rounding by nature, whereas DECIMAL(p, s)rounded by truncation.

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

An extra decimal place in storage for DECIMALallows 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允许实施自定义舍入算法，而不是采用供应商的默认值（银行家的舍入至少可以说是令人担忧的，对于希望以 0.5 结尾的所有值从零舍入的设计者而言） .

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) isrequired 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 :)

是的，DECIMAL(24, 8)对我来说听起来有点矫枉过正。大多数货币的报价都精确到小数点后四位或五位。我知道在哪里十进制数8规模（或以上）的情况下是需要的，但是这就是一个“正常”的货币量（说小数点后四位）一直亲rata'd，寓意小数精度应相应减少（也考虑在这种情况下的浮点类型）。现在没有人有那么多钱来要求小数精度为 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.

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

---

---

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

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

---

---

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).

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

回答by Marcus Downing

We recently implemented a system that needs to handle values in multiple currencies and convert between them, and figured out a few things the hard way.

我们最近实施了一个系统，需要处理多种货币的价值并在它们之间进行转换，并以艰难的方式解决了一些问题。

NEVER USE FLOATING POINT NUMBERS FOR MONEY

永远不要使用浮点数来赚钱

Floating point arithmetic introduces inaccuracies that may not be noticed until they've screwed something up. All values should be stored as either integers or fixed-decimal types, and if you choose to use a fixed-decimal type then make sure you understand exactly what that type does under the hood (ie, does it internally use an integer or floating point type).

浮点算术引入了一些不准确的地方，直到他们搞砸了才可能注意到。所有值都应该存储为整数或固定十进制类型，如果您选择使用固定十进制类型，那么请确保您确切了解该类型在幕后的作用（即，它内部是否使用整数或浮点数类型）。

When you do need to do calculations or conversions:

当您确实需要进行计算或转换时：

1. Convert values to floating point
2. Calculate new value
3. Round the number and convert it back to an integer

1. 将值转换为浮点数
2. 计算新值
3. 将数字四舍五入并将其转换回整数

When converting a floating point number back to an integer in step 3, don't just cast it - use a math function to round it first. This will usually be round, though in special cases it could be flooror ceil. Know the difference and choose carefully.

在步骤 3 中将浮点数转换回整数时，不要只是强制转换 - 首先使用数学函数对其进行四舍五入。这通常是round，但在特殊情况下可能是floor或ceil。了解差异并谨慎选择。

Store the type of a number alongside the value

在值旁边存储数字的类型

This may not be as important for you if you're only handling one currency, but it was important for us in handling multiple currencies. We used the 3-character code for a currency, such as USD, GBP, JPY, EUR, etc.

如果您只处理一种货币，这对您来说可能不那么重要，但在处理多种货币时对我们来说很重要。我们使用 3 个字符的代码表示货币，例如美元、英镑、日元、欧元等。

Depending on the situation, it may also be helpful to store:

根据情况，存储以下内容也可能有帮助：

• Whether the number is before or after tax (and what the tax rate was)
• Whether the number is the result of a conversion (and what it was converted from)

• 数字是税前还是税后（以及税率是多少）
• 数字是否是转换的结果（以及它是从什么转换而来的）

Know the accuracy bounds of the numbers you're dealing with

了解您正在处理的数字的准确度界限

For real values, you want to be as precise as the smallest unit of the currency. This means you have no values smaller than a cent, a penny, a yen, a fen, etc. Don't store values with higher accuracy than that for no reason.

对于实际值，您希望精确到货币的最小单位。这意味着您没有小于一分、一分、一日元、一分等的值。不要无缘无故地存储比该精度更高的值。

Internally, you may choose to deal with smaller values, in which case that's a different type of currency value. Make sure your code knows which is which and doesn't get them mixed up. Avoid using floating point values even here.

在内部，您可以选择处理较小的值，在这种情况下，这是不同类型的货币值。确保您的代码知道哪个是哪个并且不会混淆它们。即使在这里也避免使用浮点值。

---

---

Adding all those rules together, we decided on the following rules. In running code, currencies are stored using an integer for the smallest unit.

将所有这些规则加在一起，我们决定了以下规则。在运行代码中，货币使用整数作为最小单位进行存储。

class Currency {
   String code;       //  eg "USD"
   int value;         //  eg 2500
   boolean converted;
}

class Price {
   Currency grossValue;
   Currency netValue;
   Tax taxRate;
}

In the database, the values are stored as a string in the following format:

在数据库中，值以以下格式存储为字符串：

USD:2500

That stores the value of $25.00. We were able to do that only because the code that deals with currencies doesn't need to be within the database layer itself, so all values can be converted into memory first. Other situations will no doubt lend themselves to other solutions.

这存储了 25.00 美元的价值。我们之所以能够这样做，只是因为处理货币的代码不需要位于数据库层本身内，因此可以先将所有值转换到内存中。其他情况无疑将有助于其他解决方案。

---

---

And in case I didn't make it clear earlier, don't use float!

如果我之前没有说清楚，请不要使用浮动！

回答by pollux1er

When handling money in MySQL, use DECIMAL(13,2) if you know the precision of your money values or use DOUBLE if you just want a quick good-enough approximate value. So if your application needs to handle money values up to a trillion dollars (or euros or pounds), then this should work:

在 MySQL 中处理货币时，如果您知道货币值的精度，请使用 DECIMAL(13,2)；如果您只想快速获得足够好的近似值，请使用 DOUBLE。因此，如果您的应用程序需要处理高达一万亿美元（或欧元或英镑）的货币价值，那么这应该可行：

DECIMAL(13, 2)

Or, if you need to comply with GAAPthen use:

或者，如果您需要遵守GAAP，请使用：

DECIMAL(13, 4)

回答by bobince

4 decimal places would give you the accuracy to store the world's smallest currency sub-units. You can take it down further if you need micropayment (nanopayment?!) accuracy.

4 位小数可以让您准确存储世界上最小的货币子单位。如果您需要小额支付（纳米支付？！）的准确性，您可以进一步取消它。

I too prefer DECIMALto DBMS-specific money types, you're safer keeping that kind of logic in the application IMO. Another approach along the same lines is simply to use a [long] integer, with formatting into ¤unit.subunit for human readability (¤ = currency symbol) done at the application level.

我也更喜欢DECIMALDBMS 特定的货币类型，在应用程序 IMO 中保留这种逻辑会更安全。与此类似的另一种方法是简单地使用 [long] 整数，并在应用程序级别将格式设置为 ¤unit.subunit 以提高可读性（¤ = 货币符号）。

回答by Hank Gay

If you're going to be doing any sort of arithmetic operations in the DB (multiplying out billing rates and so on), you'll probably want a lot more precision than people here are suggesting, for the same reasons that you'd never want to use anything less than a double-precision floating point value in application code.

如果您要在数据库中进行任何类型的算术运算（乘以计费率等），您可能需要比这里的人建议的精度高得多的精度，原因与您永远不会想要在应用程序代码中使用小于双精度浮点值的任何值。

回答by Austin Salonen

The money datatype on SQL Server has four digits after the decimal.

SQL Server 上的货币数据类型在小数点后有四位数字。

From SQL Server 2000 Books Online:

来自 SQL Server 2000 联机丛书：

Monetary data represents positive or negative amounts of money. In Microsoft? SQL Server? 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.

货币数据代表货币的正数或负数。在微软？SQL服务器？2000 年，货币数据使用 money 和 smallmoney 数据类型存储。货币数据可以精确到小数点后四位。使用 money 数据类型存储从 -922,337,203,685,477.5808 到 +922,337,203,685,477.5807 范围内的值（需要 8 个字节来存储值）。使用 smallmoney 数据类型存储从 -214,748.3648 到 214,748.3647 范围内的值（需要 4 个字节来存储值）。如果需要更多的小数位，请改用小数数据类型。

回答by WW.

Sometimes you will need to go to less than a cent and there are international currencies that use very large demoniations. For example, you might charge your customers 0.088 cents per transaction. In my Oracle database the columns are defined as NUMBER(20,4)

有时你需要不到一分钱，而且有使用非常大的妖魔化的国际货币。例如，您可以向客户收取每笔交易 0.088 美分的费用。在我的 Oracle 数据库中，列被定义为 NUMBER(20,4)

回答by ayaz

I would think that for a large part your or your client's requirements should dictate what precision and scale to use. For example, for the e-commerce website I am working on that deals with money in GBP only, I have been required to keep it to Decimal( 6, 2 ).

我认为在很大程度上，您或您的客户的要求应该决定要使用的精度和规模。例如，对于我正在处理的电子商务网站，该网站仅处理英镑货币，我被要求将其保留为小数 ( 6, 2 )。

回答by Jonathan Leffler

If you were using IBM Informix Dynamic Server, you would have a MONEY type which is a minor variant on the DECIMAL or NUMERIC type. It is always a fixed-point type (whereas DECIMAL can be a floating point type). You can specify a scale from 1 to 32, and a precision from 0 to 32 (defaulting to a scale of 16 and a precision of 2). So, depending on what you need to store, you might use DECIMAL(16,2) - still big enough to hold the US Federal Deficit, to the nearest cent - or you might use a smaller range, or more decimal places.

如果您使用的是 IBM Informix Dynamic Server，您将拥有一个 MONEY 类型，它是 DECIMAL 或 NUMERIC 类型的次要变体。它始终是定点类型（而 DECIMAL 可以是浮点类型）。您可以指定 1 到 32 的小数位数和 0 到 32 的精度（默认小数位数为 16，精度为 2）。因此，根据您需要存储的内容，您可能会使用 DECIMAL(16,2) - 仍然大到足以将美国联邦赤字保持到最接近的美分 - 或者您可能使用更小的范围或更多的小数位。

回答by Mike Upjohn

A late answer here, but I've used

一个迟到的答案，但我用过

DECIMAL(13,2)

which I'm right in thinking should allow upto 99,999,999,999.99.

我认为应该允许高达 99,999,999,999.99。