2

What is the best way to store monetary values in MySql.

I've seen this: decimal(5,2)

but then you can't enter in more than $999.99 for the amount. I mean I know you can change the 5 to something else but this doesn't seem like the most appropriate way to do this.. then again I'm not sure that's why I'm posting.

I've also seen storing the amount as an unsigned int too. So what's the most efficient way to store monetary values?

Improve this question
4
  • All currency will be in USD. The value needs the ability to represent both positive and negative amounts.. and i don't want the column type to round anything.. Does decimal round your numbers? Commented Oct 20, 2010 at 21:42
  • No, DECIMAL values are not rounded -- I have to check but my assumption is they will be truncated (cut off, not rounded) if you attempt to insert a decimal value with more precision (IE: three decimal places into a two decimal place column). Commented Oct 20, 2010 at 21:46
  • Yes,to two places with the suggestions below. If you divide the cost or add tax etc, you're bound to get partial cents to deal with. Then rounding will occur. Commented Oct 20, 2010 at 21:46
  • 1
    Truncating is called rounding down! Commented Oct 20, 2010 at 21:47

4 Answers 4

Reset to default
4

How big a currency value do you anticipate that you need to store? DECIMAL(15,2) would handle what most will throw at it.

I've also seen storing the amount as an unsigned int too.

Unsigned means the value will never be negative -- you'd need additional means to indicate the value is meant to be negative if such is the case. I don't recommend this approach.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Your decision will have to include how you perform rounding. Businesses tend to round cents in their favour, whereas mathematical rounding might be preferred, as well as not caring at all. These may have a bearing on your storage choice.

Improve this answer

Comments

1

And what about currency? If you want to support distributed clients in multiple locales, you'll need to embed the currency / locale of each monetary value. How do other DBs handle this?

For signed-integer values, it is a business decision to decide what is the "minimum value of interest" (do you care about 0.001c? This means that you don't mind losing 1c over 1000 sequential transactions - this is also important in calculating interest on low values, for example). If 0.001c is OK, then multiply all values by 1000 when you write to the table, divide by 1000 when you read them back...

Improve this answer

Comments

0

Why not save the value in the lowest possible unit and let your script do the conversion to the usual representation?

That way you could store the value as an integer.

Improve this answer

5 Comments

Some currencies (IE: Japanese Yen) doesn't have cents, specifically.
I don't think that would matter. Change the above answer to "store in the lowest currency unit" and it works. You would just need to adjust currency conversions accordingly.
@OMG Ponies: Even then you could save the value in the lowest possible entity, so you get an integer.
What is the lowest currency unit, though? Cents are, theoretically, for US dollars, but gas stations sell gas in tenth-of-a-cent prices.
@ceejayoz, you're right there. There are some limitations in my solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.