MySQL - Playing with Data types

Try this on your MySQL server.
1.  create table money(trx_id int, trx_value float)engine=innodb;
2.  insert into money values(1,0.99);
3.  mysql> select * from money where trx_value=0.99;
Empty set (0.00 sec)

Now how did that happen?

ok now try this,
1.  create table money2(trx_id int, trx_value dec(4,3))engine=innodb;
2. insert into money2 values(1,0.99);
3. mysql> select * from money2 where trx_value=0.99;
+--------+-----------+
| trx_id | trx_value |
+--------+-----------+
|      1 |     0.990 |
+--------+-----------+
1 row in set (0.00 sec)

Any answers still?

Ok now lets go and extra step and try this,

mysql> select * from money where trx_value between 0.99-0.005 and 0.99+0.005;
+--------+-----------+
| trx_id | trx_value |
+--------+-----------+
|      1 |      0.99 |
+--------+-----------+
1 row in set (0.00 sec)

The explanation is pretty simple, float is an approximate data type and it doesn't store precise values. On the other hand decimal is a exact value data type. On the flip side float takes less storage where as the exact value data type takes more space. So the trick is to remember that you should never use float if you are using the column to store monetary value - You might unknowingly induce teeming and lading!