CODEWITHSUNDEEP
mysqlcurrency
David Jones
David Jones

Reputation: 10219

Storing currency values in MySQL database

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?

Upvotes: 12

Views: 14489

Answers (2)

DavidO
DavidO

Reputation: 13942

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."

Upvotes: 13

Nickoli Roussakov
Nickoli Roussakov

Reputation: 3409

There is nothing wrong with the approach you describe. There is no right or wrong when it comes to this question.

You have to keep in mind that to display a $ value to the user, you would need to always do a division operation or some kind of formatting.

Will it be easier to debug your formulas/calculations if everything was in cents or dollars? Will you be displaying the values in cents or dollars? etc.

Keep it simple, but either way you'll have to use a decimal.

Upvotes: 2

Related Questions