PHP: MySql Data Type for monetary values

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 eff开发者_JAVA技巧icient way to store monetary values?

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.

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.

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

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.