logo标签列表

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

mysqlsqlsqldatatypes
226

什么是最适合货币值的SQL数据类型？我正在使用MySQL，但更喜欢无数据库依赖性的类型。

2
可能是在MySQL中存储货币值的最佳数据类型的重复问题。 - Marcus Müller
10个回答
244

类似于Decimal(19,4)这样的内容通常在大多数情况下都能很好地工作。您可以根据需要调整比例和精度以适应需要存储的数字。即使在SQL Server中，我也不倾向于使用"money"，因为它是非标准的。

56
关于尺寸的一点说明：根据MSDN（http://msdn.microsoft.com/en-us/library/ms187746.aspx），Decimal（10,4）和Decimal（19,4）都使用9个字节的存储空间，因此可能会选择多使用9个数字的比例尺。 - Adam Nofsinger
1
这篇MSDN文章是关于SQL Server的，但问题是关于MySQL的。（我遇到过一些开发人员认为两者是相同的，所以最好明确一下。） - cja
6
使用(19,4)相比于(19,2)有什么好处？ - ryvantage
4
我的好处是这样的...我需要所有的表格行的金额加起来等于特定的金额。假设这个金额是10美元。随着每个新的行被添加，每行的金额都会改变。如果表格中有3行，10/3=3.3333333333... 但是只存储两位小数，它们被存储为3.33。因此，当你把它们加起来时，3.33+3.33+3.33=9.99。我们少了一分钱！在较大的数据集上情况会更糟。将它们存储在19,4并求和，然后将输出舍入到19,2。 - Rick
我来这里考虑国际价值，特别是带逗号的欧元。您仍应使用十进制/数字：https://stackoverflow.com/questions/54644222/how-to-store-integer-data-with-comma-in-it - Jono
50
链接现在已经失效了，很遗憾。 - Marco Aurélio Deleu
1
@MarcoAurélioDeleu - 我已经更改了链接，指向Wayback Machine上的页面，这样你就可以看到它在2009年的样子。 - Tony
20

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

我曾经参与开发一个股票价格应用程序，需要计算一百万股的价格。报价股价必须存储到7位有效数字。

8
好的，尤其是在金融应用程序中，“价格”并不一定意味着“金钱”，这是一个很好的观点。 - Mike Woodhouse
17

阿萨夫的回答是：

取决于你有多少钱...

听起来有点轻浮，但实际上很恰当。

就在今天，我们遇到了一个问题，由于我们的“利率”表中的一列（GrossRate）被设置为十进制（11,4），而我们的产品部门刚刚签署了一份合同，购买了波拉波拉岛上某个惊人度假村每晚几百万太平洋法郎的房间......这在10年前设计数据库架构时从未被预料到。

2
这就是为什么我建议使用 decimal (19,4)。听起来可能有些过度，但你永远不知道何时需要在该字段中存储非常大的金额。 - Kibbee
2
@Kibbee：直到今天我才同意你的观点，对于我们的需求我之前并不认同。（6年来（11,4）一直很好用……） - Scott Ferguson
1
如果您想处理像津巴布韦元这样的高通胀货币，2009年的汇率可能达到了10³⁰比1美元。 - user3064538
14

在会计应用中，将数值存储为整数非常常见（甚至有些人认为这是唯一的方法）。为了了解这个概念，假设交易金额为$100.23，然后乘以100、1000、10000等来获得所需的精度。因此，如果您只需要存储美分并且可以安全地四舍五入，只需乘以100即可。在我的示例中，这将使10023成为要存储的整数。您将节省数据库中的空间，并且比较两个整数比比较两个浮点数更容易。以上仅为个人意见。

如何存储 6.125（6 1/8）？ - PeterToTheThird
我猜他会将它存储为整数6125。 - Jimmy Knoot
4
这个方案相较于“DECIMAL”会更好吗？你需要非常小心地在适当的时候将分、厘或毫转化为美元。 - user565869
我已经阅读过，即使在最新版本的MySQL中，使用INT比DECIMAL通常更快。当您需要将这些值带入PHP或其他语言进行比较时，使用INT也更加容易。 - daneb
这个想法是可行的，但它是一个非常过时的设计。你只会想要在那些不支持精确数字格式的系统中使用整数。25年前，这是相当普遍的。今天，几乎任何适用于商业应用程序的业务应用程序或RDBMS都将支持精确的数字格式，例如SQL的十进制或C#的十进制。我可以想象一个有如此大量的财务数据流入的系统，今天可能仍需要仅限整数的数学计算，但这样的系统不会由在SO上寻找答案的人设计。 - Bacon Bits
10

这取决于数据的性质。您需要事先考虑清楚。

我的情况

  • 使用decimal(13,4) unsigned记录货币交易
    • 存储效率高（小数点两边各4个字节）1
    • 符合GAAP标准
  • 使用decimal(19,4) unsigned进行聚合
    • 我们需要更多的空间来记录多个数十亿交易的总计
    • 与MS货币数据类型半兼容不会有影响2
    • 每条记录将占用更多的空间（11个字节-左7位和右4位），但由于聚合记录较少，因此可以接受1
  • 使用decimal(10,5)记录汇率
    • 它们通常以5个数字的方式报价，因此您可能会找到像1.2345和12.345这样的值，但不是12345.67890
    • 这是一种广泛的惯例，但不是规范标准（至少在我快速搜索的知识范围内）
    • 您可以使用相同的存储将其变为decimal(18,9)，但数据类型限制是有价值的内置验证机制

为什么使用(M,4)？

  • 有些货币分成一千个便士
  • 有一些货币等价物，如“发酵单位”、“CLF”，用4个有效小数表示 3,4
  • 它符合GAAP标准

    • 权衡

    • 低精度:
      • 更少的存储成本
      • 更快的计算速度
      • 更低的计算误差风险
      • 更快的备份与恢复
    • 高精度:
      • 未来兼容性（数字倾向于增长）
      • 开发时间节省（当达到限制时不必重建一半系统）
      • 由于不足的存储精度而导致生产故障的风险降低

    兼容极值

    虽然MySQL允许使用decimal(65,30)，但如果我们想留下传输选项，31为刻度和30为精度似乎是我们的限制。

    大多数常见RDBMS中的最大刻度和精度：

                精度     刻度
Oracle      31          31
T-SQL       38          38
MySQL       65          30
PostgreSQL  131072      16383

    6, 7, 8, 9

    合理的极端

    1. 为什么是(27,4)?
      • 你永远不知道系统何时需要存储津巴布韦元

    2015年9月，津巴布韦政府宣布以1美元兑换35万亿津巴布韦元的比率进行交换。5

    我们常说：“是啊，当然……我不需要那种疯狂的数字。” 嗯，津巴布韦人也曾这么说。 不久之前。

    让我们想象您需要用津巴布韦元记录100万美元的交易（也许今天不太可能，但谁知道10年后会变成什么样子？）。

    1. (1百万美元) * (35万亿ZWL) = ( 10^6 ) * (35 * 10^15) = 35 * 10^21
    2. 我们需要：
      • 2个数字来存储“35”
      • 21位数字来存储零
      • 小数点右边的4位数字
    3. 这使得小数decimal(27,4)，每个条目需要15个字节
    4. 我们可以免费在左边再加上一位数字-我们有decimal（28,4），需要15个字节
    5. 现在我们可以用津巴布韦元来表示1000万美元的交易，或者防止另一次高通胀袭击，希望这种情况不会再发生
    10

    虽然来晚了，但 GAAP 是一个好的经验法则。对货币字段应用 DECIMAL(13,4) 应该足够。

    如果您的应用程序需要处理高达一万亿的金钱价值，那么这个应该可以：13.2 如果需要遵守 GAAP（公认的会计原则），则使用：13.4

    通常，在将输出舍入为 13.2 之前，应将您的金钱价值总和设定为 13.4。

    使用 MySQL，您可以使用 DECIMAL 或 NUMERIC 数据类型，因为它们存储精确的数字数据值。

    使用 13,4 允许达到 $999,999,999.9999。根据 ISO 4217 标准，只有 2 个国家使用 4 位小数（智利和乌拉圭）。

    来源:

    6
    你可以默认使用DECIMAL(19,2)来存储货币值，但如果你只会存储低于$1,000的值，那么这只会浪费宝贵的数据库空间。
    对于大多数实现来说，DECIMAL(N,2)就足够了，其中N的值至少应该是你预期在该字段中存储的最大金额数字前面的位数+5。因此，如果你不希望存储任何大于999999.99的值，DECIMAL(11,2)就足够了（直到预期发生改变）。
    如果你想符合GAAP标准，可以使用DECIMAL(N,4)，其中N的值至少应该是你预期在该字段中存储的最大金额数字前面的位数+7。
    2

    虽然可能有些晚了，但对其他人可能会有所帮助。从我的经验和研究中，我知道并接受了decimal(19, 6)。这是在使用php和mysql时处理大量货币和汇率时的情况。

    0

    简短回答：我建议根据您的需求使用带有精度的十进制。如果您想要存储货币较小单位（例如分）的整数数字，并且在编程语言中处理小数存在问题，则可以选择精度为0的十进制。

    要确定所需的精度，您需要考虑以下因素：

    • 您支持的货币类型（它们可以具有不同数量的小数位）。加密货币最多有18个小数位（ETH）。由于通货膨胀，小数位数可能会随时间而变化。
    • 存储小单位商品价格（可能是从另一种货币转换而来）或具有累加器可能需要使用比货币定义的更多的小数位

    存储最小单位的整数数字可能导致将来需要重新调整值的精度。如果使用十进制，这将更容易。

    有关详细信息和注意事项，请参见文章。

    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接