This question has been asked many times before, but I've found conflicting opinions on the topic so I thought I would bring it up again in hopes of a more unified conclusion.
这个问题之前已被多次提出过，但我发现有关该主题的意见相互矛盾，所以我想我会再次提出这个问题，希望得到更统一的结论。
I would like to store a currency value in my database. Let's assume all entries are the same type of currency (USD for example) and that both positive and negative values are allowed.
我想在我的数据库中存储货币值。假设所有条目都是相同类型的货币（例如美元），并且允许正值和负值。
My initial thought would be to store the value as a signed integer in terms of the smallest unit of the associated currency. For example, if I want to store the value $1.25, I would insert 125 into the database, since the smallest unit of USD is $0.01. The nice thing about this method is that MySQL will automatically round to the nearest integer. For example, if the dollar value is $1.259, I could insert 125.9, which would automatically be rounded and stored as 126 or $1.26.
我最初的想法是将值存储为相关货币的最小单位的有符号整数。例如，如果我想存储值$ 1.25，我会将125插入数据库，因为最小的USD单位是0.01美元。这个方法的好处是MySQL将自动舍入到最接近的整数。例如，如果美元值为1.259美元，我可以插入125.9，这将自动舍入并存储为126或1.26美元。
So, what do you think? Is this a sound approach or is there a better way?
所以你怎么看？这是一种合理的方法还是有更好的方法？
2 个解决方案
#1
13
Financial data can be stored as DECIMAL(p,s) where p is the number of significant digits, and s is the scale (number of places to the right of the decimal point). See The MySQL documentation.
财务数据可以存储为DECIMAL（p，s），其中p是有效位数，s是小数位（小数点右边的位数）。请参阅MySQL文档。
Also from O'Reilly's High Performance MySQL, chapter 3:
同样来自O'Reilly的高性能MySQL，第3章：
"...you should use DECIMAL only when you need exact results for fractional numbers--for example, when storing financial data."
“......只有在需要精确的小数数字结果时才应使用DECIMAL - 例如，存储财务数据时。”
From O'Reilly's Learning MySQL:
来自O'Reilly的学习MySQL：
DECIMALis, "...A commonly used numeric type. Stores a fixed-point number such as a salary or distance..."DECIMAL是，“......一种常用的数字类型。存储定点数，例如薪水或距离...”
和MySQL Pocket参考：
DECIMAL"...Stores floating-point numbers where precision is critical, such as for monetary values."DECIMAL“......存储精度至关重要的浮点数，例如货币价值。”
#2
2
There is nothing wrong with the approach you describe. There is no right or wrong when it comes to this question.
您描述的方法没有任何问题。谈到这个问题没有对错。
You have to keep in mind that to display a $ value to the user, you would need to always do a division operation or some kind of formatting.
您必须记住，要向用户显示$值，您需要始终执行除法运算或某种格式化。
Will it be easier to debug your formulas/calculations if everything was in cents or dollars? Will you be displaying the values in cents or dollars? etc.
如果一切都是美分或美元，调试你的公式/计算会更容易吗？你会以美分或美元显示价值吗？等等
Keep it simple, but either way you'll have to use a decimal.
保持简单，但无论哪种方式，您都必须使用小数。
#1
13
Financial data can be stored as DECIMAL(p,s) where p is the number of significant digits, and s is the scale (number of places to the right of the decimal point). See The MySQL documentation.
财务数据可以存储为DECIMAL（p，s），其中p是有效位数，s是小数位（小数点右边的位数）。请参阅MySQL文档。
Also from O'Reilly's High Performance MySQL, chapter 3:
同样来自O'Reilly的高性能MySQL，第3章：
"...you should use DECIMAL only when you need exact results for fractional numbers--for example, when storing financial data."
“......只有在需要精确的小数数字结果时才应使用DECIMAL - 例如，存储财务数据时。”
From O'Reilly's Learning MySQL:
来自O'Reilly的学习MySQL：
DECIMALis, "...A commonly used numeric type. Stores a fixed-point number such as a salary or distance..."DECIMAL是，“......一种常用的数字类型。存储定点数，例如薪水或距离...”
和MySQL Pocket参考：
DECIMAL"...Stores floating-point numbers where precision is critical, such as for monetary values."DECIMAL“......存储精度至关重要的浮点数，例如货币价值。”
#2
2
There is nothing wrong with the approach you describe. There is no right or wrong when it comes to this question.
您描述的方法没有任何问题。谈到这个问题没有对错。
You have to keep in mind that to display a $ value to the user, you would need to always do a division operation or some kind of formatting.
您必须记住，要向用户显示$值，您需要始终执行除法运算或某种格式化。
Will it be easier to debug your formulas/calculations if everything was in cents or dollars? Will you be displaying the values in cents or dollars? etc.
如果一切都是美分或美元，调试你的公式/计算会更容易吗？你会以美分或美元显示价值吗？等等
Keep it simple, but either way you'll have to use a decimal.
保持简单，但无论哪种方式，您都必须使用小数。