Manuel de référence MySQL 5.0

Les fonctionnalités mathématiques couvrent les types de données exacts (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 pourvues 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 MySQL5.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'intervalle 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'intervalle 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 faits 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 requièrent un octet par chiffre ou signe. À la place, 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 côté de la virgule, ce qui fait que la partie entière et la partie décimale demandent 4 octets chacune. Une colonne DECIMAL(20,10) dispose de 10 chiffres de chaque côté de la virgule. Cela fait 4 octets pour chaque groupe de 9 chiffres, et 1 octet pour le reste.

Le stockage requis pour le « reste » est présenté dans le tableau suivant :

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 changement de format de stockage signifie que les colonnes DECIMAL ne supportent plus les extensions non standards qui permettaient aux valeurs trop grandes d'être stockées. Pour les valeurs positives qui ne requièrent pas de signe, MySQL permettait l'ajout d'un chiffre de plus. Par exemple, dans une colonne DECIMAL(3,0), l'intervalle 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. Dans MySQL 5.0.3 et les plus récentes versions, 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écision 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 vos anciennes applications et les migrer vers MySQL 5.0.3.

• Pour DECIMAL(M,D), la valeur maximale de M est 64, et non 254.
• Les calculs impliquant des valeurs décimales exactes ont 64 chiffres de précision. C'est inférieur au nombre maximal de chiffres précédemment autorisés avant MySQL 5.0.3 (254 chiffres), mais la précision est malgré tout améliorée. Les calculs étaient faits en double précision, ce qui représente 52 bits ou 15 chiffres.
• L'extension non standard MySQL de la limite supérieure de stockage des colonnes DECIMAL n'est plus supportée.
• Les caractères initiaux '+' et '0' ne sont plus stockés.

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'intervalle 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épend 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 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 chiffres, 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 Section 1.5.2 : « Sélectionner les modes SQL »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'intervalle de validité de la colonne.

Si la valeur a trop de chiffres décimaux, un arrondissement survient 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 suit :

• 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 NULL et aucune alerte n'est générée. 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 :

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

• 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.

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éremment les valeurs, suivant qu'elles sont exactes ou approximatives :

• 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 arrondie en s'éloignant de 0). Une valeur avec une partie décimale inférieure à 0.5 est arrondie à l'entier inférieur si elle est positive, et à l'entier 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    |
+------+

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 qui 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 cela 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 de l'« 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'arrondi des valeurs décimales utilise toujours la bibliothèque C, qui applique la règle de l'arrondi à 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 erreurs, et non 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 : dans MySQL 4, les valeurs litté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       |       |
+-------+-------------+------+-----+---------+-------+

Dans MySQL 5, les nombres décimaux approximatives 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.