The where statement of Mysqlfloat type determines equality _ MySQL

The where statement of Mysqlfloat type determines equality. bitsCN.com

When I debugged the program today, I found that the data of the float type in the Mysql where statement is equal, and no records can be retrieved. After trying for half a day, I found that mysql could not retrieve records for such SELECT RecordTime FROM test WHERE ziduan = 98.1. Baidu found that the floating point value was stored as 10.27999973297119140625 on the computer. this is because of the error between decimal and binary. it seems that the error is also in the seventh place. therefore, small data can be retained for seven or eight bits. this problem is common in most computer languages because not all floating point values can be saved with accurate precision.

Later I asked Geng Bo, and gave me a method, which is quite helpful, as shown below:

SELECT RecordTime FROM test where abs (ziduan-98.1) <1e-5

The absolute value function is used to determine the equality of float type values, which is highly precise and convenient!

There are several ways to deal with this problem on the internet. I don't feel very good! Such as what is greater than one value, and then less than another value. this is a method, and some rely on adding fields, these methods are not very good!

Solution:

Solution:

The first is to use the interval:

Select * from Olympus _result where result> 10.27 and result <10.29;

The second step is to design a float-type field, instead of a float-type field, instead of a float-type field. Instead, an int-type + decimal point is used to mark this int-type field, that is, result = 10.28 is saved in the database as result = 10.28, precision = 2

Disadvantage: However, this sorting method is hard to solve.

Method 3: Create one character field at design:

For example, alter table Olympus _result add cresul varchar (32 );

When an update is inserted, quotation marks are added.

> Update Olympus _result set cresult = '10. 28' where id = 1;

In this way.

Query:

Mysql> select * from Olympus _result where cresult = '10. 28 ';

+ ---- + --------- + ------- + -------- + ----------- + --------- +

| Id | user_id | types | result | times | cresult |

+ ---- + --------- + ------- + -------- + ----------- + --------- +

| 1 | 243 | 1 | 10.28 | 143243242 | 10.28 |

+ ---- + --------- + ------- + -------- + ----------- + --------- +

Sorting can be performed by result.

Every day is improving. there is still a big gap with my colleagues. come on! The future will be good!

From ykm0722's column

BitsCN.com