MySQL FLOOR函数出乎意料的结果 [英] MySQL FLOOR function unexpected results
问题描述
CREATE TABLE table_name (col_a double(10,2), col_b double(10,2), col_c double(10,2));
INSERT INTO table_name VALUES(36.3, 0, 6.3);
查询
SELECT FLOOR(36.3- 0 -6.3), FLOOR(col_a - col_b - col_c) AS calc, col_a, col_b, col_c
FROM table_name LIMIT 1;
结果
第一个选择的值=> FLOOR(36.3- 0 -6.3)导致30.
first selected value => FLOOR(36.3- 0 -6.3) result in 30.
第二选择值=> FLOOR(col_a - col_b - col_c)等于FLOOR(36.3- 0 -6.3)导致29，但是我期望30
second selected value => FLOOR(col_a - col_b - col_c) which is equals to FLOOR(36.3- 0 -6.3) result in 29 but i am expecting 30
为什么这些选择会获得两个不同的值?
Why these selects getting two different values?
推荐答案
在使用double或float类型时，这是MySQL中的一个已知问题，它们在内部存储的方式与我们看到的不完全相同.
This is a known problem in MySQL when using the double or float type, which are not stored internally exactly as we see them.
如果您阅读了 MySQL文档，您会发现建议的解决方法，该方法是使用decimal而不是double.您可以在下面的小提琴中看到，使用decimal(10,2)作为列类型时，所有功能都按预期工作:
If you read the MySQL documentation, you will find a suggested workaround which is to use decimal instead of double. You can see in the following Fiddle that all is working as expected when using decimal(10,2) as your column types:
这篇关于MySQL FLOOR函数出乎意料的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
