How to Store Monetary Values on MySQL Databases

Unlocking Financial Precision: Best Practices for Storing and Managing Monetary Values in SQL Databases

TL;DR
Never use float or double .

They are approximations, are not precise enough.

use decimalor integer instead.

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision values. — MySQL Reference Manual

This problem is so recurrent that MySQL created one section in the documentation to explain the problems of using Floating-Point Values

In this post, I will simplify that with a quick proof of concept around operations with monetary values.

Proof of concept

If you already read something I’ve written, you know I always like to give you a quick proof of concept.

Follow me in this one.

First, let’s build our example table called money :

create table money (
 id serial,
 value_1 double,
 value_2 double
)

then, populate with some data:

insert into money (value_1, value_2) values 
(1000.2, 200.2), 
(-800.0, 0.0)

We now have:

SELECT * FROM money

So, if we decide to sum the column’s values we should have the same value on both sides (200.2), right?

Well…

SELECT SUM(value_1), SUM(value_2) from money;

No! This is what happens when you store money data with double or float. These types are approximations.

And whenever you deal with money it should be extremely precise.

How to proceed then?

Let’s try out the same example, but now with decimal as a value type.

Quick note: MySQL has the notation numeric but it is just an alias to decimal , so you can use numeric if you wanna to.

create table money (
 id serial,
 value_1 decimal(10, 2),
 value_2 decimal(10, 2)
)

Now, with decimal , we need to specify the number of digits (in this case 10) and allocate 2 digits to be after decimal.

Let’s take a look at it and see how it will behave now:

SELECT * FROM money;

as you can see the values are now following the pattern that we define with 2 digits for the decimal value.

Proceeding to the SUM, the operation is now correct:

Sum using decimal type

Ok, so should I use decimal every time that I deal with monetary value?

The answer is: It depends!

The idea of this article is to call your attention to these weird scenarios that you can face if you decide to go with float or double .

One other alternative, when dealing with monetary values, is to store the value as an integer , and multiply by 100.

This way you are only storing the “cents” and you can control the size of the precision.

That’s everything for today!

Don’t forget to follow and express your gratitude 👏