UPDATE: Several people have commented that decimal(10,2) is not correct for money, since sometimes currencies go out to more than 2 decimal places. Others claimed that storing cents (or base unit) as integers make it simpler to perform calculations (thanks, Kevin Farley for your comment). Regardless of what you choose – don’t use floats for money. If you do use integers, I would include the base unit in the name to avoid confusion (AmountInCents).

Data types make all the difference in the world when you’re designing your database. The choices you make now will affect the quality of your data, as well as application performance. I’m going to focus on one issue in this article: why you should always use decimals to represent money. Let’s jump in and see why that’s true.

## An example of floats gone wrong

Let’s use a really, really simplified accounting ledger. It’s just three fields, an entry id, a customer id, and an amount, which has a float for a data type.

Create Table LedgerEntries ( LedgerEntryID Int Primary Key Auto_Increment Not Null ,CustomerID Int Not Null ,Amount Float Not Null );

Now let’s load some data.

Insert Into LedgerEntries (CustomerID, Amount) Values (1, 3.14); Insert Into LedgerEntries (CustomerID, Amount) Values (1, 30000.14);

Great! No errors or warnings! But when I run select:

Select * From LedgerEntries; +---------------+------------+---------+ | LedgerEntryID | CustomerID | Amount | +---------------+------------+---------+ | 1 | 1 | 3.14 | | 2 | 1 | 30000.1 | +---------------+------------+---------+ 2 rows in set (0.00 sec)

I don’t get the data I expect. That second entry is missing $0.04. Now that might not seem like much, but here’s an even bigger rounding error:

Select * From LedgerEntries; +---------------+------------+-----------+ | LedgerEntryID | CustomerID | Amount | +---------------+------------+-----------+ | 1 | 1 | 3.14 | | 2 | 1 | 300000 | | 3 | 1 | 300000000 | +---------------+------------+-----------+

Ouch! A hundred bucks just disappeared into the ether! Why do floats perform this way? They’re actually stored as base-2 numbers, whereas we care about base-10 numbers when we’re talking about money. They cannot represent some decimal values, so they round in ways you don’t expect. They store the number as two parts, a mantissa and an exponent, which means they represent a number as x^y and store both of those separately. This is great for some uses (which we’ll get to later), but not for storing money, or any other decimal value you’d like to store exactly.

## Using decimals for money

Let’s run through the same example with a decimal amount:

Create Table LedgerEntries ( LedgerEntryID Int Primary Key Auto_Increment Not Null ,CustomerID Int Not Null ,Amount Decimal(10,2) Not Null ); Insert Into LedgerEntries (CustomerID, Amount) Values (1, 3.14); -- This is the largest value we can insert into a Decimal(10,2) -- if we have two numbers to the right of the decimal point Insert Into LedgerEntries (CustomerID, Amount) Values (1, 99999999.99); Select * From LedgerEntries; +---------------+------------+-------------+ | LedgerEntryID | CustomerID | Amount | +---------------+------------+-------------+ | 1 | 1 | 3.14 | | 2 | 1 | 99999999.99 | +---------------+------------+-------------+ 2 rows in set (0.00 sec)

Great! We don’t see the same rounding we saw with floats. Decimals store the number as two parts as well: one for the numbers to the left of the decimal point and one for the right of the decimal point. When you declare the Decimal type, you specify how many digits the entire number will be (precision) as well as how many digits will be to the right of the decimal point (scale). If you try to insert a value with too many digits, MySQL 5.6 will return this error:

Error Code: 1264. Out of range value for column 'Amount' at row 1

If you’re not on MySQL 5.6 or you have an empty SQL mode, you will get a warning and MySQL will convert 9999999999 to 99999999.99. Always use Strict_Trans_Table in your sql mode.

Unfortunately, MySQL, even in strict mode, will silently truncate data on the right side of the decimal point. You’ll get this warning for inserting 3.148 into Amount:

1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'Amount' at row 1

The value will be 3.14. Not cool, MySQL, not cool.

## Application code data types

Some languages such as C# have a primitive decimal type, while others like Java use objects to represent their values. Do some research and make sure that you’re using only integer or fixed point numbers to represent money or other numbers that are decimals that need to be exact.

The other option is to make your own data type on top of integer. This means you store all your money in pennies, and divide by 100 when you want to display it to the user and multiply it by 100 when you get money in dollars.

## The value of floats

Floats are typically used for scientific calculations. They can store very large and very small numbers as long as you’re okay with some rounding errors. By that I mean 10^-48 to 10^38. That’s a big range. The error is relative to the size of number, so a million will have a different error than say a thousand.

If you enjoyed this article, sign-up below to get more just like it as well as news on a book I’m writing to help people get to the next level in MySQL.

**Ready to take your MySQL skills to the next level?**

You can **subscribe below** and I’ll send you updates on a new book I’m writing that helps you work with MySQL like a pro. If you sign-up, I’ll also send out stuff on MySQL like tips and tutorials.

Hi Noel,

I absolutely agree that floats should never be used when dealing with money!

But the truncation to two decimal digits is standard-compliant, in fact the standard does not even demand a warning here.

I imagine that the meaning is that if you care to calculate with three decimals, you would have defined the datatype as DECIMAL(11,3)

That’s true, I remember reading that on another blog. Would you agree that regardless of the standard, an error should be thrown? Or if you were setting the standard, would you keep it the way it is? Couldn’t you use your same line of reasoning to say, if you meant to store a string with three characters, you would have defined the datatype as VARCHAR(3) not VARCHAR(2)? I’m struggling to see the difference, so your expertise would be appreciated.

I think the standard is reasonable. By the laws of mathematics, 3.14 is a correct approximation for pi when inserted into a DECIMAL(10,2) column (even though you probably would use a float or double for that.) When you represent money as DECIMAL(10,2) and calculate an interest for a year as 10.353 dollars, by saying that you want only two decimals, it should be fine to truncate this to the value 10.35. If you were to give errors when truncation occurs, you would probably get an annoying number of errors.

But if you try to assign my name to a CHAR(8) column, significant parts of the name is lost, and returning an exception looks reasonable. For character strings, you cannot say that the leading part is more significant than the trailing part, as you can for decimal numbers.

Having gone through a sea-change in our own platform over the last year, I had to pass on some wisdom which we learned the hard way after years of using a number of decimal based fields (float, decimal, double) to store currency.

While everything you state about the different fractional field types is correct, currency should NEVER EVER EVER be stored with decimals, it should be stored as INTEGERS. All currencies should be reduced to their base unit, which is not Dollars, it’s Cents. Not only does it help avoid calculation errors in other fractional field types, but it makes currency conversion a hell of a lot easier as your application grows. Not having to deal with fractions, and rounding (at least to a lesser degree) makes a world of difference.

Consider also that a number of currencies have three decimal places in their common unit (not base unit), and so the idea of using Decimal(10,2) makes it very difficult to expand. Finally there are also storage and mathematical efficiencies that come with using integers vs fractional field types.

Overall, I’ll repeat, “NEVER use decimal for money, use integers.”

Hope that helps others avoid the learning curve we had to go through.

Cheers,

Kevin.

You MUST use DECIMAL (13, 4) because some currencies use up to 4 digits after the decimal separator.

http://en.wikipedia.org/wiki/ISO_4217#Active_codes

https://rietta.com/blog/2012/03/03/best-data-types-for-currencymoney-in/

Kevin,

You are spot on!

Right now i’m using biginteger, since i dont need any decimal for my currency