Is it okay to store money as decimal(22,2) in MySQL?

Question

Is it okay to store money values as decimal(22,2) in MySQL? I am writing a web based marketplace and Ad network for a virtual world.

Solution 1

What if someone has sextillion dollars?!? The field couldn't contain this massive pile of benjamins....

In seriousness though, SQL Server and PostgreSQL have a money datatype, and Access has a currency datatype. Oracle and MySQL have neither.

This thread - Best data type for storing currency values in a MySQL database

however recommends 4 significant digits for some reason, I suppose in case you need fractional cents (stock shares, etc).

If no user could have over sextillion dollars (in seriousness), and you don't need to calculate fractions of cents for storage, your format should be just fine.

Solution 2

You should ask yourself "What is the most amount of money someone will have using my program?" and "To what precision do I require my decimal places?"

If you only need to handle up to a billion, then it seems (10,2) would be sufficient. If you wanted more precision for interest calculations perhaps you want (10,5). If you're just keeping track of a payroll and annual salary, it seems (7,2) would be sufficient (and I'd like to be in the 7 part, thanks!)

Without knowing the purpose of your data, it's hard to answer this. All I can give you is the questions you have to ask yourself.

Solution 3

If you are storing money in a database, you can use the int type but store in the minimal value (cent for dollars), then just divide by 100 when needed. It will work faster.

Comments

[Keverw]

Yeah. i think this will work. I doubt anyone even has that much money in this game! But i do wish MySQL would add a money type.

[Brad Peabody]

Bear in mind that if the application may expand to take into account other currencies, you may very well need more than 2 digits for the precision. Bitcoin, as the most extreme example I'm aware of, uses 8 digits precision. And there are several others that use 3 digits.