MySQL用于显示浮点值的规则是什么？

I'm storing values in a column defined as float. When I read back these values from the database they sometimes differ much more from the original values than I would expect (I know that float doesn't store exact values, please take a look at the example to see what I mean).

我将值存储在定义为float的列中。当我从数据库中读回这些值时,它们有时与原始值的差异远远超出我的预期(我知道浮点数不存储确切的值,请查看示例以了解我的意思)。

Here's 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;

The results of the select are:

选择的结果是:

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, selecting value2 results in 11743.8 whereas selecting round(value2, 6) results in a value that is much closer to the one I originally put in. Also if you

如您所见,选择value2会产生11743.8,而选择round(value2,6)会产生一个更接近我最初输入的值。如果你

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 value1 and value2 are actually equal. Thus I think it's just a matter of how the results are displayed. Looking through the MySQL documentation I could not find any rules that say how float values are being rounded automatically for display.
So now I have tow questions:
1. What are the rules mysql uses for displaying float values?
2. Is there a way (e.g. some configuration options) I can retrieve the value that's not rounded to the first decimal place without modifying my table definition and without changing my select statement?

你可以看到存储在value1和value2中的值实际上是相等的。因此,我认为这只是结果如何显示的问题。通过MySQL文档,我找不到任何规则,说明浮动值如何自动舍入显示。所以现在我有两个问题:1。mysql用于显示浮点值的规则是什么? 2.有没有办法(例如一些配置选项)我可以检索未舍入到第一个小数位的值而不修改我的表定义而不更改我的select语句?

I've tested this on mysql 4.0, 5.0 and 5.1.

我在mysql 4.0,5.0和5.1上测试了这个。

Thank you,
Stefan

谢谢Stefan

2 个解决方案

#1

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

如果要保持2个小数点的精度,则应该明确地将字段创建为float(M,D),其中D = 2(精度),M =所需的总位数。

Defining the field as float(8,2) for example, would likely yield what you wanted.

例如,将字段定义为float(8,2)可能会产生您想要的结果。

It's important to keep in mind that float and double are used to store approximate values in MySQL... You may be better off using the DECIMAL data type... for example, DECIMAL(8,3).

重要的是要记住,float和double用于在MySQL中存储近似值...您可能最好使用DECIMAL数据类型...例如,DECIMAL(8,3)。

Take care.

#2

This doesn't completely answer my question but solved the problem for me as I'm using jdbc to connect to the database:
You get the desired results when using PreparedStatements instead of ordinary Statements.
Code:

这并没有完全回答我的问题但是我解决了这个问题,因为我正在使用jdbc连接到数据库:使用PreparedStatements而不是普通语句时,您可以获得所需的结果。码:

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

#1