Usually, I store number with decimal point in float, but now I run into a problem – the rounding error. This really doesn’t good in financial data like money, for example, in float datatype, when we calculate 10000000-25, it will result in 9.99998e+006, when we convert to decimal (I do it in PHP), it will result in 9999980. Not good!

But that was a big mistake I made, if you just take the data, let’s say

SELECT floatdatafield FROM whatevertable

you will get this 9.99998e+006 value, and no matter what you do in PHP, you won’t be able to get the exact value, 9999975. So for the solution, we need to use the SQL function, ROUND(). So instead of just select everything, we can do this

SELECT ROUND(floatdatafield,2) AS floatdatafield FROM whatevertable

This will round it to have maximum 2 numbers digit after the decimal point and it will return 9999975.00, exactly what we need.

Another solution is to use decimal data type instead, or by defining the number of digits before and after decimal point of the float.