[TriLUG] Money in mysql

[Rick DeNatale]

Actually, in the "old days" the data type was fixed point decimal.
PL/I support(s/ed) various combinations of decimal or binary and fixed
or float. An integer is a special case of fixed with a zero length
fractional part. I think COBOL supports some subset of those, maybe
all. (Used to program a lot in PL/I, I've only read a few COBOL
programs).

MySQL does have a DECIMAL datatype aka DEC, NUMERIC, or FIXED which
are all synonyms.
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
This doc indicates that it's stored as a CHAR value, with one
character used for each digit and for the decimal point and the minus
sign if necessary. In effect the number is stored in printable form. 
However, the comment on the range leads me to think that if you do
arithmetic in the database, it's going to convert the values to
DOUBLES (i.e. floats) do the arithmetic and convert the result back to
DECIMAL, so you run the risk of the kind of things which happen with
floats.

The MySQL white paper on conversion from MS which someone else posted,
indicates that Acess and SQL Server has a CURRENCY data type which
MySQL lacks, but that it's the same as the DECIMAL(19,4) datatype.
This means that it can store a value with 19 digits, four of which are
after the decimal point, taking up a total of 21 characters. It also
indicates that they'll actually use the unused character for the +
sign for positive numbers.

And there are other issues when you assign one DECIMAL number to
another with different precision (total number of digits) and/or scale
(number of digits after the decimal point) which can be platform
dependent.

Computers are like that actor on the old TV Commercial: They don't
really do Arithmetic, we just portray it the best way they can.  Once
you muddy up the abstract notion of numbers with the need to actually
represent them things get complicated.


On Thu, 14 Oct 2004 10:24:30 -0400, Joseph Mack <mack.joseph at epa.gov> wrote:
> Matt Frye wrote:
> >
> > What's the best numeric type in mysql for dealing with money, i.e.
> > dollars and cents?  Float, decimal, fixed?  Informed suggestions
> > appreciated.
> 
> In the old days all money calculations were done in BCD (binary coded decimal)
> which was designed for money and was exact for floating point addition/subtraction.
> Does mysql have BCD?
> 
> Joe
> 
> --
> Joseph Mack PhD, High Performance Computing & Scientific Visualization
> LMIT, Supporting the EPA Research Triangle Park, NC 919-541-0007
> Federal Contact - John B. Smith 919-541-1087 - smith.johnb at epa.gov
> 
> 
> --
> TriLUG mailing list        : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG Organizational FAQ  : http://trilug.org/faq/
> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
> TriLUG PGP Keyring         : http://trilug.org/~chrish/trilug.asc
>