为什么 MySQL 的轮次浮动比预期的要多? [英] Why does MySQL round floats way more than expected?

问题描述

UPDATE some_table SET some_float_field=1919.987 WHERE id=123

SELECT * FROM some_table WHERE id=123

其中 some_float_field 是定义为浮动"的字段(没有任何特定大小值).

where some_float_field is a field defined as "float" (without any specific size values).

预期的结果值为 1919.987；相反，它四舍五入为 1919.99

The expected resulting value would be 1919.987; instead, it is rounded to 1919.99

为什么?一个 32 位(单精度)浮点数有足够的精度来正确存储它！

Why? A 32bit (single precision) float has enough precision for storing that correctly!

推荐答案

运行查询时:

SELECT * FROM some_table WHERE id = 123

您依赖于用户界面来格式化浮点数.您使用的界面使用的是两个字符而不是更多字符.毕竟，没有关于权利"的信息.要显示的号码.

You are relying on the user interface to format the floating point numbers. The interface you are using is using two characters rather than more. After all, there is no information on the "right" number to show.

您可以通过将数字格式化为字符串或小数来说服界面显示正确的数字.例如:

You can convince the interface to show the right number by formatting the number as a string or as a decimal. For instance:

select format(some_float_field, 3)

将其转换为具有三个小数位的字符串.一个警告:它还会添加您可能不想要的逗号.这也应该有效:

will convert this to a string with three decimal places. One caution: it will also add commas which you might not want. This should also work:

select cast(some_float_field as decimal(8, 3))

请注意，您可以通过执行以下操作轻松验证数据是否正确:

Note that you can readily validate that the data is correct by doing something like:

select *
from some_table
where some_float_field between 1919.987 - 0.0001 and 1919.987 + 0.0001;

请注意，您不想在浮点值上使用 =，但您已经了解这一点.

Note that you don't want to use = on floating point values, but you already understand that.

这篇关于为什么 MySQL 的轮次浮动比预期的要多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！