>keywords: mysql accounting currency decimal fixed "floating point"
Quoting the manual:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
If D is omitted, the default is 0. If M is omitted, the default is 10.
All basic calculations (+, -, *, /) with DECIMAL columns are done with
a precision of 64 decimal digits.
Re: Floating Point limitations and using IEEE format floating point
many numbers cannot be stored exactly in a FP format
FP inaccuracies are bound to cause error and worry. round() has its
own problems.
Should I use
* double and also use round()
or
* int (storing in $ .01 ) and then format for all displays, and
reports
There is no exact representation for 0.01 in binary floating point.
You're just begging for rounding errors if you use quantities of
dollars in a floating point variable.
I prefer to use integer quantities of *cents* (or the smallest unit
of whatever currency you are using). The logical C types to use
are long, long long, double, or long double. Check before proceeding
whether you have enough range. A 32-bit long, for example, can
cover about +/- $20 million, which may be enough for most individuals
(even when calculating lifetime earnings). A 64-bit long long might
even cover 100 years of the Bush (A thru Y) administrations military
spending.
You need special currency formatting routines. You may want them
*anyway*: it is common to represent negative numbers in
ways unusual to math, such as:
100.00-
or (100.00)
Regardless of whether you use decimal in MySQL, you may not have
it available outside of MySQL, so either you'll need to do all
your calculations in MySQL, or worry about representing it outside
MySQL.
Gordon L. Burditt
