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
-- 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
