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.

score 16 · Accepted Answer · edited May 23 '17 at 12:01

16

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.

edited May 23 '17 at 12:01

Community

1
1

answered Mar 05 '11 at 04:36

Brandon Frohbieter

17,563
3
40
62

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. – Keverw Mar 10 '11 at 10:24
1

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. – Brad Peabody Feb 05 '17 at 21:40

score 6 · Answer 2 · answered Mar 05 '11 at 04:43

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.

score 2 · Answer 3 · edited Apr 12 '14 at 21:50

2

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.

edited Apr 12 '14 at 21:50

Kevin

53,822
15
101
132

answered Apr 12 '14 at 17:34

user2176459

41
5

Zach Smith · Answer 4 · 2018-06-11T03:59:41.010

The accepted answer by @Orbit on this page mentions that SQL Server has a money data type.

However, according to this answer (Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?), a better choice for storing monetary values in SQL Server is decimal(19,4) - which is also available in MySQL.

From what I understand you should be careful when performing calculations with the money type in SQL Server due to floating point rounding errors. And it may use the decimal(19,4) type indirectly anyway.

So... the decimal(22,2) type would be fine to store monetary values... But as a lot of people seem to recommend, decimal(19,4) would be better.

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

4 Answers4