How to Store Decimal Currency Values in MySQL CodeUnit 17 JAN 2010

Now while storing integers and numerals in MySQL is pretty rock solid and easy to do, storing decimal values like currency, in other words money, isn’t quite as intuitive when you have a phpMyAdmin structure tab open in front of you.

When it comes to storing non integer numbers, you pretty much have the choice of taking either the FLOAT or DECIMAL route. Now FLOAT is intended for very large values and rounds as a floating point, which is a bit of a problem at times because floating point arithmetic is not entirely exact, particularly when it comes to rounding – which is definitely not a behaviour you want went it comes to adding and subtracting all those cents in any of your financial applications.

DECIMAL was introduced as an “exact packed decimal number”, but prior to MySQL 5.0, any calculations performed on a DECIMAL type column would be done using floating point arithmetic, which didn’t really solve the problem. However, starting with MySQL 5.0 and above, the server now uses a separate fixed-point arithmetic library for this, making it far more precise and far less prone to rounding errors.

Thus the best way to currently store your money value in whatever currency, be it in Rands or US Dollars is to declare a DECIMAL column type and assign it a length/value of 10,2 (where 2 indicates the length of the fractional part of the number).

(Of course you could always be a Smart Alec and simply save everything as cents in the first place)

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.