MySQL如何进行浮点加法的数学计算? [英] How MySQL does the math calculation of floating point addition?

问题描述

我用SELECT 0.1 + 0.2;进行了测试，并使用MySQL(MariaDB)进行了查询，并返回了正确的答案

I tested with SELECT 0.1 + 0.2;, queried with MySQL (MariaDB), and it returned the right answer

MariaDB [(none)]> SELECT 0.1 + 0.2;
+-----------+
| 0.1 + 0.2 |
+-----------+
|       0.3 |
+-----------+
1 row in set (0.000 sec)

由于 IEEE 754 所述，大多数编程语言中的浮点计算都不准确，此处.

Floating point calculation is inaccurate in most programming languages because of IEEE 754 as explained here.

MySQL如何进行浮点计算以使其返回正确答案?

How MySQL does the floating point calculation that makes it return the right answer?

推荐答案

我知道SQL 92是旧标准，但是我敢肯定，在较新的SQL标准版本中这不会更改.

I know SQL 92 is old standard but iám pretty sure this is not changed in the newer SQL standard versions.

SQL 92定义

73)第6.12节，"<numeric value expression>":数据类型时 加法的两个操作数.减法，乘法 或除法运算符为精确数字，即 结果是实施定义的."*

73)Subclause 6.12, "<numeric value expression>": When the data type of both operands of the addition. subtraction, multiplication, or division operator is exact numeric, the precision of the result is implementation-defined."*

75)第6.12节，"<numeric value expression>":当数据 算术运算符的任一操作数的类型为近似 数字，结果的精度为实现定义."*

75)Subclause 6.12, "<numeric value expression>": When the data type of either operand of an arithmetic operator is approximate numeric, the precision of the result is implementation-defined."*

问题是:查询SELECT 0.1 + 0.2中的0.1和0.2是近似值还是精确值?
答案是:您也不知道数据库也不知道.
因此，数据库将运行为MySQL和MariaDB引擎定义的实现，并将此接缝处理为DECIMAL(1,1)数据类型

The question is: 0.1 and 0.2 in the query SELECT 0.1 + 0.2 a approximate or is it exact?
The answer is: you don't know also the database can't know.
So the database will run the implemention defined for MySQL and MariaDB engines this seams to be handled as DECIMAL(1,1) datatypes

为什么尼克的答案会返回正确的值或带有表定义的期望值

Why does Nick's answer return the correct values or expected ones with a table definition

SQL 92也定义了

SQL 92 also defines

隐式类型转换可以在表达式中进行，提取操作-
指令，单行选择操作，插入，删除和更新.
可以使用CAST
来指定显式类型转换 运算符.

Implicit type conversion can occur in expressions, fetch opera-
tions, single row select operations, inserts, deletes, and updates.
Explicit type conversions can be specified by the use of the CAST
operator.

尼克通过定义表中的数据类型来做到这一点.

Which Nick has done by defining the datatype in the table.

编辑此答案是因为我今天在MySQL手册中找到了一些东西.

查询

SELECT (0.1 + 0.2) = 0.3

MySQL中1的结果，这意味着MySQL使用精确的数值计算并使用精密数学(如果可能). 因此，MySQL确实知道0.1，0.2和0.3是此处的精确数据类型，需要计算精确数据，就像我在进行此编辑之前所期望的那样.

Results into 1 in MySQL which means MySQL uses exact numeric calculation and uses Precision Math where possible. So the MySQL does know that 0.1, 0.2 and 0.3 are exact datatypes here and needs to calculate exact, like i was expecting before this edit.

含义查询

SELECT (0.1 + 0.2) = 0.3 

或多或少像

SELECT CAST((0.1 + 0.2) AS DECIMAL(1, 1)) = CAST((0.3) AS DECIMAL(1, 1));

这篇关于MySQL如何进行浮点加法的数学计算?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！