Hosting guebs

Capítulo 23. Matemáticas de precisión

Tabla de contenidos

23.1. Tipos de valores numéricos
23.2. Cambios en el tipo de datos DECIMAL
23.3. Manejo de expresiones
23.4. Cómo se redondea
23.5. Ejemplos de matemáticas de precisión

MySQL 5 introduce matemáticas de precisión, esto es, tratamiento numérico que proporciona resultados más precisos y más control sobre valores inválidos que en versiones anteriores de MySQL. La matemática de precisión se basa en dos cambios de implementación:

Estos cambios tienen varias implicaciones para operaciones numéricas:

Un resultado importante de estos cambios es que MySQL proporciona un mejor cumplimiento del estándar SQL.

La siguiente discusión cubre varios aspectos de cómo funciona la matemática de precisión (incluyendo posibles incompatibilidades con aplicaciones anteriores). Al final, se dan algunos ejemplos que demuestran cómo MySQL 5 trata operaciones numéricas de forma más precisa que anteriormente.

23.1. Tipos de valores numéricos

El ámbito de matemáticas de precisón para operaciones de valores exactos incluyen tipos de datos precisos (DECIMAL y tipos interos) y literales de valores numéricos exactos. Los tipos de datos aproximados y literales numéricos se tratan como valores en coma flotante.

Literales numéricos de valores exactos tienen una parte entera o fraccional, o ambas. Pueden tener signo. Ejemplos: 1, .2, 3.4, -5, -6.78, +9.10.

Literales de valores numéricos aproximados se representan en notación científica con una mantisa y exponente. Una o ambas partes pueden tener signo. Ejemplos: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Números que parecen similares no necesitan ser ambos valores exactos o aproximados. Por ejemplo, 2.34 es un valor exacto (punto fijo), mientras que 2.34E0 es un valor aproximado (coma flotante).

El tipo de datos DECIMAL es un tipo de punto fijo y los cálculos son exactos. En MySQL, el tipo DECIMAL tiene varios sinónimos: NUMERIC, DEC, FIXED. El tipo entero también es un tipo de valor exacto.

Los tipos de datos FLOAT y DOUBLE son tipos de coma flotante y los cálculos son aproximados. En MySQL, los tipos sinónimos de FLOAT o DOUBLE son DOUBLE PRECISION y REAL.

23.2. Cambios en el tipo de datos DECIMAL

En MySQL 5.0.3, se hicieron varios cambios en distintos aspectos del tipo de datos DECIMAL (y sus sinónimos):

  • Numero máximo de dígitos

  • Formato de almacenamiento

  • Requerimientos de almacenamiento

  • Las extensiones MySQL no estándar al rango superior de columnas DECIMAL

Algunos de los cambios provocan posibles incompatibilidades para aplicaciones escritas en versiones antiguas de MySQL. Estas incompatibilidades se muestran durante esta sección.

La sintaxis de declaración para columnas DECIMAL sigue siendo DECIMAL(M,D), aunque el rango de valores para los argumentos ha cambiado algo:

  • M es el número máximo de dígitos (la precisión). Tiene un rango de 1 a 64. Introduce una posible incompatibilidad para aplicaciones antiguas, ya que versiones prévias de MySQL permiten el rango de 1 a 254.

  • D es el número de dígitos a la derecha del punto decimal (la escala). Tiene el rango de 0 a 30 y no debe ser mayor que M.

El valor máximo de 64 para M significa que los cálculos con valores DECIMAL son precisos hasta 64 dígitos. Este límite de 64 dígitos de precisión también se aplica a literales con valor exacto, así que el rango máximo de tales literales es diferente al anterior. (Antes de MySQL 5.0.3, los valores decimales podían tener hasta 254 dígitos. Sin embargo, los cálculos se hacían usando coma flotanto y por lo tanto eran aproximados, no exactos.) Este cambio en el rango de valores literales es otra posible fuente de incompatibilidades para aplicaciones antiguas.

Los valores para columnas DECIMAL no se representan como cadenas que requieren un byte por dígito o carácter de signo. En su lugar, se usa un formato binario que empaqueta nueve dítigos decimales en cuatro bytes. Este cambio del formato de almacenamiento de DECIMAL cambia los requerimientos de almacenamiento también. El almacenamiento para las partes enteras y fraccionales de cada valor se determinan por separado. Cada múltiple de nueve dígitos necesita cuatro bytes, y los dígitos restantes necesitan una fracción de cuatro bytes. Por ejemplo, una columna DECIMAL(18,9) tiene nueve dígitos en cada parte del punto decimal, así que la parte entera y fraccional necesitan cuatro bytes cada una. Una columna DECIMAL(20,10) tiene 10 dígitos en cada lado del punto decimal. Cada parte requiere cuatro bytes para nueve de los dígitos, y un byte para el dígito restante.

El almacenamiento requerido para los dígitos restantes lo da la siguiente tabla:

DígitosNúmero
Restantesde Bytes
00
11
21
32
42
53
63
74
84
94

Como resultado del cambio de cadena de caracteres a formato numérico para almacenamiento DECIMAL, las columnas DECIMAL no necesitan un carácter '+' o dígito '0' precedente. Antes de MySQL 5.0.3, si insertaba '+0003.1' en una columna DECIMAL(5,1) , se almacenaría como +0003.1. Desde MySQL 5.0.3, se almacena como 3.1. Aplicaciones que confían en el antiguo comportamiento deben modificarse teniendo en cuenta este cambio.

El cambio de formato de almacenamiento también significa que las columnas DECIMAL no soportan la extensión no estándar que permitía valores mayores que el rango implicado por la definición de la columna. Antiguamente, se reservaba un byte para almacenar el carácter de signo. Para valores positivos que no necesitaban byte de signo, MySQL permitía almacenar un byte extra. Por ejemplo, una columna DECIMAL(3,0) debe soportar un rango de al menos -999 a 999, pero MySQL debería permetir almacenar valores de 1000 a 9999 también, usando el byte de signo para almacenar un dígito extra. Esta extensión del rango superior de las columnas DECIMAL no se permite. En MySQL 5.0.3 y posteriores, una columna DECIMAL(M,D) permite como mucho MD dígitos a la izquierda del punto decimal. Esto puede resultar en una incompatibilidad si una aplicación tiene confianza en que MySQL permita valores "demasiado grandes".

El estándar SQL requiere que la precisión de NUMERIC(M,D) sean exactamente M dígitos. Para DECIMAL(M,D), requiere una precisión de al menos M dígitos, pero permite más. En MySQL, DECIMAL(M,D) y NUMERIC(M,D) son los mismo y ambos tienen una precisión de exactamente M dígitos.

Resumen de incompatibilidades:

La siguiente lista resume las incompatibilidades resultantes de cambios de la columna DECIMAL y tratamiento de valores. Puede usarla como guía cuando al portar aplicaciones antiguas para usar con MySQL 5.0.3 y posteriores.

  • Para DECIMAL(M,D), el máximo M es 64, no 254.

  • Los cálculos que implican valores decimales con valores exactos son precisos hasta 64 dígitos. Esto es menor que el número máximo de dígitos permitidos antes de MySQL 5.0.3 (254 dígitos), pero la precisión exacta es mayor. Los cálculos anteriormente se hacían con punto flotante de doble precisión, que tiene una precisión de 52 bits (acerca de 15 dígitos decimales).

  • La extensión no estándard MySQL del rango superior de columnas DECIMAL no se soporta.

  • Los caracteres precedentes '+' y '0' no se almacenan.

23.3. Manejo de expresiones

Con matemáticas de precisión, los números con valores exactos se usan tal y como se dan cuando es posible. Por ejemplo, números en comparaciones se usan exactamente como se dan sin cambiar su valor. En modo SQL estricto, para un INSERT en una columna con un tipo exacto (DECIMAL o entero), se inserta un número con su valor exacto si está dentro del rango de la columna. Cuando se recibe, el valor debe ser el mismo que se insertó. (Sin modo estricto, se permite truncar para INSERT.)

El tratamiento de expresiones numéricas depende de qué clase de valores contiene la expresión:

  • Si hay presente algún valor aproximado, la expresión es aproximada y se evalúa usando aritmética de punto flotante.

  • Si no hay presente ningún valor aproximado, la expresión contiene sólo valores exactos. Si algún valor exacto contiene una parte fraccional ( un valor a continuación del punto decimal), la expresión se evalúa usando aritmética exacta DECIMAL y una precisión de 64 dígitos. ("Exacto" esta sujeto a los límites de lo que puede representarse en binario. 1.0/3.0 puede representarse como .333... con un número finito de dígitos, no como "exactamente un tercio", así que (1.0/3.0)*3.0 no se evalúa como "exactamente 1.0.")

  • En otro caso, la expresión contiene sólo valores enteros. La expresión es exacta y evaluada usando aritmética entera y tiene la misma precisión que BIGINT (64 bits).

Si una expresión numérica contiene cualquier cadena de caracteres, se convierten a valores de coma flotante y doble precisión y la expresión es aproximada.

Las inserciones en columnas numéricas están afectadas por el modo SQL, controlada por la varible de sistema sql_mode. (Consulte Sección 1.7.2, “Selección de modos SQL”.) La siguiente discusión menciona el modo estricto (seleccionado por los valores de modo STRICT_ALL_TABLES o STRICT_TRANS_TABLES) y ERROR_FOR_DIVISION_BY_ZERO. Para activar todas las restricciones, puede usar el modo TRADITIONAL, que incluye tanto el modo estricto como ERROR_FOR_DIVISION_BY_ZERO:

mysql> SET sql_mode='TRADITIONAL';

Si se inserta un número en una columna de tipo exacto (DECIMAL o entero), debe insertarse con su valor exacto si está dentro del rango de la columna.

Si el valor tiene demasiados dígitos en la parte fraccional, se redondea y se genera una advertencia. El redondeo se hace como se describe en "Comportamiento del redondeo".

Si el valor tiene demasiados dígitos en la parte entera, es demasiado grande y se trata como se explica a continuación:

  • Si el modo estricto no está activado, el valor se trunca al valor legal más cercano y se genera una advertencia.

  • Si el modo estricto está activo, se genera un error de desbordamiento.

Desbordamiento inferior no se deteca, así que su tratamiento no está definido.

Por defecto, la división por cero produce un resultado de NULL y ninguna advertencia. Con el modo SQL ERROR_FOR_DIVISION_BY_ZERO activado, MySQL trata la división por cero de forma distinta:

  • Si el modo estricto no está activo, aparece una advertencia.

  • Si el modo estricto está activo, las inserciones y actualizaciones con divisiones por cero están prohibidas y ocurre un error.

En otras palabras, inserciones y actualizaciones que impliquen expresiones que realizan divisiones por cero pueden tratarse como errores, pero esto requiere ERROR_FOR_DIVISION_BY_ZERO además del modo estricto.

Suponga que tenemos este comando:

INSERT INTO t SET i = 1/0;

Esto es lo que ocurre al combinar modo estricto y ERROR_FOR_DIVISION_BY_ZERO :

sql_mode ValorResultado
''No advertencia, no error, i es NULL
strictNo advertencia, no error, i es NULL
ERROR_FOR_DIVISION_BY_ZEROAdvertencia, no error, i es NULL
strict,ERROR_FOR_DIVISION_BY_ZEROError, no se inserta el registro

Para inserciones de cadenas de caracteres en columnas numéricas, las conversiones de cadenas a números se tratan como se muestra si la cadena tiene contenido no numérico:

  • Una cadena que no comienza con un número no puede usarse como número y produce un error en modo estricto, o una advertencia en otro caso. Esto incluye la cadena vacía.

  • Una cadena que comienza con un número puede convertirse, pero se trunca la parte no numérica final. Esto produce un error en modo estricto, o una advertencia en otro caso.

23.4. Cómo se redondea

Esta sección discute el redondeo de la matemática precisa para la función ROUND() y para inserciones en columnas DECIMAL .

La función ROUND() redondea de forma distinta dependiendo de si su argumento es exacto o aproximada:

  • Para valores exactos, ROUND() usa la regla "redondeo al alza": Un valor con parte fraccional de .5 o superior se redondea al siguiente entero si es positivo o al anterior entero si es negativo. (En otras palabras, siempre se redondea alejándose del cero.) Un valor con una parte fraccional menor que .5 se redondea al anterior valor entero si es positivo o al siguiente entero si es negativo.

  • Para números aproximados, el resultado depende de la biblioteca C. En muchos sistemas, esto significa que ROUND() usa la regla "redondeo al número par más próximo": Un valor con un parte fraccional se redondea al siguiente entero par.

El siguiente ejemplo muestra cómo difiere el redondeo para valores exactos y aproximados:

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

Para inserciones en una columna DECIMAL, el objetivo es un tipo de datos exacto, así que el redondea usa "redondeo al alza" independientemente de si el valor a ser insertado es exacto o aproximado:

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. Ejemplos de matemáticas de precisión

Esta sección proporciona algunos ejemplos que muestran cómo la matemática precisa mejora los resultados de consultas en MySQL 5 comparados con versiones anteriores.

Ejemplo 1. Los números se usan con su valor exacto tal y como se da cuando es posible.

Antes de MySQL 5.0.3, los números tratados como valores en coma flotante producen valores inexactos:

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

Desde MySQL 5.0.3, los números se usan tal y como se dan cuando es posible:

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

Sin embargo, para valores en coma flotante, todavía ocurre la inexactitud:

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

Otra forma de ver la diferencia en el tratamiento de valores aproximados y exactos es añadir un pequeño número en una suma muchas veces. Considere el siguiente procedimiento aproximado, que añade .0001 a una variable 1000 veces.

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 suma de ambos d y f lógicamente debe ser 1, pero eso es cierto sólo para cálculos decimales. El cálculo de coma flotante introduce pequeños errores:

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

Ejemplo 2. La multiplicación se hace con la escala requerida por el estándar SQL. Esto es, para dos números X1 y X2 con escala S1 y S2, la escala del resultado es S1 + S2.

Antes de MySQL 5.0.3, esto es lo que ocurre:

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

El valor mostrado es incorrecto. El valor se calcula correctamente en este caso, pero no se muestra en la escala requerida. Para comprobar que el valor calculado realmente es .0001, pruebe:

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

Desde MySQL 5.0.3, la escala mostrada es correcta:

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

Ejemplo 3. El comportamiento del redondeo está bien definido.

Antes de MySQL 5.0.3, el comportamiento para el redondeo (por ejemplo con la función ROUND() ) depende de la implementación de la biblioteca C subyacente. Esto provoca inconsistencias entre plataformas. Por ejemplo, puede obtener un valor distinto en Windows y en Linux, o un valor distinto en máquinas x86 y PowerPc.

Desde MySQL 5.0.3, el redondeo se realiza así:

El redondeo para columnas DECIMAL y de valor exacto usa la regla de "redondeo hacia arriba". Los valoeres con una parte fraccional de .5 o mayor se redondean al entero más cercano y más lejano al cero, como se muestra aquí:

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

El redondeo de valores en coma flotante todavía usa la biblioteca C, que en muchos sistemas usa la regla "redondeo al número par más cercano". Los valores con cualquier parte fraccional se redondean al entero par más cercano:

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

Ejemplo 4. Para inserciones en tablas, un valor demasiado grande provoca un desbordamiento y un error, no se trunca a un valor legal. (Esto requiere modo estricto.)

Antes de MySQL 5.0.2, se truncaba a un valor legal:

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)

Desde MySQL 5.0.2, ocurre un desbordamiento si el modo estricto está activado:

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)

Ejemplo 5. Para inserciones en tablas, la división por cero causa un error, no un resultado NULL. (Esto requiere modo estricto y ERROR_FOR_DIVISION_BY_ZERO.)

Antes de MySQL 5.0.2, la división por cero tiene un resultado de 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)

Desde MySQL 5.0.2, la división por cero es un error si el modo SQL apropiado está activado:

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)

Ejemplo 6. En MySQL 4, literales de valores aproximados y exactos se convierten en valores de coma flotante y doble precisión:

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, el literal de valor aproximado todavía se convierte en un valor de coma flotante, pero el literal de valor exacto se trata como 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       |       |
+-------+--------------+------+-----+---------+-------+

Ejemplo 7. Si el argumento de una función agregada es un tipo numérico exacto, el resultado debe serlo también, con una escala al menos igual a la del argumento. El resultado no debe siempre ser un valor double.

Considere estos comandos:

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;

Resultado antes de 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    |       |
+--------+--------------+------+-----+---------+-------+

El resultado es un double independientemente del tipo del argumento.

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

El resultado es un double sólo para el argumento de coma flotante. El resultado es un tipo exacto para argumentos con tipos exactos.


Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.