在MySQL数据库中存储货币值的最佳数据类型。

What is the best SQL data type for currency values? I'm using MySQL but would prefer a database independent type.

对于货币值，最好的SQL数据类型是什么?我使用的是MySQL，但更喜欢数据库独立的类型。

9 个解决方案

#1

Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use "money" as it's non-standard.

像十进制(19,4)这样的东西在大多数情况下通常都很好用。您可以调整大小和精度来满足您需要存储的数字的需求。即使在SQL Server中，我也不会使用“金钱”作为它的非标准。

#2

The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things

唯一需要注意的是，如果从一个数据库迁移到另一个数据库，您可能会发现小数(19,4)和小数(19,4)意味着不同的东西。

( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html )

(http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html)

    DBASE: 10,5 (10 integer, 5 decimal)
    MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal)

#3

It is also important to work out how many decimal places maybe required for your calculations.

计算出计算所需的小数位数也很重要。

I worked on a share price application that required the calculation of the price of one million shares. The quoted share price had to be stored to 7 digits of accuracy.

我做了一个股票价格的申请，需要计算100万股的价格。所报的股价必须被储存到7位数的精确度。

#4

Assaf's response of

艾瑟夫巴德的响应

Depends on how much money you got...

这要看你有多少钱……

sounds flippant, but actually it's pertinant.

听起来很轻率，但实际上是有针对性的。

Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacific Francs per night... something that was never anticpated when the database schema was designed 10 years ago.

只有今天,我们有一个问题,未能记录被插入到我们的费率表,因为其中一个列(GrossRate)设置为十进制(11日,4),和我们的产品部门刚刚合同房间在博拉博拉岛的一些惊人的度假胜地,每晚太平洋卖几百万法郎…当数据库模式在10年前设计时，从来没有出现过反效果。

#5

For accounting applications it's very common to store the values as integers (some even go so far as to say it's the only way). To get an idea, take the amount of the transactions (let's suppose $100.23) and multiple by 100, 1000, 10000, etc. to get the accuracy you need. So if you only need to store cents and can safely round up or down, just multiply by 100. In my example, that would make 10023 as the integer to store. You'll save space in the database and comparing two integers is much easier than comparing two floats. My $0.02.

对于会计应用程序来说，将值存储为整数是非常常见的(有些甚至会说这是惟一的方法)。为了得到一个想法，取交易的金额(假设是$100.23)和倍数乘以100,1000,10000，等等，以得到你需要的精度。因此，如果你只需要储存美分，并且可以安全地上下移动，只需乘以100。在我的示例中，这将使10023作为整数存储。在数据库中保存空间并比较两个整数比比较两个浮点数要容易得多。我的0.02美元。

#6

super late entry but GAAP is a good rule of thumb..

超级晚进入，但公认会计准则是一个很好的经验法则。

If your application needs to handle money values up to a trillion then this should work: 13,2 If you need to comply with GAAP (Generally Accepted Accounting Principles) then use: 13,4

如果您的应用程序需要处理高达1万亿美元的货币价值，那么这应该起作用:13、2如果您需要遵守公认会计准则(公认会计原则)，那么使用:13,4。

Usually you should sum your money values at 13,4 before rounding of the output to 13,2.

通常情况下，你应该把你的钱的值在13,4，然后把结果四舍五入到13,2。

Source: Best datatype to store monetary value in MySQL

源:最好的数据类型存储MySQL中的货币值。

#7

You could use something like DECIMAL(19,2) by default for all of your monetary values, but if you'll only ever store values lower than $1,000, that's just going to be a waste of valuable database space.

您可以使用十进制(19,2)这样的东西来默认所有的货币值，但是如果您只存储低于$1,000的值，那将会浪费宝贵的数据库空间。

For most implementations, DECIMAL(N,2) would be sufficient, where the value of N is at least the number of digits before the . of the greatest sum you ever expect to be stored in that field + 5. So if you don't ever expect to store any values greater than 999999.99, DECIMAL(11,2) should be more than sufficient (until expectations change).

对于大多数实现，DECIMAL(N,2)是足够的，其中N的值至少是前面的数字的位数。在你期望的最大的和中，你期望被存储在这个领域+ 5。因此，如果您不希望存储任何大于999999.99的值，那么DECIMAL(11,2)应该是足够的(直到期望发生变化)。

If you want to be GAAP compliant, you could go with DECIMAL(N,4), where the value of N is at least the number of digits before the . of the greatest sum you ever expect to be stored in that field + 7.

如果你想要符合GAAP的要求，你可以使用十进制(N,4)，其中N的值至少是前面数字的位数。这是你期望的最大的一笔钱。

#8

It depends on the nature of data. You need to contemplate it beforehand.

这取决于数据的性质。你需要事先考虑一下。

My case

• decimal(13,4) unsigned for recording money transactions
  • storage efficient (4 bytes for each side of decimal point anyway) 1
  • 存储效率(无论如何，每边4字节)1。
  • GAAP compliant
  • 公认会计准则的
• 十进制(13,4)未签名，用于记录货币事务存储效率(无论如何，每边4字节)1 GAAP兼容。
• decimal(19,4) unsigned for aggregates
  • we need more space for totals of multiple multi-billion transactions
  • 我们需要更多的空间来完成数十亿次的交易。
  • semi-compliance with MS Currency data type won't hurt 2
  • 半符合MS货币数据类型不会造成伤害。
  • it will take more space per record (11 bytes - 7 left & 4 right), but this is fine as there are fewer records for aggregates 1
  • 每个记录需要更多的空间(11个字节- 7个左和4个右)，但是这很好，因为聚集1的记录较少。
• 十进制(19,4)未签名的集合，我们需要更多的空间，以满足MS货币数据类型的多亿事务的半符合，这不会伤害2，它将占用更多的空间记录(11字节- 7左和4右)，但是这很好，因为聚集1的记录较少。
• decimal(10,5) for exchange rates
  • they are normally quoted with 5 digits altogether so you could find values like 1.2345 & 12.345 but not 12345.67890
  • 它们通常都是5位数字，所以你可以找到像1.2345和12.345这样的值，而不是12345.67890。
  • it is widespread convention, but not a codified standard (at least to my quick search knowledge)
  • 这是一种普遍的惯例，但不是一种成文的标准(至少对我快速的搜索知识来说)
  • you could make it decimal (18,9) with the same storage, but the datatype restrictions are valuable built-in validation mechanism
  • 您可以使用相同的存储方式将其设置为decimal(18,9)，但datatype限制是有价值的内置验证机制。
• 小数(10、5)汇率通常引用5位数完全可以找到值1.2345 & 12.345而不是12345.67890是普遍的惯例,但不是一个法典标准(至少我的快速搜索知识)可以使其小数(18日9)相同的存储,但是是有价值的内置数据类型限制验证机制

Why (M,4)?

• there are currencies that split into a thousand pennies
• 有一种货币可以分成一千便士。
• there are money equivalents like "Unidad de Fermento", "CLF" expressed with 4 significant decimal places 3,4
• 有“Unidad de Fermento”、“CLF”和4个重要的十进制位(3,4)。
• it is GAAP compliant
• 这是符合公认会计准则

Tradeoff

• lower precision:
  • less storage cost
  • 更少的存储成本
  • quicker calculations
  • 更快的计算
  • lower calculation error risk
  • 降低计算误差的风险
  • quicker backup & restore
  • 更快的备份和恢复
• 更低的精度:更少的存储成本更快的计算更低的计算错误风险更快的备份和恢复。
• higher precision:
  • future compatibility (numbers tend to grow)
  • 未来的兼容性(数字趋向于增长)
  • development time savings (you won't have to rebuild half a system when the limits are met)
  • 开发时间节省(当遇到限制时，您不必重建半个系统)
  • lower risk of production failure due to insufficient storage precision
  • 由于存储精度不足，生产失败的风险降低。
• 更高的精度:未来的兼容性(数字倾向于增长)开发时间的节省(当遇到限制时，您不需要重建半个系统)由于存储精度不足而降低生产失败的风险。

Compatible Extreme

Although MySQL lets you use decimal(65,30), 31 for scale and 30 for precision seem to be our limits if we want to leave transfer option open.

虽然MySQL允许您使用十进制(65,30)，但是如果我们想要打开传输选项，那么，如果我们想要将这个选项打开的话，那么它的大小是31，而精度是30。

Maximum scale and precision in most common RDBMS:

最常见RDBMS的最大规模和精度:

            Precision   Scale
Oracle      31          31
T-SQL       38          38
MySQL       65          30
PostgreSQL  131072      16383

6, 7, 8, 9

6、7、8、9

Reasonable Extreme

2. Why (27,4)?
   • you never know when the system needs to store Zimbabwean dollars
   • 你永远不知道这个系统何时需要储存津巴布韦元。
3. 为什么(27岁,4)?你永远不知道这个系统何时需要储存津巴布韦元。

September 2015 Zimbabwean government stated it would exchange Zimbabwean dollars for US dollars at a rate of 1 USD to 35 quadrillion Zimbabwean dollars 5

2015年9月，津巴布韦*宣布将以1美元至35千万亿津巴布韦元的汇率兑换津巴布韦的美元。

We tend to say "yeah, sure... I won't need that crazy figures". Well, Zimbabweans used to say that too. Not to long ago.

我们倾向于说:“是的，当然……”我不需要那些疯狂的人物。津巴布韦人也曾经这么说过。不很久以前。

Let's imagine you need to record a transaction of 1 mln USD in Zimbabwean dollars (maybe unlikely today, but who knows how this will look like in 10 years from now?).

让我们想象一下，你需要在津巴布韦的美元上记录一个mln美元的交易(也许今天不太可能，但谁知道10年后会是什么样子呢?)

1. (1 mln USD) * (35 Quadrylion ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
2. (1 mln USD) * (35 Quadrylion ZWL) =(10 6) *(35 * 10 15) = 35 * 10 21。
3. we need:
   • 2 digits to store "35"
   • 2位数字存储“35”
   • 21 digits to store the zeros
   • 21位数字存储0。
   • 4 digits to the right of decimal point
   • 小数点右侧4位。
4. 我们需要:2位数字来存储“35”21位数字，以存储小数点右边的零4位数字。
5. this makes decimal(27,4) which costs us 15 bytes for each entry
6. 这使得小数(27,4)每个条目花费15个字节。
7. we may add one more digit on the left at no expense - we have decimal(28,4) for 15 bytes
8. 我们可以在左边再加一个数字——我们有十进制(28,4)的15个字节。
9. Now we can store 10 mln USD transaction expressed in Zimbabwean dollars, or secure from another strike of hiperinflation, which hopefully won't happen
10. 现在，我们可以在津巴布韦的美元上储存10个mln美元的交易，或者从另一个hiperinflation的打击中获得安全，希望不会发生。

#9

Though this may be late, but it will be helpful to someone else.From my experience and research I have come to know and accept decimal(19, 6).That is when working with php and mysql. when working with large amount of money and exchange rate

虽然这可能会很晚，但对别人会有帮助。从我的经验和研究来看，我已经认识并接受了十进制(19,6)，这是在使用php和mysql时。在处理大量的货币和汇率时。