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