Hmmm.... The trainer on our "Managing MySQL" told us that they are stored as FLOATS/DOUBLES. But I could have wrong recollection of it, maybe he meant they are stored as strings and only CALCULATION is done with DOUBLES.
I this case, I would trust the manual :) But still, it is no true monetary calculation as you will get these floating point errors with doubles. On the other hand it is good to know that they store it as STRING, because some legal institutes require you to store numbers in "human readable format" (e.g. some bookkeeping data). Thanx to point out my error, John! Cheers /rudy -----Original Message----- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 22:03 To: Rudy Metzger; [EMAIL PROTECTED] Subject: Re: Best practice column type for storing decimal currency amounts? 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:[EMAIL PROTECTED] > Sent: dinsdag 15 juli 2003 21:39 > To: [EMAIL PROTECTED] > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
