Storing currency values in MySQL database
currency, mysql
Solution
Financial data can be stored as `DECIMAL(p,s)` where p is the number of significant digits, and s is the scale (number of places to the right of the decimal point). See The MySQL documentation.
Also from O'Reilly's High Performance MySQL, chapter 3:
"...you should use DECIMAL only when you need exact results for fractional numbers--for example, when storing financial data."
From O'Reilly's Learning MySQL:
`DECIMAL` is, "...A commonly used numeric type. Stores a fixed-point number such as a salary or distance..."
And MySQL Pocket Reference:
`DECIMAL` "...Stores floating-point numbers where precision is critical, such as for monetary values."
Problem
This question has been asked many times before, but I've found conflicting opinions on the topic so I thought I would bring it up again in hopes of a more unified conclusion. I would like to store a currency value in my database. Let's assume all entries are the same type of currency (USD for example) and that both positive and negative values are allowed. My initial thought would be to store the value as a signed integer in terms of the smallest unit of the associated currency. For example, if I want to store the value $1.25, I would insert `125` into the database, since the smallest unit of USD is $0.01. The nice thing about this method is that MySQL will automatically round to the nearest integer. For example, if the dollar value is $1.259, I could insert `125.9`, which would automatically be rounded and stored as `126` or $1.26. So, what do you think? Is this a sound approach or is there a better way?