About MySQL with a storage type of float, loss of precision when using JDBC queries, or auto rounding issues

A. mysql table structure:

The data type of the Member_price column is float (20,2) (Note: Maximum 20-bit floating-point number, 2-bit precision )

Insert Record:

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 1 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.8745120', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 2 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.8743120', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 3 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.874', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 4 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.879', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 5 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.871', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 6 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.87', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 7 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.8', ' 1450078966586 ');

INSERT into ' member_price ' (' id ', ' data_type ', ' month ', ' member_count ', ' member_price ', ' Create_at ') VALUES (' 8 ', ' 1 ', ' 20 15-11 ', ' 5864 ', '1765910.1', ' 1450078966586 ');

Inquire:

To add a condition query:

Attention:

Float in MySQL: This example illustrates float (20,2), although the set precision is 2 bits, but three bits are saved each time the value is inserted, but the three digits saved are imprecise, as shown above.

When you query

1. If the insertion is a decimal, but the precision is two bits, the number of inserts is not found by the equality (except the first decimal is 0 and 5), because MySQL will be complete to three bits, the final saving accuracy is not accurate

2. If the insertion is two decimal places, and the accuracy is two bits, then the number of inserts by the same will not be sure to find out

3. For queries of three decimal places, ibid.

So in MySQL numeric type, float is imprecise, try to avoid using, can use double or decimal, the difference is double is floating point calculation, decimal is fixed point calculation, will get more accurate data.

Two. Querying the float type data in JDBC

Java code:

1  Public classMain {2      Public Static voidMain (string[] args) {3Connection Connection =NULL;4PreparedStatement PreparedStatement =NULL;5ResultSet ResultSet =NULL;6         Try {7Class.forName ("Com.mysql.jdbc.Driver");8Connection = Drivermanager.getconnection ("Jdbc:mysql://127.0.0.1:3306/user", "root", "123456a");9String sql = "SELECT * from Member_price WHERE ' month ' = ' 2015-11 ' and data_type = 1";TenPreparedStatement =connection.preparestatement (SQL); OneResultSet =preparedstatement.executequery (); A              while(Resultset.next ()) { -System.out.println ("string:" + resultset.getstring ("Member_price"));//Get by String type -System.out.println ("float:" + resultset.getfloat ("Member_price"));//Get by Long type theSystem.out.println ("============>next"); -             } -}Catch(Exception e) { - e.printstacktrace (); +}finally { -             Try { + resultset.close (); A preparedstatement.close (); at connection.close (); -}Catch(SQLException e) { - e.printstacktrace (); -             } -         } -  in     } -}

View Code (code is not very canonical, just for demonstration purposes)

Intercept part of the output:

string:1765910.88
float:1765910.9
============>next
string:1765910.75
float:1765910.8
============>next
string:1765910.12
float:1765910.1
============>next
string:1765910.25
float:1765910.2
============>next
string:1765910.00
float:1765910.0
============>next
string:1765910.38
float:1765910.4
============>next
string:1765910.50
float:1765910.5
============>next
string:1765910.62
float:1765910.6
============>next

With the up-change test, you can find:

1. The accuracy in the database is set to 2 bits, and the value obtained by resultset.getstring () is two bits (consistent with MySQL), but the value obtained by Resultset.getlong () is a (this number is rounded result ( But 25 the last to get is 2, do not know why))

2. Use Format (member_price,10), TRUNCATE (member_price,10), ROUND (member_price,10) and other functions to query if through Resultset.getlong () To get the value of the query, and finally get only one decimal, you need to use resultset.getstring () to get the exact query results .

Conclusion: It is best not to use the float type in MySQL, for the query of floating-point numbers, it is best to use resultset.getstring () in JDBC to get the result value of the query, Resultset.getlong () will only get one decimal place

If there is a mistake, please criticize ^_^

About MySQL with a storage type of float, loss of precision when using JDBC queries, or auto rounding issues