The float type of mysql is very strange. The two tables with different values have a lot of data, but there are several very strange ones. The fields are of the float type and the two values are the same.
Payment Value = 299.7
Feiyong value = 299.7
Query statement: feiyong> 0 and Payment> 0 and Payment
In this case, the query is still available, and only a few items have this problem, and others are normal.
Reply to discussion (solution)
The floating point number indicates a large number, and some precision is lost.
Therefore, when compared, the absolute values of two numbers are considered equal when the difference is smaller than a certain decimal number.
Or use the round to obtain the valid number before comparison.
If your application requires precise DECIMAL places (such as finance), use the DECIMAL type.
This is quite normal. after all, there is no way for float data to ensure data accuracy.
The floating point number indicates a large number, and some precision is lost.
Therefore, when compared, the absolute values of two numbers are considered equal when the difference is smaller than a certain decimal number.
Or use the round to obtain the valid number before comparison.
If your application requires precise DECIMAL places (such as finance), use the DECIMAL type.
Now, how can I change the query statement to make the query normal? thank you.
We recommend that you change the data type to DECIMAL.
select ..., abs(Payment-feiyong) as aform ......where feiyong>0 and Payment>0having a > 1e-5
select ..., (Payment-feiyong) as aform ......where feiyong>0 and Payment>0having a > 1e-5