least expensive MySQL data type for storing currency values?

[Alex]

been reading through the reference for a bit now, just wondering whether someone has a quick answer pls!

thanks!
-Alex

 

[Drakkon]

how big of a currency value are you calculating from? If LARGE values (i.e millions, billions, trillions) use a Decimal as it will automatically adjust storage based on size, if anything smaller you can use a smallint or tinyint and store the number by multiplying by 100 then whenever drawing it out dividing by 100.

 

[Alex]

we're talking between thousands and tens of thousands, with an absolute theoretical maximum of hundreds of thousands, but 1 million is never going to be an option.

sorry i didnt quite get the whole multiply and divide int thing... decimals are gonna be important here and also i want to make sure performance is optimal... so using DECIMAL is a bad idea?
how about FLOAT?

thx

-Alex

 

[jjones]

He's talking about removing the decimal by multiplying by 100 for storage and dividing by 100 for practical use. For example you have an amount like 89.97. Well, 89.97 x 100 is 8997, which is the number you store as an int in the db. When you pull that number later on for use, you divide by 100 and get the original 89.97 back.

Whether or not that is better, or more efficient, than any other option, I couldn't tell you.

 

[Drakkon]

Originally posted by: jjones
He's talking about removing the decimal by multiplying by 100 for storage and dividing by 100 for practical use. For example you have an amount like 89.97. Well, 89.97 x 100 is 8997, which is the number you store as an int in the db. When you pull that number later on for use, you divide by 100 and get the original 89.97 back.

Whether or not that is better, or more efficient, than any other option, I couldn't tell you.

Exactly. Its deifnately not more efficient but he was concerned about storage issues and by far an int is smaller to store than a decimal.

It sounds like you'd be better off storing as a Decimal. Floats are better for more decimal places (i.e. scientific notation). Decimals seem to store less when it comes to only 2-3 decimal places.
See http://dev.mysql.com/doc/refma...rage-requirements.html
and discussion on Decimal/Numeric http://dev.mysql.com/doc/refma.../en/numeric-types.html

 

[lousydood]

Never use floating point numbers for money. It will only cause trouble. IEEE 754 floating point numbers can't even represent the number 0.10 exactly. It is a repeating decimal in base 2. Just because it has a decimal point does not mean that it can represent all Real numbers -- in fact, doing so would be impossible due to fundamental computability reasons. Even basic properties of normal arithmetic like associativity of addition are not assured by FP arithmetic.

Every programmer should read this:

http://docs.sun.com/source/806-3568/ncg_goldberg.html

Store the number of pennies -- integers are safe and easy. Also, if you're doing arithmetic with money, then you'd better consider what you do when you have to divide an amount and have a remainder.

(or Decimal, which seems to be an exact number type, but most definitely not Float)