What are the rules used by MySQL to display float values?

Question

I store the values ​​in a column defined as a float. When I read these values ​​from the database, they sometimes differ a lot more from the original values ​​than I would expect (I know that the float does not preserve the exact values, please take a look at the example to understand what I mean).

Here is my test case:

drop table if exists float_test; create table float_test (value1 float(8, 3), value2 float); insert into float_test values (11743.85, 11743.85); select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt; 

Selection Results:

 mysql> select mt.*, round(mt.value1, 6), round(mt.value2, 6) from float_test mt; +-----------+---------+---------------------+---------------------+ | value1 | value2 | round(mt.value1, 6) | round(mt.value2, 6) | +-----------+---------+---------------------+---------------------+ | 11743.850 | 11743.8 | 11743.849609 | 11743.849609 | +-----------+---------+---------------------+---------------------+ 1 row in set (0.01 sec) 

As you can see, choosing value2 leads to 11743.8, while choosing round (value2, 6) leads to a value that is much closer to the one I originally put in. Also if you

 mysql> select * from float_test mt where value1 = value2; +-----------+---------+ | value1 | value2 | +-----------+---------+ | 11743.850 | 11743.8 | +-----------+---------+ 1 row in set (0.00 sec) 

you can see that the values ​​stored in values ​​1 and 2 are actually equal. So, I think it is just a question of how the results are displayed. Looking through the MySQL documentation, I could not find any rules that say float values ​​are automatically rounded for display.
So now I have towing questions:
1. What rules do mysql use to display float values?
2. Is there a way (for example, some configuration parameters) that I can get a value that is not rounded to the first decimal place without changing the definition of my table and without changing my select statement?

I tested this on mysql 4.0, 5.0 and 5.1.

Thanks,
Stephen

Answer 1

This does not fully answer my question, but solved the problem for me, as I use jdbc to connect to the database:
You get the desired results when using PreparedStatements instead of regular expressions.
Code:

 import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCTest { private static final String URL = "jdbc:mysql://sp101ap0006:3306/lvs_server_38080"; private static final String USER = "user"; private static final String PASSWD = "abc"; private static void executeQuery(Connection connection) throws SQLException { Statement statement = connection.createStatement(); ResultSet queryResult = statement.executeQuery("select * from float_test"); queryResult.first(); System.out.println(queryResult.getFloat(1) + " - " + queryResult.getFloat(2)); } private static void executePreparedQuery(Connection connection) throws SQLException { PreparedStatement statement = connection.prepareStatement("select * from float_test"); ResultSet queryResult = statement.executeQuery(); queryResult.first(); System.out.println(queryResult.getFloat(1) + " - " + queryResult.getFloat(2)); } public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); Connection connection = DriverManager.getConnection(URL, USER, PASSWD); executeQuery(connection); executePreparedQuery(connection); } catch (Exception e) { e.printStackTrace(); } } } 

gives the following results:

 11743.85 - 11743.8 11743.85 - 11743.85 

Answer 2

If you want to maintain accuracy in 2 decimal places, you probably should explicitly create a field like float (M, D), where D = 2 (precision) and M = the desired number of digits in total.

For example, defining a field as float (8.2) will most likely give what you want.

It is important to remember that float and double are used to store approximate values ​​in MySQL ... Perhaps you should use the DECIMAL data type ... for example, DECIMAL (8,3).

Take care.