14.24.5 Precision Math Examples — MySQL 8.4

14.24.5 Примеры точных математических вычислений

В этом разделе приведены примеры, демонстрирующие результаты запросов точных математических вычислений в MySQL. Эти примеры иллюстрируют принципы, описанные в разделе 14.24.3 «Обработка выражений» и разделе 14.24.4 «Поведение округления».

Пример 1. Числа используются со своим точным значением, когда это возможно:

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

Для чисел с плавающей точкой результаты являются приблизительными:

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

Другой способ увидеть разницу в обработке точных и приблизительных значений — это многократно добавлять малое число к сумме. Рассмотрим следующую хранимую процедуру, которая 1000 раз добавляет .0001 к переменной.

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. То есть для двух чисел X1 и X2, имеющих масштаб S1 и S2, масштаб результата равен S1 + S2:

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

Пример 3. Поведение округления для чисел с точным значением определено однозначно:

Поведение округления (например, с функцией ROUND()) не зависит от реализации базовой библиотеки C, что означает, что результаты согласованы на разных платформах.

Округление для столбцов с точным значением (DECIMAL и целые числа) и чисел с точным значением использует правило “округление к ближайшему целому вдали от нуля”. Значение с дробной частью .5 или больше округляется в сторону от нуля до ближайшего целого, как показано здесь:
```
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:

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;

Результат — double только для аргумента с плавающей точкой. Для аргументов с точным типом результат также является типом с точным значением:

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    |       |
+--------+---------------+------+-----+---------+-------+

Результат — double только для аргумента с плавающей точкой. Для аргументов с точным типом результат также является типом с точным значением.