[TriLUG] Money in mysql

[David Rasch]

Phillip Rhodes 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.
>
>
> There's a train of thought amongst some folks that it's best
> to store and calculate "money" amounts as integers, in terms of
> cents, instead of floating point types in terms of dollars.
> Ie, 1 dollar becomes '100' instead of '1.00'.  The idea is to eliminate
> the possiblity of rounding errors that can be introduced by conversions
> to and from floating point types, and in floating point arithmetic
> itself.
>
> Outside of that, I know nothing specific to mysql to indicate
> on field type over another. 


The Decimal type is what one might think they would want for storing 
money.  This stores numbers by the digit, and thus will not be subject 
to floating point _STORAGE_ errors.  I.e. when 0.10 is put in, you will 
get _exactly_ 0.10 out.  The problem is, that when you pull this number 
out of the database, you're typically (depending on the language) going 
to be storing it in a floating point type variable, which will _not_ 
represent 0.10 exactly (though it will be close).  It is indeed a good 
practice to store money as integers with an implied *10^-2 or *10^-3

David