I recently ran into a bug where a float value (money) wasn’t saving. If you changed it to double it worked. However, as a result, I came across a warning that you shouldn’t use double or float for storing monetary values but rather decimal/numeric.

I see the reason is to due with loss of data. I assume this is only when doing arithmetic with the values. Can someone give me an example in MySQL storing monetary values where decimal would be accurate and float wouldn’t?

In my case, I lost nothing by using float as I wasn’t doing any arithmetic.

Binary can’t accurately represent floating points with only a limited number of bits.

You can see this in action in your browser, try this:

<script>
var floatSum = 0;
// add 0.1 to floatSum 10 times
for (var i=0; i<10; i++) {
floatSum += 0.1;
}
// if the repetative adding was correct, the floatSum should be equal to 1
var expectedSum = 10*0.1; // 1
// you can see that floatSum does not equal 1 because of floating point error
document.write(expectedSum + " == " + floatSum + " = " + (expectedSum==floatSum) + "<br />");
// --- using integers instead ---
// Assume the example above is adding £0.10 ten times to make £1.00
// With integers, we will use store money in pence (100 pence (also written 100p) in £1)
var intSum = 0;
// add 0.1 to floatSum 10 times
for (var i=0; i<10; i++) {
intSum += 10;
}
// if the repetative adding was correct, the floatSum should be equal to 1
var expectedSum = 10*10; // 100
// you can see that floatSum does not equal 1 because of floating point error
document.write(expectedSum + " == " + intSum + " = " + (expectedSum==intSum) + "<br />");
document.write("To display as £ instead of pence, we can divide by 100 (presentation only) : £" + intSum/100 + "<br />");
</script>

You might get away with using floating point numbers for money because in most cases the precision of a float (or double) is much greater than the smallest monetary unit (cent, penny, etc.) - then when you round to the 2 decimal places you should get the correct number. Problems start when you use arithmetic - the small inaccuracies accumulate to the point of affecting a significant digit. This can be worked around by splitting the calculations into smallest possible units and rounding after each one. Another possible workaround is to multiply currency by 100 so the numbers have no fractional parts and then floating point arithmetic should be accurate.

But these are all hacks and workarounds that we sometimes must resort to in languages that cannot do precise decimal arithmetic. Now that mysql has a decimal data type designed for exact decimal numbers there is no reason to use float or double for that - unless you want to make life harder for yourself in unexpected situations.

You also have to consider that if someone takes a ‘precise decimal’ from a database, and calculations are performed in say PHP, or JavaScript, or 99% of other languages, then you still get the same old risk of inaccuracies. Also if you migrate to a database that doesn’t support ‘precise decimal’ then you will have to convert to integers and update and test all your queries, or accept that your calculations may go wrong over time. IMHO it is still safer to use integers to store numeric data that must be exactly correct.

So it’s clear than monetary values should be stored as DECIMAL in MySQL. Is there anything else that would use DECIMAL? If you’re using it for storage only and no arithmetic is taking place, I guess FLOAT would be fine and even VARCHAR. In my case anyway since PHP will fetch it as a string.

RT_'s point is good as often all calculations are done by PHP, etc. In the real world, does the accuracy only really matter if you’re doing something for a bank or other financial institution?

The rule is anything that needs exact numbers: temperature, distance, depth, speed, time. Do these really need super exact representation? Only you can tell as the author of the application, who else knows how much accuracy you need? Likewise, monetary numbers don’t always need DECIMAL - you may need to store statistical information where small inaccuracies are irrelevant.

Sure, this will work but it’s not good practice and may be confusing to other programmers. You could actually store everything as TEXT and this will work, too, but remember that data types have also another role - to some extent they act like documentation for your db, which you will surely appreciate if you come back to it after many months or years!

My opinion is that we should be using the most appropriate data type in the db for what we store. If we need exact fractional numbers we use DECIMAL - if a language like PHP does not have this data type then it’s not db’s fault and I apply proper workarounds in that language to work around that (e.g. use BC Math, do calculations as integers, etc.) instead of using less appropriate data types in the db.