MySQL的函数和运算符 - 精确数学 - 精确计算实例

林员外聊编程 2021-10-12

1665

精确计算实例

本部分提供一些在 MySQL 中显示精确计算查询结果的示例。

示例1. 在可能的情况下，使用数字时会使用它们的确切值：

mysql> SELECT (.1 + .2) = .3;
+----------------+
| (.1 + .2) = .3 |
+----------------+
|              1 |
+----------------+

对于浮点值，结果是不精确的：

mysql> SELECT (.1E0 + .2E0) = .3E0;
+----------------------+
| (.1E0 + .2E0) = .3E0 |
+----------------------+
|                    0 |
+----------------------+

另一种查看精确值和近似值处理差异的方法是对小数多次求和。考虑下面的存储过程，它将 .0001 相加 1000 次。

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE d DECIMAL(10,4) DEFAULT 0;
  DECLARE f FLOAT DEFAULT 0;
  WHILE i < 10000 DO
    SET d = d + .0001;
    SET f = f + .0001E0;
    SET i = i + 1;
  END WHILE;
  SELECT d, f;
END;

对 d 和 f 的求和逻辑上应该是 1，但这只对小数计算是正确的。浮点运算引入了小的误差：

+--------+------------------+
| d      | f                |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+

示例2。按照标准 SQL 所需的标度执行乘法。即，对于具有标度 S1 和 S2 的两个数字 X1 和 X2，其结果的标度为 S1 + S2：

mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001    |
+-----------+

示例3。精确数值的舍入行为是定义良好的：

舍入行为(例如，使用 ROUND() 函数)独立于底层C库的实现，这意味着结果在不同平台之间是一致的。

● 对精确值列(DECIMAL 和整数)和精确值数的舍入使用“四舍五入”规则。小数部分大于或等于 .5 的值将四舍五入到远离 0 的最接近的整数，如下所示：

mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3          | -3          |
+------------+-------------+

● 浮点数四舍五入使用C库，在许多系统上使用“四舍五入到最接近偶数”规则。小数部分恰好在两个整数中间的值被舍入为最接近的偶数：

mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
|            2 |            -2 |
+--------------+---------------+

示例4。在严格模式下，插入超出列范围的值会导致错误，而不是截断为合法值。

当 MySQL 不是在严格模式下运行时，会出现截断为合法值的情况：

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.00 sec)


mysql> SELECT i FROM t;
+------+
| i    |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

然而，如果严格模式生效，则会发生错误：

mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1


mysql> SELECT i FROM t;
Empty set (0.00 sec)

示例5：在严格模式下，如果设置了 ERROR_FOR_DIVISION_BY_ZERO，除零将导致错误，而不是返回结果 NULL。

在非严格模式下，除零的结果是 NULL：

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.01 sec)


mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.00 sec)


mysql> SELECT i FROM t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

但是，如果正确的 SQL 模式生效，除以 0 会返回错误：

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)


mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)


mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0


mysql> SELECT i FROM t;
Empty set (0.01 sec)

示例6。精确值字面量被计算为精确值。

近似值字面量按照浮点数计算，但精确值字面量按照 DECIMAL 处理：

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0


mysql> DESCRIBE t;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a     | decimal(2,1) unsigned | NO   |     | 0.0     |       |
| b     | double                | NO   |     | 0       |       |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

示例7。如果聚合函数的参数是精确数字类型，则结果也是精确数字类型，其标度至少与参数的规模相同。

考虑以下语句：

mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;

对于浮点参数，结果是双精度类型。对于精确值类型参数，结果也是精确值类型：

mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| AVG(i) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(d) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(f) | double        | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

官方网址：

https://dev.mysql.com/doc/refman/8.0/en/precision-math-examples.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 精确数学 - 精确计算实例

评论