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?
因为假设一个固定宽度的char列会更有效，所以我认为对于十进制列也是一样的。是吗?
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
- 将货币存储在account表中
- The transaction amount will be a
DECIMAL(19,4) - 交易金额为小数(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.
谢谢大家的宝贵意见。
10 个解决方案
#1
138
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.
如果你正在寻找一个适合所有人的，我建议十进制(19,4)是一个流行的选择(一个快速谷歌证实了这一点)。我认为这源于旧的VBA/Access/Jet货币数据类型，是语言中第一个定点十进制类型;Decimal在VB6/VBA6/Jet 4.0中采用了“1.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.
用于存储固定小数点值的经验法则是存储至少一个小数位数，而不需要考虑舍入。将前端的旧货币类型映射为后端的十进制(19,4)类型的原因之一是，货币本质上显示了银行家的四舍五入，而十进制(p, s)则通过截断来四舍五入。
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).
在十进制存储中增加一个额外的小数点位置，可以实现自定义的舍入算法，而不是采用供应商的默认值(而且银行家的舍入对于期望所有值以.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) 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 :)
是的，十进制(24,8)听起来有点过分了。大多数货币被引用到四位或五位小数。我知道有一些情况需要一个8(或更多)的小数，但这是“正常”货币数量(比如4个小数点)的按比例进行的，这意味着十进制精度应该相应地降低(在这种情况下还考虑浮点类型)。现在没有人有那么多钱去要求精确到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 MONEY data 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的货币数据类型，因为它在舍入的准确性上有严重的问题，在其他的考虑比如可移植性等方面。
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年的《概率与错误理论》(The Probability and Theory of Errors)将其称为“计算机规则”(“计算机”指执行计算的人)。
#2
81
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:
当你确实需要进行计算或转换时:
- Convert values to floating point
- 将值转换为浮点数
- Calculate new value
- 计算新值
- Round the number and convert it back to an integer
- 将数字四舍五入，并将其转换为整数
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 floor or ceil. Know the difference and choose carefully.
当将浮点数转换回第3步中的整数时，不要只是强制转换它——首先使用一个数学函数来将它四舍五入。这通常是圆的，虽然在特殊情况下可以是地板或锡箔。了解其中的差异并仔细选择。
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个字符的代码，如USD、GBP、JPY、EUR等。
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美元。我们之所以能够做到这一点，仅仅是因为处理货币的代码不需要在数据库层内部，所以所有的值都可以首先转换为内存。其他情况无疑将有助于其他解决办法。
And in case I didn't make it clear earlier, don't use float!
如果我之前没说清楚，不要用float!
#3
3
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 GAAP then use:
或者，如果您需要遵守GAAP，则使用:
DECIMAL(13, 4)
#4
2
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 DECIMAL to 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.
相对于特定于dbms的货币类型，我也更喜欢十进制，在应用程序IMO中保持这种逻辑更安全。沿着相同的路线的另一种方法就是使用一个长整数,格式化成¤单位。亚基对人类可读性(¤=货币符号)在应用程序级别完成的。
#5
1
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.
货币数据表示正或负的货币数量。在Microsoft®SQL Server™2000,货币使用金钱和smallmoney数据存储的数据类型。货币数据可以精确地存储到小数点后四位。使用货币数据类型来存储从- 922337,203,685,477.5808到+922,337,203,685,477.5807之间的值(需要8字节来存储值)。使用smallmoney数据类型将值存储在- 214748.3648到214748.3647之间(需要4字节才能存储值)。如果需要更多的小数位数，可以使用decimal数据类型。
#6
1
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)
#7
1
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.
如果你要做任何形式的算术运算的DB(乘以计费率等等),你可能会想要更多的精度比这里的人们说,出于同样的原因,你从未想使用任何少于一个双精度浮点值在应用程序代码。
#8
0
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，那么您将拥有一种货币类型，它是小数或数字类型的一个小变体。它始终是定点类型(而DECIMAL可以是浮点类型)。您可以指定一个范围从1到32岁,和一个精度从0到32(违约规模16 - 2)的精度。所以,这取决于你需要存储,您可以使用十进制(16日2),还是大得足以容纳美国联邦赤字,到最近的分,或者您可以使用一个较小的范围内,或更多的小数位。
#9
0
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.
我认为应该是99999999999999999.99。99。99。
#10
-1
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 ).
我认为，在很大程度上，您或您的客户的需求应该决定使用什么精度和规模。例如，对于我正在开发的电子商务网站，我只使用GBP进行交易，我被要求将其保持为十进制(6,2)。
#1
138
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.
如果你正在寻找一个适合所有人的，我建议十进制(19,4)是一个流行的选择(一个快速谷歌证实了这一点)。我认为这源于旧的VBA/Access/Jet货币数据类型，是语言中第一个定点十进制类型;Decimal在VB6/VBA6/Jet 4.0中采用了“1.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.
用于存储固定小数点值的经验法则是存储至少一个小数位数，而不需要考虑舍入。将前端的旧货币类型映射为后端的十进制(19,4)类型的原因之一是，货币本质上显示了银行家的四舍五入，而十进制(p, s)则通过截断来四舍五入。
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).
在十进制存储中增加一个额外的小数点位置，可以实现自定义的舍入算法，而不是采用供应商的默认值(而且银行家的舍入对于期望所有值以.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) 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 :)
是的，十进制(24,8)听起来有点过分了。大多数货币被引用到四位或五位小数。我知道有一些情况需要一个8(或更多)的小数，但这是“正常”货币数量(比如4个小数点)的按比例进行的，这意味着十进制精度应该相应地降低(在这种情况下还考虑浮点类型)。现在没有人有那么多钱去要求精确到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 MONEY data 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的货币数据类型，因为它在舍入的准确性上有严重的问题，在其他的考虑比如可移植性等方面。
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年的《概率与错误理论》(The Probability and Theory of Errors)将其称为“计算机规则”(“计算机”指执行计算的人)。
#2
81
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:
当你确实需要进行计算或转换时:
- Convert values to floating point
- 将值转换为浮点数
- Calculate new value
- 计算新值
- Round the number and convert it back to an integer
- 将数字四舍五入，并将其转换为整数
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 floor or ceil. Know the difference and choose carefully.
当将浮点数转换回第3步中的整数时，不要只是强制转换它——首先使用一个数学函数来将它四舍五入。这通常是圆的，虽然在特殊情况下可以是地板或锡箔。了解其中的差异并仔细选择。
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个字符的代码，如USD、GBP、JPY、EUR等。
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美元。我们之所以能够做到这一点，仅仅是因为处理货币的代码不需要在数据库层内部，所以所有的值都可以首先转换为内存。其他情况无疑将有助于其他解决办法。
And in case I didn't make it clear earlier, don't use float!
如果我之前没说清楚，不要用float!
#3
3
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 GAAP then use:
或者，如果您需要遵守GAAP，则使用:
DECIMAL(13, 4)
#4
2
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 DECIMAL to 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.
相对于特定于dbms的货币类型，我也更喜欢十进制，在应用程序IMO中保持这种逻辑更安全。沿着相同的路线的另一种方法就是使用一个长整数,格式化成¤单位。亚基对人类可读性(¤=货币符号)在应用程序级别完成的。
#5
1
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.
货币数据表示正或负的货币数量。在Microsoft®SQL Server™2000,货币使用金钱和smallmoney数据存储的数据类型。货币数据可以精确地存储到小数点后四位。使用货币数据类型来存储从- 922337,203,685,477.5808到+922,337,203,685,477.5807之间的值(需要8字节来存储值)。使用smallmoney数据类型将值存储在- 214748.3648到214748.3647之间(需要4字节才能存储值)。如果需要更多的小数位数，可以使用decimal数据类型。
#6
1
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)
#7
1
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.
如果你要做任何形式的算术运算的DB(乘以计费率等等),你可能会想要更多的精度比这里的人们说,出于同样的原因,你从未想使用任何少于一个双精度浮点值在应用程序代码。
#8
0
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，那么您将拥有一种货币类型，它是小数或数字类型的一个小变体。它始终是定点类型(而DECIMAL可以是浮点类型)。您可以指定一个范围从1到32岁,和一个精度从0到32(违约规模16 - 2)的精度。所以,这取决于你需要存储,您可以使用十进制(16日2),还是大得足以容纳美国联邦赤字,到最近的分,或者您可以使用一个较小的范围内,或更多的小数位。
#9
0
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.
我认为应该是99999999999999999.99。99。99。
#10
-1
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 ).
我认为，在很大程度上，您或您的客户的需求应该决定使用什么精度和规模。例如，对于我正在开发的电子商务网站，我只使用GBP进行交易，我被要求将其保持为十进制(6,2)。