12.21.5 Precision Math Examples — MySQL 5.7

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

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

Пример 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. То есть для двух чисел 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 только для аргумента с плавающей точкой. Для аргументов точного типа результат также является типом с точным значением.