Chapitre 23 Mathématiques de précision

Les fonctionnalités mathématiques couvrent les types de données exactes (le type DECIMAL et les entiers), et les nombres décimaux exacts littéraux. Les types de données approximatifs sont gérés comme des nombres décimaux.

Les valeurs littérales exactes ont une partie entière ou une partie décimale, ou les deux. Elles peuvent être pourvues d'un signe. Par exemple : 1, .2, 3.4, -5, -6.78, +9.10.

Les valeurs littérales approximatives sont représentées en notation scientifique, avec une mantisse et un exposant. Les deux parties de cette représentation peuvent être pourvus d'un signe ou non. Par exemple : 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Des nombres qui se ressemblent n'ont pas forcément le même type de données, exact ou approximatif. Par exemple, 2.34 est une valeur exacte (virgule fixe), alors que 2.34E0 est une valeur approximative (virgule flottante).

Le type de données DECIMAL est un type à virgule fixe, et les calculs qui s'y rattachent sont exacts. Pour MySQL, DECIMAL a plusieurs synonymes : NUMERIC, DEC et FIXED. Les types entiers sont aussi des valeurs exactes.

Les types de données FLOAT et DOUBLE sont des types à virgule flottante, et les calculs qui s'y rattachent sont des approximations. Pour MySQL, les types synonymes de FLOAT ou DOUBLE sont DOUBLE PRECISION et REAL.

En MySQL 5.0.3, plusieurs modifications ont été apportées au type de données DECIMAL et ses synonymes :

Certaines de ces évolutions ont des incompatibilités potentielles avec les applications qui ont été écrites avec les vieilles versions de MySQL. Ces incompatibilités sont présentées dans cette section.

La syntaxe de déclaration des colonnes DECIMAL reste DECIMAL(M,D), même si l'intervale de validité des arguments a un peu changé :

La valeur maximale de 64 pour M signifie que les calculs sur des valeurs DECIMAL ont une précision de 64 chiffres. Cette limite de 64 chiffres s'applique aussi aux valeurs numériques exactes littérales, ce qui fait que la taille maximale des littéraux est différente. Avant MySQL 5.0.3, les valeurs décimales pouvaient avoir jusqu'à 254 chiffres. Cependant, les calculs étaient fait avec des virgules flottantes, et restaient approximatifs. Ce changement dans la taille maximale des nombres est aussi une source de conflit avec les anciennes versions.

Les valeurs des colonnes DECIMAL ne sont plus représentées comme des chaînes, qui requiert un octet par chiffre ou signe. A la plae, un format binaire est utilisé, et il contient 9 chiffres dans 4 octets. Cela modifie la taille de stockage des valeurs DECIMAL. Chaque multiple de 9 chiffres requiert 4 octets, et le reste requiert une fraction de 9 chiffres. Par exemple, une colonne DECIMAL(18,9) a 9 chiffres de chaque coté de la virgule, ce qui fait que la partie entière et la partie décimale demandent 4 octets chacun. Une colonne DECIMAL(20,10) dipose de 10 chiffres de chaque coté de la virgule. Cela fait 4 octets pour chaque gropue de 9 chiffres, et 1 octets pour le reste.

Le stockage requis pour le "reste" est présenté dans la table suivante :

Une conséquence du changement de chaîne en format binaire de DECIMAL est que les colonnes DECIMAL ne peuvent plus stocker le caractère ‘+’ initial ou les ‘0’ initiaux. Avant MySQL 5.0.3, si vous insériez '+0003.1' dans une colonne DECIMAL(5,1), le nombre aurait été stocké sous forme de +0003.1. Depuis MySQL 5.0.3, il est stocké sous forme de 3.1. Les applications qui exploitent le vieux comportement doivent être modifiées pour prendre en compte ce changement.

Le changemenet de format de stockage signifie que les colonnes DECIMAL ne supportent plus les extensions non standard qui permettait aux valeurs trop grandes d'être stockées. Pour les valeurs positives qui ne requierent pas de signe, MySQL permettait l'ajout d'un chiffre de plus. Par exemple, dans une colonne DECIMAL(3,0), l'intervale de validité était de -999 à 999, mais MySQL permettait le stockage de valeurs de 1000 à 9999, en utilisant l'octet de signe pour stocker les valeurs supplémentaires. Cette extension de la valeur maximale des colonnes DECIMAL n'est plus autorisée. En MySQL 5.0.3 et plus récent, une colonne DECIMAL(M,D) autorise le stockage d'au plus M−D chiffres à gauche de la virgule décimale. Cela peut engendrer des incompatibilités si une application exploitait cette tolérance de MySQL.

Le standard SQL requiert que la précision des valeurs NUMERIC(M,D) soit exactement de M. Pour DECIMAL(M,D), le standard requiert une précsion d'au moins M mais en autorise plus. Avec MySQL, DECIMAL(M,D) et NUMERIC(M,D) sont les mêmes, et les deux ont la même précision de M chiffres exactement.

Résumé des incompatibilités :

La liste suivante résume les incompatibilités qui résultent des modifications des comportements des colonnes DECIMAL. Vous pouvez l'utiliser pour vérifier votre anciennes applications et les migrer vers MySQL 5.0.3.

Avec les mathématiques de précisions, les valeurs exactes sont utilisées aussi souvent que possible. Par exemple, les comparaisons entre noms sont faîtes exactement, sans perte de valeur. En mode SQL strict, lors d'une commande INSERT dans une colonne avec un type exact, tel que DECIMAL ou entier, un nombre est inséré avec sa valeur exacte s'il est contenu dans l'intervale de validité de la colonne. Lorsqu'il est lu, sa valeur est la même que lors de l'insertion. Hors du mode strict, les arrondissements sur INSERT sont autorisés.

La gestion des expressions numériques dépends du type de valeurs qui sont manipulées :

Si une expression numérique contient des chaînes de caractères, elles sont converties en valeur décimale de précision double, et l'expression sera une approximation.

Les insertions dans les colonnes numériques sont affectées par le mode SQL, qui est contrôlé par la variable système sql_mode. Voyez see Section 1.5.2, « Sélectionner les modes SQL ». La présentation suivante mentionne le mode strict, sélectionné par les valeurs STRICT_ALL_TABLES ou STRICT_TRANS_TABLES mode values et ERROR_FOR_DIVISION_BY_ZERO. Pour désactiver ces contraintes, vous pouvez utiliser simplement le mode TRADITIONAL, qui inclut le mode strict et ERROR_FOR_DIVISION_BY_ZERO :

mysql> SET sql_mode='TRADITIONAL';

Si un nombre est inséré dans une colonne de type exact (DECIMAL ou entier), il sera inséré comme valeur exacte s'il est dans l'intervale de validité de la colonne.

Si la valeur a trop de chiffres décimaux, un arrondissement surviend et une alerte est générée. L'arrondissement se fait tel que décrit par le "Comportement d'arrondissement".

Si la valeur a trop de chiffres dans la partie entière, la valeur est alors trop grande, et elle est traitée comme ceci :

Les valeurs trop petites pour être stockées ne sont pas détectées, et aucune erreur ne survient dans ce cas : le comportement est indéfini.

Par défaut, la division par zéro produit le résultat de NULL et aucune alerte. Avec le mode SQL ERROR_FOR_DIVISION_BY_ZERO, MySQL gère les divisions par zéro différemment :

En d'autres mots, les insertions et modifications qui impliquent des divisions par zéro peuvent être traitées comme des erreurs, mais cela requiert le mode SQL ERROR_FOR_DIVISION_BY_ZERO en plus du mode strict.

Supposez que nous ayons la commande suivante :

INSERT INTO t SET i = 1/0;

Voici ce qui arrive dans différentes combinaisons du mode strict et de ERROR_FOR_DIVISION_BY_ZERO :

Pour les inserionts de lignes dans une colonne numérique, la conversion de chaîne en valeur numérique est géré comme ceci :

Cette section présente les méthodes d'arrondissement des valeurs par la fonction ROUND() et lors des insertions dans ces colonnes de type DECIMAL.

La fonction ROUND() arrondit différement les valeurs, suivant qu'elles sont exactes ou approximative :

L'exemple suivant illustre la différence de comportement entre les deux valeurs :

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

Pour les insertions dans les colonnes de type DECIMAL, la cible est une valeur exacte, ce qui fait que l'arrondissement se fait à l'entier le plus proche, indépendamment de la nature de la valeur insérée, approchée ou exacte :

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

Cette section fournit des exemples d'améliorations de la qualité des calculs mathématiques en MySQL 5, en comparaison avec les anciennes versions.

Exemple 1 Les nombres sont utilisés avec leur valeur exacte dès que possible.

Avant MySQL 5.0.3, les nombres étaient traités comme des nombres décimaux, avec des résultats inexacts :

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

Depuis MySQL 5.0.3, les nombres sont utilisés tels que spécifiés, tant que possible :

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

Cependant, pour les valeurs décimales, les erreurs de précision existent toujours :

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

Un autre moyen de voir la différence entre les valeurs exactes et les approximations est d'ajouter un grand nombre de fois des petites valeurs. Considérez la procédure stockée suivante quie ajoute .0001 mille fois à une variable.

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;

La somme de d et f vaut logiquement 1, mais ce n'est vrai que pour le calcul décimal. Les calculs décimaux introduisent une erreur :

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

Exemple 2 La multiplication est faite avec l'échelle imposée par le standard SQL. C'est à dire que pour deux nombres X1 et X2 qui ont pour échelle respective S1 et S2, le résultat du produit est l'échelle S1 + S2.

Avant MySQL 5.0.3, ceci arrivait :

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

La valeur affichée est incorrecte. La valeur a été calculée correctement dans cette situation, mais n'est pas affichée avec l'échelle nécessaire. Pour afficher la valeur correcte, il faut utiliser ceci :

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

Depuis MySQL 5.0.3, l'échelle finale est correcte :

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

Exemple 3 L'arrondissement est bien maîtrisé.

Avant MySQL 5.0.3, l'arrondissement (par exemple, avec ROUND()) était lié à l'implémentation de la bibliothèque C sous-jacente. Cela conduisait à des incohérences entre les plates-formes. Par exemple, cela arrivait si vous essayiez de faire le même calcul sur Windows et sur Linux, ou sur différentes architectures telles que des x86 ou des PowerPC.

Depuis MySQL 5.0.3, l'arrondissement se fait comme ceci :

l'arrondit des colonnes DECIMAL des valeurs exactes utilisent la règle du ``arrondi à la valeur supérieure''. Les valeurs ayant une partie décimale supérieure ou égale à .5 sont arrondies au prochain entier, comme ceci :

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

L'arrondit des valeurs décimales utilise toujours la bibliothèque C, qui applique la règle de l'arrondit à l'entier le plus proche :

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

Exemple 4 Pour les insertions dans les tables, une valeur trop grande qui engendre un dépassement de capacité cause maintenant une erreur, et non plus la troncation de la valeur. Pour cela, il faut être en mode strict :

Avant MySQL 5.0.2, la troncation se faisait à la valeur valide la plus proche :

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

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

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

Depuis MySQL 5.0.2, le dépassement de capacité intervient dès que le mode strict est actif :

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

mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.10 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)

Exemple 5 Lors des insertions dans les tables, les divisions par zéro causent des erreur, et non plus des insertions de valeur NULL. Il faut utiliser le mot script et l'option ERROR_FOR_DIVISION_BY_ZERO.

Avant MySQL 5.0.2, la division par zéro conduisait à un NULL :

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

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

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

Depuis MySQL 5.0.2, la division par zéro est une erreur si le bon mode SQL est actif :

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

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
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)

Exemple 6 En MySQL 4, les valeurs litérales exactes et approximatives sont converties en nombres décimaux en double précision :

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;

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

En MySQL 5, la nombre décimaux approximative sont toujours convertis en nombres décimaux en précision double, mais les valeurs exactes sont gérées comme des nombres décimaux en précision simple DECIMAL :

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;

mysql> DESCRIBE t;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | decimal(3,1) | NO   |     | 0.0     |       |
| b     | double       | NO   |     | 0       |       |
+-------+--------------+------+-----+---------+-------+

Exemple 7 Si un argument d'une fonction d'agrégation est une valeur exacte, le résultat sera aussi exact, avec une échelle au moins égale à cet argument. Le résultat ne sera pas un nombre décimal de précision double.

Considérez les commandes suivantes :

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;

Le résultat avant 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    |       |
+--------+--------------+------+-----+---------+-------+

Le résultat est un nombre décimal en précision double, quel que soit le type des arguments.

Le résultat depuis MySQL 5.0.3 :

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

Le résultat est un nombre décimal en précision double pour les arguments de type nombre décimal. Le résultat est une valeur exacte pour les arguments exacts.