Mysql float是怎么回事? [英] Whats going on with Mysql float?

问题描述

这与答案有关.我了解不建议使用浮标来实现高精度.但经过以下结果后，我并没有完全了解 FLOAT 的情况，

This is somewhat related to the answer. I understand the float is not suggested for high precision. But I don't exactly get what's going on with FLOAT after going through the below results,

观察1

以下2个查询返回的值将返回不同的结果，

The value returned by the below 2 queries returns different results,

SELECT MY_FLOAT_COL FROM MY_TABLE;

SELECT MY_FLOAT_COL*1 FROM MY_TABLE;

可能的解释是上面链接中的引用，它表示浮点数是近似值，而问题是数据类型.

The possible explanation is the reference in the above link, which says the float is approximate value and the problem is the datatype.

观察2

查询

SELECT MY_FLOAT_COL*1 FROM MY_TABLE;

SELECT MY_FLOAT_COL FROM MY_TABLE;

当我使用Squirrel SQL(Java)执行时返回相同的结果，但是当通过Heidi SQL(Object Pascal)和Python脚本执行时返回不同的结果.因此，得出的结论是问题出在客户身上.

returns the same result when I execute using Squirrel SQL(Java), but returns different results when executed through Heidi SQL(Object Pascal) and Python script. So this leads to a conclusion that the problem is with the client.

Mysql的Float数据类型到底发生了什么.我试图理解以上2个观察结果.

What exactly is going on with the Float datatype of Mysql. I am trying to make sense of above 2 observations.

编辑:我更关心我的观察2 .我可以从 Observation 1 中了解一些信息，但到目前为止，后者仍然是个谜.

I care more about my Observation 2. I am able to make some sense out of my Observation 1 but the latter is mysterious as of now.

推荐答案

发生的事情可以通过对Barmar的答案稍加修改来解释，而我对此有以下解释:

What is happening can be explained with a slight modification to Barmar's answer which I have below:

drop table if exists my_table2;
CREATE TABLE `my_table2` (
  `my_float_col` float(18,16), -- FLOAT(M,D)
  `my_double_col` double(18,16)
);
INSERT INTO my_table2 (my_float_col) values (1.2355); -- 1.2354999780654907
UPDATE my_table2 SET my_double_col = my_float_col;
SELECT my_float_col, my_double_col, my_float_col * 1 FROM my_table2;
+--------------------+--------------------+--------------------+
| my_float_col       | my_double_col      | my_float_col * 1   |
+--------------------+--------------------+--------------------+
| 1.2354999780654907 | 1.2354999780654907 | 1.2354999780654907 |
+--------------------+--------------------+--------------------+

与我的一样，Barmar的float(第1列)也是C float数据类型.但是由于他未指定精度(M,D)，所以C中的内部实现仍将其固定为C浮点数，我们具有相同的值，但事实在Barmar中丢失了.只是他的显示宽度四舍五入.请注意我在下面创建的可以显示该功能的函数.

The float (column 1) for Barmar is a C float datatype just as mine is. But because he did not specify the precision (M,D), the internal implementation in C still pegs it as a C float, we have the same value, but the fact is lost in Barmar's. It is just that his display width rounds it up. Note the function I created below that can show that.

并查看这个幽默的MySQL手册页，标题为 FLOAT和DOUBLE数据类型表示，我认为对任何研究它的人来说都是很清楚的.因为毕竟我们还是会成为数学高手:

And check out this humorous MySQL Manual page entitled FLOAT and DOUBLE Data Type Representation and I think it will be very clear to anyone delving into it. Because we would, afterall, be math-heads anyway:

现在让我们从"MySQL参考手册"中了解这个故事. 离开.

Let us now take up the story from where the MySQL Reference Manual leaves off.

下面的讨论集中在没有显示的情况下 给出了宽度和小数.这意味着FLOAT存储为 不管C类型的float是什么，并且存储REAL或DOUBLE [PRECISION] 就像C类型的double一样.字段长度由 MySQL代码.

The following discussion concentrates on the case where no display width and decimals are given. This means that FLOAT is stored as whatever the C type float is and REAL or DOUBLE [PRECISION] is stored as whatever the C type double is. The field length is selected by the MySQL code.

因此，尽管Barmar的输出为第一列，但它从未作为1.2355出现在上面，并且上面的手册页巧妙而幽默地描述了人们经历的令人头疼的事情.

So despite Barmar's first column of output, it was never in there as a 1.2355, and the above manual page cleverly and humorously describes the head-banging that people have gone through.

有帮助的fcn:

DROP FUNCTION IF EXISTS returnFloatFromDec;
DELIMITER $$
CREATE FUNCTION returnFloatFromDec(n DECIMAL(18,16))
returns FLOAT
BEGIN
    DECLARE theRet FLOAT;
    SET theRet=n; -- note that CAST can't take a FLOAT
    return(theRet);
END;$$
DELIMITER ;

这篇关于Mysql float是怎么回事?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！