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

Best data type for storing currency values in a MySQL database

提问by Brian Fisher

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

货币值的最佳 SQL 数据类型是什么？我正在使用 MySQL，但更喜欢与数据库无关的类型。

回答by Kibbee

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.

Decimal(19,4)在大多数情况下，类似的东西通常效果很好。您可以调整比例和精度以满足您需要存储的数字的需要。即使在 SQL Server 中，我也不倾向于使用“ money”，因为它是非标准的。

回答by SeanJA

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

您唯一需要注意的是，如果您从一个数据库迁移到另一个数据库，您可能会发现 DECIMAL(19,4) 和 DECIMAL(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)

回答by Leah

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.

我从事过一个需要计算一百万股价格的股价应用程序。报价的股票价格必须精确到 7 位数字。

回答by Scott Ferguson

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.

直到今天，我们遇到了一个问题，记录未能插入到我们的 Rate 表中，因为其中一列 (GrossRate) 设置为 Decimal (11,4)，而我们的产品部门刚刚获得了一些令人惊叹的度假村的房间合同在波拉波拉岛，每晚售价数百万太平洋法郎……这是 10 年前设计数据库模式时从未预料到的。

回答by Dane Bendixen

For accounting applications it's very common to store the values as integers (some even go so far as to say it's the onlyway). 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 mucheasier than comparing two floats. My $0.02.

对于会计应用程序，将值存储为整数是很常见的（有些甚至说这是唯一的方法）。要获得一个想法，请取交易金额（假设为 100.23 美元）并乘以 100、1000、10000 等，以获得所需的准确性。因此，如果您只需要存储美分并且可以安全地向上或向下取整，只需乘以 100。在我的示例中，这将使 10023 作为要存储的整数。您将节省数据库中的空间，并且比较两个整数比比较两个浮点数要容易得多。我的 0.02 美元。

回答by Damian

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

进入超级晚，但 GAAP 是一个很好的经验法则。

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

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

如果您的应用程序需要处理高达一万亿的货币价值，那么这应该可行：13,2 如果您需要遵守 GAAP（公认会计原则），则使用：13,4

通常，在将输出四舍五入到 13,2 之前，您应该将您的货币价值总和为 13,4。

Source: Best datatype to store monetary value in MySQL

来源：在 MySQL 中存储货币价值的最佳数据类型

回答by John Slegers

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.

DECIMAL(19,2)默认情况下，您可以为所有货币价值使用类似的东西，但如果您只存储低于 1,000 美元的价值，那只会浪费宝贵的数据库空间。

For most implementations, DECIMAL(N,2)would be sufficient, where the value of Nis 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 GAAPcompliant, you could go with DECIMAL(N,4), where the value of Nis at least the number of digits before the .of the greatest sum you ever expect to be stored in that field + 7.

如果您想符合GAAP，您可以使用DECIMAL(N,4)，其中 的值N至少是.您希望存储在该字段中的最大总和之前的位数+ 7。

回答by kshishkin

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
  • GAAP compliant
• 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
  • 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
• 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
  • 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

• 十进制（13,4）无符号记录货币交易
  • 存储效率高（无论如何小数点的每一侧都是 4 个字节）1
  • 符合公认会计准则
• 十进制（19,4）无符号聚合
  • 我们需要更多空间来处理数十亿笔交易
  • 半遵守 MS Currency 数据类型不会伤害2
  • 每条记录将占用更多空间（11 字节 - 左 7 和右 4），但这很好，因为聚合1的记录较少
• 汇率的十进制（10,5）
  • 它们通常用 5 位数字引用，因此您可以找到 1.2345 和 12.345 之类的值，但不能找到 12345.67890
  • 这是普遍的惯例，但不是编纂的标准（至少就我的快速搜索知识而言）
  • 您可以使用相同的存储将其设为十进制 (18,9)，但数据类型限制是有价值的内置验证机制

Why (M,4)?

为什么是 (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
• it is GAAP compliant

• 有些货币可以分成一千便士
• 有货币等价物，如“Unidad de Fermento”、“CLF”，用 4 个有效小数位表示3, 4
• 符合美国通用会计准则

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

合理极限

1. Why (27,4)?
   • you never know when the system needs to store Zimbabwean dollars

2. 为什么是 (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 万亿津巴布韦元的汇率将津巴布韦元兑换成美元5

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

假设您需要以津巴布韦元记录一笔 100 万美元的交易（今天可能不太可能，但谁知道 10 年后会是什么样子？）。

1. (1 mln USD) * (35 Quadrylion ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
2. we need:
   • 2 digits to store "35"
   • 21 digits to store the zeros
   • 4 digits to the right of decimal point
3. this makes decimal(27,4) which costs us 15 bytes for each entry
4. we may add one more digit on the left at no expense - we have decimal(28,4) for 15 bytes
5. Now we can store 10 mln USD transaction expressed in Zimbabwean dollars, or secure from another strike of hiperinflation, which hopefully won't happen

1. (100 万美元) * (35 Quadrylion ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
2. 我们需要：
   • 2 位数字来存储“35”
   • 21 位数字来存储零
   • 小数点右边4位
3. 这使得 decimal(27,4) 为每个条目花费我们 15 个字节
4. 我们可以免费在左边再增加一位数字 - 我们有十进制（28,4）为 15 个字节
5. 现在我们可以存储以津巴布韦元表示的 1000 万美元交易，或者避免再次发生恶性通货膨胀，这希望不会发生

回答by precious

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 时。处理大量货币和汇率时