The Short Answer (TL;DR)
If GAAP Compliance is required or you need 4 decimal places:
1


Which supports a max value of:
$999,999,999.9999
Otherwise, if 2 decimal places is enough:
1


Which supports a max value of:
$99,999,999,999.99
Too Short; Want More
Some relational databases support a money type, but we aren’t as lucky when it comes to MySQL. This leaves the choice of data type to the developer.
Since MySQL 5 we have the luxury of using DECIMAL
as a datatype.
Note that DECIMAL
and NUMERIC
are the same datatype.
From the MySQL manual:
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.
The DECIMAL
data type allows you to specify the total number of digits as
well as the number of digits after the decimal point. If no values are specified,
the default is DECIMAL(10,0)
which is not suitable for money.
If you need to support more than the max value stated above, you can increase
the first argument (13 in the example) to fit your needs. Likewise,
the second number of DECIMAL
increases the
number of digits after the decimal point.
If the application needs to handle money values up to a trillion dollars (or any other currency), then use 2 decimal places:
1


If you need to comply with GAAP, then use 4 decimal places:
1


When displaying this value to the end user, finish all mathematical operations
utilizing the more accurate 4 decimal places, then round the final result to
two decimal places (ROUND(number, 2)
in MySQL). GAAP is concerned with ensuring
numerical rounding errors are small enough to be considered nonmaterial even
when dealing with many entries.