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.

  • Hey,

    I am testing a db on my computer before I upload, and am using DECIMAL (6,2). The values for the prices I insert seem to always have the cents set to zero, so when I put in the value 150.95 the price I get when displaying the data is R150.00. Is this because of the mysql version, or could it be something else?
    .-= Francois´s last blog ..Another website goes live =-.

  • Hey,

    I am testing a db on my computer before I upload, and am using DECIMAL (6,2). The values for the prices I insert seem to always have the cents set to zero, so when I put in the value 150.95 the price I get when displaying the data is R150.00. Is this because of the mysql version, or could it be something else?
    .-= Francois´s last blog ..Another website goes live =-.

  • Hi Francois. Which version of MySQL are you using and what are the SQL statements being used to insert the values into the table?

  • Hi Francois. Which version of MySQL are you using and what are the SQL statements being used to insert the values into the table?

  • Pingback: Dec-Met Ready Reckoner for Decimal Currency Values of Kilogrammes and Tonnes()

  • Craig, Thx for posting the screenshot of adding a decimal data type in phpadmin…just what I was looking for. Cheers, Richard

  • Cheers for that, I somehow missed the DECIMAL type when looking through the drop down :(

  • Changing from Sql server to MySql… This information was really helpful!

  • Changing from Sql server to MySql… This information was really helpful!

  • Magz

    Thanx for that.  I needed a quick answer, did a quick search on google, and your page came up on top.  Luckily you gave me the answer I needed and didn’t have to go on searching. 

  • Cmdillibabu

    It is not working by me

  • Cameron

    Helped me out thanks really appreciate it