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

I want to store many records in a MySQL database. All of them contains money values. But I don't know how many digits will be inserted for each one.
Which data type do I have to use for this purpose?
VARCHAR or INT (or other numeric data types)?

我想在MySQL数据库中存储很多记录。所有这些都包含货币价值。但是我不知道每一个会插入多少位数字。为此，我需要使用哪种数据类型?VARCHAR或INT(或其他数字数据类型)?

10 个解决方案

#1

Since money needs an exact representation don't use data types that are only approximate like float. You can use a fixed-point numeric data type for that like

由于货币需要精确的表示，所以不要使用仅近似于float的数据类型。您可以使用定点数字数据类型。

decimal(15,2)

• 15 is the precision (total length of value including decimal places)
• 15是精度(包括小数位数的总长度)
• 2 is the number of digits after decimal point
• 2是小数点后的位数。

See MySQL Numeric Types:

看到MySQL数值类型:

These types are used when it is important to preserve exact precision, for example with monetary data.

这些类型在重要的时候使用，以保持精确的精度，例如使用货币数据。

#2

You can use DECIMAL or NUMERIC both are same

您可以使用十进制或数字两者都是相同的。

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC. : MySQL

小数和数字类型存储精确的数字数据值。这些类型在重要的时候使用，以保持精确的精度，例如使用货币数据。在MySQL中，数值被实现为十进制，因此，以下关于十进制的注释同样适用于数值。:MySQL

i.e. DECIMAL(10,2)

例如十进制(10,2)

Good read

良好的阅读

#3

It depends on your need.

这取决于你的需要。

Using DECIMAL(10,2) usually is enough but if you need a little bit more precise values you can set DECIMAL(10,4).

使用十进制(10,2)通常是足够的，但是如果您需要更精确的值，您可以设置十进制(10,4)。

If you work with big values replace 10 with 19.

如果你使用大值，用19代替10。

#4

I prefer to use BIGINT, and store the values in by multiply with 100, so that it will become integer.

我更喜欢使用BIGINT，并将值与100相乘，这样它就会变成整数。

For e.g., to represent a currency value of 93.49, the value shall be stored as 9349, while displaying the value we can divide by 100 and display. This will occupy less storage space.

例如，要表示货币价值为93.49，该值应存储为9349，同时显示我们可以除以100并显示的值。这将占用更少的存储空间。

Caution:
Mostly we don't perform currency * currency multiplication, in case if we are doing it then divide the result with 100 and store, so that it returns to proper precision.

警告:大多数情况下，我们不执行货币*货币的乘法运算，如果我们这样做，然后将结果与100和存储区分开，这样它就会返回到适当的精度。

#5

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。

#6

Indeed this relies on the programmer's preferences. I personally use: numeric(15,4) to conform to the Generally Accepted Accounting Principles (GAAP).

实际上，这依赖于程序员的偏好。我个人使用:数字(15,4)符合公认会计原则(GAAP)。

#7

Try using

试着用

Decimal(19,4)

this usually works with every other DB as well

这通常也适用于其他的DB。

#8

At the time this question was asked nobody thought about Bitcoin price. In the case of BTC, it is probably insufficient to use DECIMAL(15,2). If the Bitcoin will rise to $100,000 or more, we will need at least DECIMAL(18,9) to support cryptocurrencies in our apps.

当时有人问这个问题，没人想到比特币的价格。在BTC的情况下，使用十进制(15,2)可能是不够的。如果比特币的价格升至10万美元或以上，我们将至少需要十进制(18,9)来支持我们应用中的加密货币。

DECIMAL(18,9) takes 12 bytes of space in MySQL (4 bytes per 9 digits).

DECIMAL(18,9)在MySQL中占用12字节的空间(4字节/ 9位)。

#9

Multiplies 10000 and stores as BIGINT, like "Currency" in Visual Basic and Office. See https://msdn.microsoft.com/en-us/library/office/gg264338.aspx

在Visual Basic和Office中，将10000和存储为BIGINT，比如“Currency”。参见https://msdn.microsoft.com/en-us/library/office/gg264338.aspx

#10

We use double.

*gasp*

*喘息*

Why?

Because it can represent any 15 digit number with no constraints on where the decimal point is. All for a measly 8 bytes!

因为它可以表示任意的15位数字没有限制小数点在哪里。总共只有8个字节!

So it can represent:

所以它可以代表:

• 0.123456789012345
• 0.123456789012345
• 123456789012345.0
• 123456789012345.0

...and anything in between.

…和介于两者之间的任何东西。

This is useful because we're dealing with global currencies, and double can store the various numbers of decimal places we'll likely encounter.

这很有用，因为我们处理的是全球货币，而double可以存储我们可能遇到的各种小数位数。

A single double field can represent 999,999,999,999,999s in Japanese yens, 9,999,999,999,999.99s in US dollars and even 9,999,999.99999999s in bitcoins

在日本，一个单一的双领域可以代表999,999,999,999,999,999,999.99美元，甚至999999999999美元。

If you try doing the same with decimal, you need decimal(30, 15) which costs 14 bytes.

如果你试着用十进制来做同样的事情，你需要十进制(30,15)，它花费14字节。

Caveats

Of course, using double isn't without caveats.

当然，使用double并非没有限制。

However, it's not loss of accuracy as some tend to point out. Even though double itself may not be internally exact to the base 10 system, we can make it exact by rounding the value we pull from the database to its significant decimal places. If needed that is. (e.g. If it's going to be outputted, and base 10 representation is required.)

然而，它并没有像一些人指出的那样失去准确性。即使double本身可能不符合基本的10系统，我们也可以通过将从数据库中提取的值舍入其重要的小数位数来精确计算。如果需要的话。(例如，如果要输出，则需要以10为基数。)

The caveats are, any time we perform arithmetic with it, we need to normalize the result (by rounding it to its significant decimal places) before:

注意事项是，任何时候我们用它来执行算术运算，我们都需要将结果标准化(通过将其四舍五入到小数点后的小数点):

1. Performing comparisons on it.
2. 执行比较。
3. Writing it back to the database.
4. 把它写回数据库。

Another kind of caveat is, unlike decimal(m, d) where the database will prevent programs from inserting a number with more than m digits, no such validations exists with double. A program could insert a user inputted value of 20 digits and it'll end up being silently recorded as an inaccurate amount.

另一种警告是，不像十进制(m, d)，数据库将阻止程序插入超过m位数字的数字，没有这样的验证存在双精度。一个程序可以插入一个用户输入值为20位的数字，结果会被无声地记录为一个不准确的数字。