Rietta: Web Apps Where Security Matters
You are reading The Rietta Blog, a publication about the web since 2005.

Best Data Types for Currency/Money in MySQL?

Comments

Last Updated on September 27, 2013. This post is one of the most visited on this blog and is a maintained post that is periodically updated to be most useful to you. For more, see the list of all maintained posts.

What the best data type is for storing currency/money values in MySQL?

The Short Answer

When handling money in MySQL, use DECIMAL(13,2) if you know the precision of your money values or use DOUBLE if you just want a quick good-enough approximate value.

If you want to be compliant with Generally Accepted Accounting Principles (GAAP), then you should use DECIMAL(13,4), a practice that I have personally adopted since the first publication of this article.

As shown in the details section, when summing a column of money values, compute the sum of the series and then round the final result to two decimal places. If you round each value before adding then that can introduce rounding errors that can be difficult to track down.

The Details

On some database management systems (DBMS) there is a data type specially set aside for handling money. However, MySQL doesn’t have one so the choice of best representation is left to the programmer.

Since about 1999, using MySQL 3, I have generally used the DOUBLE data type to represent money values as it stores decimals at reasonably high precision. The values stored are approximations, but the number of decimals treated accurately is enough for normal money values. The ROUND function is helpful when doing aggregation queries, such as:

1
2
3
4
SELECT
  ROUND(SUM(amount), 2) AS total_amount
FROM orders
WHERE created_at > '2012-01-01'

Notice that the summation is computed before the final result is rounded to two decimal places. This reduces rounding errors that can appear in large datasets.

However, since MySQL 5 there are some better data types for storing exact money values. From the MySQL manual:

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

The DECIMAL data type requires you to specify the number of digits before the decimal point and the number of digits after the decimal point.

So if your application needs to handle money values up to a trillion dollars (or euros or pounds), then this should work:

1
  DECIMAL(13, 2)

Or, if you need to comply with GAAP then use:

1
  DECIMAL(13, 4)

However, considering the rate that the central banks are printing money these days, we might all soon need to be processing larger values for currency.

I hope this helps.

References

One Last Thing

One of the services my company offers is database conversion solutions. Are you looking to convert spreadsheet data to a MySQL database? Check out SQL Converter’s Expert Conversion Service. The classic Excel macro is still available as well.

Revision History

As a maintained post, this document is updated from time to time.

  • January 6, 2014: Updated the video and moved it to the top of the article.
  • September 27, 2013: Updated the introduction to reference GAAP, reformatted the comments that came over from the previous blog format
  • September 3, 2013: Reformatted as part of the migration to the new blog.
  • June 11, 2013: Added this post, which receives a lot of traffic, to the Web Application Topics series and added the embedded video
  • March 3, 2012: Originally posted on The Rietta Blog, hosted on the Blogger platform

Comments

anne.ominous

I learned this back when I was working with money data in the (then relatively new) Microsoft ‘Currency’ type in their ISAM database engine:

If you want to meet Generally Accepted Accounting Principles (GAAP), you need to have at least FOUR decimal places. This ensures that rounding errors will not, on average, exceed $0.01 more often than many thousand transactions (because rounding errors tend to even out).

If, on the other hand, you are only using 2 decimal places, you can expect rounding errors to equal or exceed $0.01 fairly frequently.

Microsoft’s Currency type, by the way, behaves like a 4-decimal-place Decimal type, but is stored in the database as a scaled integer for exactitude.

It is pretty easy to write routines that emulate this behavior. Or as you say, with MySQL you can use Decimal and still get exact storage. But you should use 4 decimal places, not 2.

Frank Rietta

Anne,

I did not know that! Thank you for the excellent insight into using four (4) decimal places to meet GAAP requirements.

Frank

Comments