Re: Best practice column type for storing decimal currency amounts?

[John Hicks]

Thanks for the reply, Rudy.

My source for the statement that decimal values are stored 
as strings is:
http://www.mysql.com/doc/en/Numeric_types.html :

"DECIMAL and NUMERIC values are stored as strings, rather 
than as binary floating-point numbers, in order to preserve 
the decimal precision of those values. One character is 
used for each digit of the value, the decimal point (if 
scale > 0), and the `-' sign (for negative numbers). If 
scale is 0, DECIMAL and NUMERIC values contain no decimal 
point or fractional part."

But you sound knowledgeable. Is the manual in error?

--John


On Wednesday 16 July 2003 04:36 am, Rudy Metzger wrote:
> Problem is that DECIMALs are currently stored as FLOAT or
> DOUBLE in the DB (at least MyISAM). MySQL AB is busy with
> adding a true monetary type (like MONEY) to the system.
>
> What I am doing when using monetary values is putting
> them into floats if I can live with rounding problems or
> put them into in ints and multiply them by 100 (or 1000)
> before putting them there (thus taking care of the
> decimal point myself).
>
> Cheers
> /rudy
>
> ps: I wish DECIMALS would be stored as char :)
>
> -----Original Message-----
> From: John Hicks [mailto:johnlist@stripped]
> Sent: dinsdag 15 juli 2003 21:39
> To: mysql@stripped
> Subject: Best practice column type for storing decimal
> currency amounts?
>
> Is there an accepted best practice on whether to store
> decimal currency amounts (e.g. dollars and cents) in
> MySQL decimal column types?
>
> Certainly, the most straightforward way is to use decimal
> columns. But it appears that such values are stored as
> ASCII strings, which would be inefficient for
> calculations (requiring conversion to a numeric type for
> each calculation).
>
> I guess the alternative would be to use integer columns
> (and multiply by 100 to store the value as total cents).
>
> My particular context is a PHP/MySQL sales system.
>
> What's the consensus?
>
> Thanks much,
>
> --John