在 MySQL 中存储货币值的最佳数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13030368/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best data type to store money values in MySQL
提问by Mohammad Saberi
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?
VARCHARor INT(or other numeric data types)?
我想在 MySQL 数据库中存储许多记录。所有这些都包含货币价值。但是我不知道每个数字会插入多少个数字。
为此，我必须使用哪种数据类型？
VARCHAR或INT（或其他数字数据类型）？
回答by juergen d
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)
15is the precision (total length of value including decimal places)2is the number of digits after decimal point
15是精度（值的总长度，包括小数位）2是小数点后的位数
See MySQL Numeric Types:
请参阅MySQL 数字类型：
These types are used when it is important to preserve exact precision, for example with monetarydata.
这些类型用于保持精确精度很重要的情况，例如货币数据。
回答by NullPoiиteя
You can use DECIMALor NUMERICboth are same
您可以使用DECIMAL或NUMERIC两者都相同
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
DECIMAL 和 NUMERIC 类型存储精确的数字数据值。这些类型用于保持精确精度很重要的情况，例如货币数据。在 MySQL 中，NUMERIC 实现为 DECIMAL，因此以下关于 DECIMAL 的说明同样适用于 NUMERIC。：MySQL
i.e.DECIMAL(10,2)
IEDECIMAL(10,2)
回答by Dinesh P.R.
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 100and display. This will occupy less storage space.
例如，要表示 的货币值93.49，该值应存储为9349，同时显示值我们可以除以 100并显示。这将占用更少的存储空间。
Caution:
Mostly we don't performcurrency * currencymultiplication, in case if we are doing it then divide the result with 100 and store, so that it returns to proper precision.
注意：
大多数情况下我们不执行currency * currency乘法，如果我们正在执行乘法，则将结果除以 100 并存储，以便它返回到正确的精度。
回答by Svetoslav
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).
DECIMAL(10,2)通常使用就足够了，但如果您需要更精确的值，您可以设置DECIMAL(10,4).
If you work with big values replace 10with 19.
如果您使用大值，请替换10为19.
回答by david.ee
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
如果您的应用程序需要处理高达一万亿的货币价值，那么这应该可行：13,2 如果您需要遵守 GAAP（公认会计原则），则使用：13,4
Usually you should sum your money values at 13,4 before rounding of the output to 13,2.
通常，在将输出四舍五入到 13,2 之前，您应该将您的货币价值总和为 13,4。
回答by Chagbert
Indeed this relies on the programmer's preferences. I personally use: numeric(15,4)to conform to the Generally Accepted Accounting Principles (GAAP).
事实上，这取决于程序员的偏好。我个人使用：numeric(15,4)符合公认会计原则（GAAP）。
回答by Deepesh
Try using
尝试使用
Decimal(19,4)
this usually works with every other DB as well
这通常也适用于所有其他数据库
回答by antak
We use double.
我们使用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.123456789012345123456789012345.0
0.123456789012345123456789012345.0
...and anything in between.
...以及介于两者之间的任何内容。
This is useful because we're dealing with global currencies, and doublecan store the various numbers of decimal places we'll likely encounter.
这很有用，因为我们正在处理全球货币，并且double可以存储我们可能会遇到的各种小数位数。
A single doublefield 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
单个double字段可以代表日元的 999,999,999,999s，美元的 9,999,999,999,999.99s 甚至比特币的 9,999,999.99999999s
If you try doing the same with decimal, you need decimal(30, 15)which costs 14 bytes.
如果您尝试对 执行相同操作decimal，则需要decimal(30, 15)花费 14 个字节。
Caveats
注意事项
Of course, using doubleisn't without caveats.
当然，使用double并非没有警告。
However, it's notloss of accuracy as some tend to point out. Even though doubleitself may not be internally exact to the base 10 system, we can make it exact by rounding the valuewe 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:
需要注意的是，任何时候我们用它执行算术时，我们都需要在之前对结果进行归一化（通过将其四舍五入到有效的小数位）：
- Performing comparisons on it.
- Writing it back to the database.
- 对其进行比较。
- 将其写回数据库。
Another kind of caveat is, unlike decimal(m, d)where the database will prevent programs from inserting a number with more than mdigits, 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.
另一种警告是，与decimal(m, d)数据库将阻止程序插入多于m数字的数字不同，不存在这样的验证double。程序可以插入用户输入的 20 位数字，最终会被记录为不准确的数字。
回答by digitalstraw
Storing money as BIGINTmultiplied by 100 or more with the reason to use less storage space makes no sense in all "normal" situations.
BIGINT在所有“正常”情况下，为了使用更少的存储空间而将资金乘以 100 或更多的方式存储是没有意义的。
- To stay aligned with GAAP it is sufficient to store currencies in
DECIMAL(13,4) - MySQL manual reads that it needs 4 bytes per 9 digits to store
DECIMAL. DECIMAL(13,4)represents 9 digits + 4 fraction digits (decimal places) => 4 + 2 bytes = 6 bytes- compare to 8 bytes required to store
BIGINT.
- 为了与 GAAP 保持一致，将货币存储在
DECIMAL(13,4) - MySQL 手册读到每 9 位需要 4 个字节来存储
DECIMAL. DECIMAL(13,4)表示 9 位 + 4 位小数位（小数位）=> 4 + 2 个字节 = 6 个字节- 与存储所需的 8 个字节相比
BIGINT。
回答by bizwiz
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 的情况下，使用DECIMAL(15,2). 如果比特币将升至 100,000 美元或更多，我们至少需要DECIMAL(18,9)在我们的应用程序中支持加密货币。
DECIMAL(18,9)takes 12 bytes of space in MySQL (4 bytes per 9 digits).
DECIMAL(18,9)在 MySQL 中需要 12 个字节的空间（每 9 个数字 4 个字节）。
