Storing monetary amounts in db? Use decimals, not floats!

Lot's of people will be surprised (or shocked, when it happens in production) when they see that mysql can work like this:

Query:
SELECT transaction_id, amount FROM transactions WHERE amount>20.5

Response:
123|20.5

Query:
SELECT transaction_id, amount FROM transactions  WHERE amount=20.5

Response:
No rows

So, 20.5 is really greater than 20.5 in sql database. Sometimes.

Oh yeah, it looks like balance_tracker developers were not aware of that (until today). So, it's like a bomb with timer for hundreds of websites which rely on this and other modules which use floats to store monetary-like amounts in db and are doing some sql selects which compare values stored in db against something. In our case, it was 20.563333333... stored in balance, which prevented our auto-payments system (built on top of balance_tracker+ubercart) in our big project from working properly.

How does it happen?

When you calculate some value in PHP (and you use floats in PHP, of course):

<?php  
 $total = 3 / 2; // total is now 1.5555555555... in PHP
?>

and store it in sql table in cell of type float, it is stored as 1.55555555555..
(but it shows as 1.5 when you are looking at the value using phpMyAdmin or SqlYog - which is understandable, but really adds confusion!)

That's why you need decimals. When you put 1.55555555.. in cell of type decimal, it is stored as 1.55. So, it works as most usual people expect it to work when dealing with financial stuff.
And when you use Drupal fields to store monetary amounts in your nodes, use decimal as field type, too!

UPD: another good (best, I guess - but requiring more work) solution (as suggested in comments) would be to use integer column in mysql and operate with integers in PHP/SQL, only rendering values as floats to website users.