MySQL 在mysql中存储金额
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2251290/
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
storing money amounts in mysql
提问by David
I want to store 3.50 into a mysql table. I have a float that I store it in, but it stores as 3.5, not 3.50. How can I get it to have the trailing zero?
我想将 3.50 存储到 mysql 表中。我有一个存储它的浮点数，但它存储为 3.5，而不是 3.50。我怎样才能让它有尾随零？
回答by Morfildur
Do not store money values as float, use the DECIMAL or NUMERIC type:
不要将货币值存储为浮点数，请使用 DECIMAL 或 NUMERIC 类型：
Documentation for MySQL Numeric Types
EDIT & clarification:
编辑和澄清：
Float values are vulnerable to rounding errors are they have limited precision so unless you do not care that you only get 9.99 instead of 10.00 you should use DECIMAL/NUMERIC as they are fixed point numbers which do not have such problems.
浮点值容易受到舍入错误的影响，因为它们的精度有限，因此除非您不在乎只得到 9.99 而不是 10.00，否则您应该使用 DECIMAL/NUMERIC，因为它们是不存在此类问题的定点数。
回答by Andy Joiner
It's not generally a good idea to store money as a float as rounding errors can occurr in calculations.
将货币存储为浮点数通常不是一个好主意，因为计算中可能会出现四舍五入错误。
Consider using DECIMAL(10,2) instead.
考虑使用 DECIMAL(10,2) 代替。
回答by graham.reeds
Does it really matter if it stores is as 3.5, 3.50 or even 3.500?
如果它存储为 3.5、3.50 甚至 3.500，这真的很重要吗？
What is really important is how it is displayed after it is retrieved from the db.
真正重要的是它从数据库中检索后的显示方式。
Or am I missing something here?
还是我在这里遗漏了什么？
Also don't use a float, use a decimal. Float has all sorts of rounding issue and isn't very big.
也不要使用浮点数，使用小数。Float 有各种四舍五入的问题，而且不是很大。
回答by Thiago Belem
回答by Andy Shellam
Why do you want to store "3.50" into your database? 3.5 == 3.50 == 3.5000 as far as the database is concerned.
为什么要将“3.50”存储到数据库中？就数据库而言，3.5 == 3.50 == 3.5000。
Your presentation and formatting of figures/dates/etc should be done in the application, not the database.
数字/日期/等的演示和格式设置应该在应用程序中完成，而不是在数据库中完成。
回答by MatsT
If you use DECIMAL or NUMERIC types, you can declare them as for example DECIMAL(18, 2) which would force 2 decimals even if they were 0. Depending on how big values you expect you can change the value of the first parameter.
如果您使用 DECIMAL 或 NUMERIC 类型，您可以将它们声明为例如 DECIMAL(18, 2) ，即使它们是 0，也会强制使用 2 个小数。根据您期望的值有多大，您可以更改第一个参数的值。
回答by SnellyCat
Binary can't accurately represent floating points with only a limited number of bits. It's not so muuch loss of data but actually conversion errors.. Here's the manual giving examples
二进制不能准确地表示只有有限位数的浮点数。这不是太多的数据丢失，而是实际上转换错误.. 这是手册给出的例子
You can see this in action in your browser, see for yourself in this code snippet.
您可以在浏览器中看到此操作，请在此代码片段中亲自查看。
<script>
var floatSum = 0;
// add 0.1 to floatSum 10 times
for (var i=0; i<10; i++) {
floatSum += 0.1;
}
// if the repetative adding was correct, the floatSum should be equal to 1
var expectedSum = 10*0.1; // 1
// you can see that floatSum does not equal 1 because of floating point error
document.write(expectedSum + " == " + floatSum + " = " + (expectedSum==floatSum) + "<br />");
// --- using integers instead ---
// Assume the example above is adding ￡0.10 ten times to make ￡1.00
// With integers, we will use store money in pence (100 pence (also written 100p) in ￡1)
var intSum = 0;
// add 0.1 to floatSum 10 times
for (var i=0; i<10; i++) {
intSum += 10;
}
// if the repetative adding was correct, the floatSum should be equal to 1
var expectedSum = 10*10; // 100
// you can see that floatSum does not equal 1 because of floating point error
document.write(expectedSum + " == " + intSum + " = " + (expectedSum==intSum) + "<br />");
document.write("To display as £ instead of pence, we can divide by 100 (presentation only) : £" + intSum/100 + "<br />");
</script>