The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Каталог документации / Раздел "Базы данных, SQL" / Оглавление документа
Глава 1. Точная математика

MySQL 5.1 обеспечивает поддержку для точной математики: числовая обработка значения, которая приводит к чрезвычайно точным результатам, и высокой степени контроль над недопустимыми значениями. Точность основана на этих двух свойствах:

Эти свойства имеют несколько импликаций для числовых операций:

Важный результат этих свойств: MySQL 5.1 обеспечивает высокую степень согласия со стандартом SQL.

Следующее обсуждение покрывает несколько аспектов того, как работает высокая точность (включая возможные несовместимости со старыми прикладными программами). В конце есть некоторые примеры.

1.1. Типы числовых значений

Контекст математической точности для операций с точным значением включает типы данных с точным значением (DECIMAL и целочисленные типы) и числовые литералы с точным значением. Типы данных с приблизительным значением и числовые литералы все еще обработаны как числа с плавающей запятой.

Числовые литералы с точным значением имеют целочисленную, дробную части или обе. Они могут быть со знаком или без него. Примеры: 1, .2, 3.4, -5, -6.78, +9.10.

Числовые литералы с приблизительным значением представляются в экспоненциальном формате с мантиссой и экспонентой. Любая из частей или обе могут иметь знак. Примеры: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Два числа, которые выглядят одинаково, могут не быть оба точными или приблизительными. Например, 2.34 представляет собой число с точным значением (с фиксированной запятой), в то время как 2.34E0 задает число с приблизительным значением (с плавающей запятой).

Тип данных DECIMAL является типом с фиксированной запятой, и вычисления точны. В MySQL тип DECIMAL имеет несколько синонимов: NUMERIC, DEC, FIXED. Целочисленные типы также типы с точным значением.

Типы данных FLOAT и DOUBLE являются типами с плавающей запятой, и вычисления приблизительны. В MySQL типы, которые являются синонимичными с FLOAT или DOUBLE, это DOUBLE PRECISION и REAL.

1.2. Изменения типа данных DECIMAL

Этот раздел обсуждает характеристики типа данных DECIMAL (и синонимов) в MySQL 5.1, со специфическим отношением к следующим темам

Возможные несовместимости с прикладными программами, которые написаны для старых версий MySQL, отмечены в этом разделе.

Синтаксис объявления для столбца DECIMAL: DECIMAL(M,D). Диапазоны значений для параметров в MySQL 5.1 следующие:

Максимальное значение 65 для M означает, что вычисления на значениях DECIMAL точны до 65 цифр. Это ограничение точности в 65 цифр также применяется к числовым литералам с точным значением, так что оно задает максимальный диапазон таких литералов. В старых версиях MySQL десятичные значения могли иметь до 254 цифр. Однако, вычисления были выполнены, используя числа с плавающей запятой и таким образом были приблизительны, не точны.

Значения для столбцов DECIMAL в MySQL 5.1 сохранены, используя двоичный формат, который упаковывает девять десятичных цифр в четыре байта. Требования к памяти для целочисленных и дробных частей каждого значения определены отдельно. Каждые девять цифр требуют четырех байт, и любые цифры сверх этого требуют некоторой доли четырех байтов. Например, DECIMAL(18,9) имеет девять цифр с обеих сторон десятичной точки, так что целочисленная и дробная части требуют четырех байтов каждая. Столбец DECIMAL(20,10) имеет по десять цифр с обеих сторон десятичной точки. Каждая часть требует четырех байтов для девяти из цифр и одного байта для остающейся цифры.

Память, требуемая для остающихся цифр, показана в следующей таблице:

Остающиеся цифры Число байтов
00
11
21
32
42
53
63
74
84
94

В отличие от старых версий MySQL (до 5.0.3), столбцы DECIMAL в MySQL 5.1 не сохраняют символ + или цифры 0 в начале значения. Если Вы вставляете +0003.1 в столбец DECIMAL(5,1), это сохранено как 3.1. Прикладные программы, которые полагаются на старое поведение, должны измениться, чтобы обработать это изменение.

Столбцы DECIMAL в MySQL 5.1 не позволяют значения больше, чем диапазон, подразумеваемый по определению столбца. Например, столбец DECIMAL(3,0) поддерживает диапазон от -999 до 999. А столбец DECIMAL(M,D) позволяет M цифр налево от десятичной точки. Это не совместимо с прикладными программами, полагающимися на старые версии MySQL, которые разрешали сохранять дополнительную цифру вместо знака +.

SQL-стандарт требует, чтобы точность NUMERIC(M,D) была точно M цифр. Для DECIMAL(M,D) стандарт требует точности по крайней мере M цифр, но позволяет больше. В MySQL DECIMAL(M,D) и NUMERIC(M,D) то же самое, и оба типа имеют точность ровно M цифр.

1.3. Обработка выражений

Числа с точным значением используются как даны всякий раз, когда возможно. Например, числа в сравнениях используются точно как даны, без изменения в значении. В строгом SQL-режиме для INSERT в столбец с точным типом данных (DECIMAL или целое число), значение вставлено с точным значением, если оно внутри диапазона столбца. Когда значение получено, оно должно быть таким же как то, что было вставлено. Без строгого режима допустимо усечение для INSERT.

Обработка числового выражения зависит от значений, которое выражение содержит:

Если числовое выражение содержит любые строки, они преобразованы в значения с плавающей запятой двойной точности, и выражения приблизительны.

На вставки в числовые столбцы воздействует SQL-режим, который управляется переменной системы sql_mode. Следующее обсуждение упоминает строгий режим (выбранный значениями режима STRICT_ALL_TABLES или STRICT_TRANS_TABLES) и ERROR_FOR_DIVISION_BY_ZERO. Чтобы включить все ограничения, Вы можете просто использовать режим TRADITIONAL, который включает строгие значения режима и ERROR_FOR_DIVISION_BY_ZERO:

mysql> SET sql_mode='TRADITIONAL';

Если число вставлено в столбец точного типа (DECIMAL или целое число), оно вставлено с точным значением, если находится внутри диапазона столбца.

Если значение имеет слишком много цифр в дробной части, происходит округление, и будет сгенерировано предупреждение. Округление выполнено как описано в разделе " 1.4. Поведение округления".

Если значение имеет слишком много цифр в целочисленной части, оно слишком большое и обработано следующим образом:

По умолчанию, деление на нуль производит результат NULL и никакого предупреждения. С включенным режимом SQL ERROR_FOR_DIVISION_BY_ZERO MySQL обрабатывает деление на нуль по-другому:

Другими словами, вставки и модификации, включающие выражения, которые выполняют деление на нуль, могут обрабатываться как ошибки, но это требует ERROR_FOR_DIVISION_BY_ZERO в дополнение к строгому режиму.

Предположим, что мы имеем эту инструкцию:

INSERT INTO t SET i = 1/0;

Это что случается для комбинации строгого режима и ERROR_FOR_DIVISION_BY_ZERO:

Значение sql_mode Результат
'' (значение по умолчанию)Никаких предупреждений и ошибок: i установлен в NULL.
strictНикаких предупреждений и ошибок: i установлен в NULL.
ERROR_FOR_DIVISION_BY_ZEROНикаких ошибок, но есть предупреждение: i установлен в NULL.
strict,ERROR_FOR_DIVISION_BY_ZEROОшибка: никакая строка не вставлена.

Для вставок строк в числовые столбцы, преобразование из строки в число обработано следующим образом, если строка имеет нечисловое содержание:

1.4. Поведение округления

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

Функция ROUND() работает по-разному в зависимости от того, является ли параметр точным или приблизительным числом:

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

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          | 2            |
+------------+--------------+

Для вставок в DECIMAL или целочисленный столбец, если адресат представляет собой точный тип данных, используется метод "округления половины" независимо от того, является ли значение, которое будет вставлено, точным или приблизительным:

mysql> CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t VALUES(2.5),(2.5E0);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 2

mysql> SELECT d FROM t;
+------+
| d    |
+------+
| 3    |
| 3    |
+------+

1.5. Примеры математической точности

Этот раздел обеспечивает некоторые примеры, которые показывают запросы с математической точностью в MySQL 5.1.

Пример 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, что означает, что результаты непротиворечивы на разных платформах.

Округление для столбцов с точным значением использует округление половины, как показано здесь:

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. До MySQL 5.0.3 литералы с точным значением и с приблизительным значением преобразованы в значения с плавающей запятой двойной точности:

mysql> SELECT VERSION();
+------------+
|  VERSION() |
+------------+
| 4.1.18-log |
+------------+
1 row in set (0.01 sec)

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

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

Начиная с MySQL 5.0.3, литерал с приблизительным значением все еще преобразован в значение с плавающей запятой, но литерал с точным значением обработан как DECIMAL:

mysql> SELECT VERSION();
+-----------------+
|       VERSION() |
+-----------------+
| 5.1.6-alpha-log |
+-----------------+
1 row in set (0.11 sec)

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 5.0.3:

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

Результат двойной точности, независимо от типа параметра. А вот результаты в MySQL 5.0.3 и выше:

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

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




Спонсоры:
Inferno Solutions
Hosting by Hoster.ru
Хостинг:

Закладки на сайте
Проследить за страницей
Created 1996-2021 by Maxim Chirkov
Добавить, Поддержать, Вебмастеру