Chapitre 23. Mathématiques de précision

Table des matières

23.1. Types de valeurs numériques
23.2. Changements de type de données avec DECIMAL
23.3. Gestion des expressions
23.4. Arrondissement de valeurs
23.5. Exemples de calculs de mathématiques

MySQL 5 introduit un module de mathématiques de précision, c'est à dire des opérations arithmétiques avec une précision accrue et un contrôle supérieur sur les erreurs, par rapport aux versions précédentes. Les nouvelles mathématiques sont basés sur deux changements dans l'implémentation :

Ces changements ont plusieurs implications au niveau des opérations numériques :

Une conséquence importante de ces changements est que MySQL est maintenant bien plus compatible avec les standards SQL.

Le chapitre suivant couvre différents aspects des mécanismes mathématiques, y compris les incompatibilités avec les anciennes applications. À la fin, des exemples illustrent le fonctionnement de MySQL 5.

23.1. Types de valeurs numériques

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.

23.2. Changements de type de données avec DECIMAL

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

  • Le nombre maximal de chiffres

  • Le format de stockage

  • La taille de stockage

  • L'extension non standard de MySQL sur l'intervale supérieure des colonnes DECIMAL

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

  • M est le nombre maximal de chiffres : la précision. Il prend une valeur entière entre 1 et 64. Cela introduit des incompatibilités possibles avec les anciennes applications, car MySQL autorisait l'intervalle de 1 à 254.

  • D est le nombre de chiffres décimaux : l'échelle. Il peut prendre des valeurs de 1 à 30, et ne doit pas dépasser M.

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 :

ResteNombre
Chiffresd'octets
00
11
21
32
42
53
63
74
84
94

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 copmte 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 MD 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.

  • Pour DECIMAL(M,D), la valeur maximale de M est 64, et non plus 254.

  • Les calculs impliquant des valeurs décimales exactes ont 64 chiffres de précision. C'est inférieur au nombre maximal de chiffre précédemment autorisé avant MySQL 5.0.3 (254 chiffres), mais la précision est malgré tout amélioré. Les calculs étaient fait en double précision, ce qui représente 52 bits ou 15 chiffres.

  • L'extension non standard MySQL de la limite supérieur de stockage des colonnes DECIMAL n'est plus supportée.

  • Les caractères initiaux ‘+’ et ‘0’ ne sont plus stockés.

23.3. Gestion des expressions

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 valeur approximative est impliquée, l'expression sera approximative, et sera évaluée avec l'arithmétique des nombres à virgule flottante.

  • Si aucune valeur approximative n'est impliquée, l'expression ne contient que des valeurs exactes. Si aucune valeur ne contient de partie décimale, l'expression sera évaluée avec avec l'arithmétique exacte DECIMAL et la précision sera de 64 chiffres. "Exact" est soumis aux limitations des représentations binaires. 1.0/3.0 peut être représenté par .333... avec un nombre infini de chiffre, mais jamais "exactement" comme un tiers, et (1.0/3.0)*3.0 ne vaudra jamais exactement "1.0."

  • Sinon, l'expression ne contient que des entiers. L'expression est exacte, et est évaluée avec l'arithmétique entière, et la précision est celle d'un BIGINT (64 bits).

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 :

  • Si le mode strict n'est pas activé, la valeur est coupée à la première valeur valide, et une alerte est générée.

  • Si le mode strict est activé, une erreur de dépassement de capacité survient.

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 :

  • Si le mode strict n'est pas activé, une alerte survient.

  • Si le mode strict est activé, les insertions et modifications qui manipulent des divisions par zéro sont interdites et une erreur survient.

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 :

sql_mode ValueResult
''Aucune alerte, aucune erreur, i prend la valeur de NULL
strictAucune alerte, aucune erreur, i vaut NULL
ERROR_FOR_DIVISION_BY_ZEROAlerte, pas d'erreur, i vaut NULL
strict,ERROR_FOR_DIVISION_BY_ZEROErreur, pas d'insertion.

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

  • Une chaîne qui ne commence pas par un nombre, ne peut pas être utilisée comme nombre et produit une erreur en mode strict, ou une alerte sinon. Cela vaut aussi pour les chaînes vides.

  • Une chaîne qui commence avec un nombre peut être convertie, mais la partie non numérique sera tronquée. Cela produit une erreur en mode strict et une alerte sinon.

23.4. Arrondissement de valeurs

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 fonctoin ROUND() arrondit différement les valeurs, suivant qu'elles sont exactes ou approximative :

  • Pour les valeurs exactes, ROUND() utilise la règle de l'arrondissement "à l'entier supérieur" : une valeur ayant une partie décimale de 0.5 ou plus est arrondie au prochain entier si elle est positive, et à l'entier inférieur si elle est négative (en d'autres termes, elle est arrondi en s'éloignant de 0). Une valeur avec une partie décimale inférieure à .5 est arrondi à l'entier inférieur si elle est positive, et supérieur si elle est négative.

  • Pour les nombres à valeur approchée, le résultat dépend de la bibliothèque C du système. Sur de nombreuses plates-formes, cela signifie que ROUND() utilise la règle de l'arrondissement "au prochain entier pair" : une valeur où la partie décimale est arrondie au prochain entier pair.

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

23.5. Exemples de calculs de mathématiques

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.


This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.