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)?
Which data type do I have to use for this purpose?
VARCHAR or INT (or other numeric data types)?
Answers
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 likedecimal(15,2)
15
is the precision (total length of value including decimal places)2
is the number of digits after decimal point
See MySQL Numeric Types:
These types are used when it is important to preserve exact precision, for example with monetary data.
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)
.
If you work with big values replace
10
with 19
.
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
Usually you should sum your money values at 13,4 before rounding of the output to 13,2.
Try using
Decimal(19,4)
this usually works with every other DB as well
Multiplies 10000 and stores as BIGINT, like "Currency" in Visual Basic and Office. See https://msdn.microsoft.com/en-us/library/office/gg264338.aspx
0 comments:
Post a Comment