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.