MySQL's Decimal Type

Recently, I had to fix a web app that was demonstrating some unexpected behavior with monetary values. I traced the problem to a floating point precision issue in the database. There was an insert statement storing a value in a float column, but when I selected that value back from the database, it was different.

To demonstrate this issue, I created an example table in a local MySQL Docker container:

create table financial_ledger (
    id int not null auto_increment primary key,
    amount float(10, 2),
    description text
);

Then, I inserted this value into the table:

insert into financial_ledger (amount, description) values (278992.57, '<- this is a problematic value!');

Finally, I selected the data from financial_ledger:

select * from financial_ledger;

+----+-----------+---------------------------------+
| id | amount    | description                     |
+----+-----------+---------------------------------+
|  1 | 278992.56 | <- this is a problematic value! |
+----+-----------+---------------------------------+

The amount is 278992.56, not 278992.57!

In this example, amount is a float field, and according to the MySQL documentation, that is an "approximate value" numeric type. This type should only be used when having an approximate value is acceptable. I can't think of a situation where it makes sense to use this type for monetary values.

Instead, MySQL has a type called decimal that stores exact numeric values (documentation here). I had always assumed decimal was just an alias for float, so I was really excited to learn that they are very different! If I redo the example using decimal, the correct value gets stored in the database:

drop table financial_ledger;
create table financial_ledger (
    id int not null auto_increment primary key,
    amount decimal(10, 2),
    description text
);
insert into financial_ledger (amount, description) values (278992.57, '<- this is the right value!');
select * from financial_ledger;

+----+-----------+-----------------------------+
| id | amount    | description                 |
+----+-----------+-----------------------------+
|  1 | 278992.57 | <- this is the right value! |
+----+-----------+-----------------------------+

If I was building a new system dealing with monetary values, I would always opt for storing monetary values as integers. (For instance, $150.99 -> 15099) This has the added benefit of also helping avoid floating point issues after the value has been read from the database and is being handled in a programming language like PHP or JavaScript. However, in an existing system that has already mistakenly used the float type for monetary values, decimal is a lovely drop-in replacement that helps avoid bugs due to floating point precision limitations!