DOUBLE vs DECIMAL en MySQL

OK, entonces sé que hay toneladas de artículos que dicen que no debo usar DOUBLE para almacenar dinero en una base de datos MySQL, o terminaré con errores de precisión difíciles. El punto es que no estoy diseñando una nueva base de datos, me pregunto para encontrar la manera de optimizar un sistema existente. La versión más nueva contiene 783 columnas de tipo DOBLE, la mayoría de las cuales se usan para almacenar dinero o fórmulas para calcular la cantidad de dinero.

Así que mi primera opinión sobre el tema fue que recomiendo encarecidamente una conversión de DOUBLE a DECIMAL en la próxima versión, porque el doc de MySQL y todos lo dicen. Pero luego no pude encontrar ningún buen argumento para justificar esta recomendación, por tres razones:

  • No realizamos ningún cálculo en la base de datos. Todas las operaciones se realizan en Java utilizando BigDecimal, y MySQL simplemente se usa como almacenamiento simple para obtener resultados.
  • La precisión de 15 dígitos que ofrece un DOUBLE es suficiente, ya que almacenamos principalmente cantidades con 2 dígitos decimales y ocasionalmente números pequeños con 8 dígitos decimales para los argumentos de fórmula.
  • Tenemos un récord de producción de 6 años sin problemas conocidos de errores debido a una pérdida de precisión en el lado de MySQL.

Incluso al realizar operaciones en una tabla de filas de 18 millones, como SUM y multiplicaciones complejas, no pude realizar un error de falta de precisión. Y no hacemos este tipo de cosas en producción. Puedo mostrar la precisión perdida haciendo algo como

SELECT columnName * 1.000000000000000 FROM tableName;

Pero no puedo encontrar una manera de convertirlo en un error en el segundo dígito decimal. La mayoría de los problemas reales que encontré en Internet son entradas de foros de 2005 y anteriores, y no pude reproducir ninguno de ellos en un servidor MySQL 5.0.51.

Entonces, siempre que no realicemos ninguna operación aritmética de SQL, lo cual no planeamos hacer, ¿hay algún problema que deberíamos esperar al solo almacenar y recuperar un monto de dinero en una columna DOBLE?

En realidad es bastante diferente. DOBLE causa problemas de redondeo. Y si haces algo como 0.1 + 0.2 te da algo así como 0.30000000000000004 . Personalmente, no confío en los datos financieros que usan matemáticas de coma flotante. El impacto puede ser pequeño, pero quién sabe. Prefiero tener lo que sé que son datos confiables que datos aproximados, especialmente cuando se trata de valores monetarios.

El ejemplo de la documentación de MySQL http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html (lo reduzco, la documentación para esta sección es la misma para 5.5)

 mysql> create table t1 (i int, d1 double, d2 double); mysql> insert into t1 values (2, 0.00 , 0.00), (2, -13.20, 0.00), (2, 59.60 , 46.40), (2, 30.40 , 30.40); mysql> select i, sum(d1) as a, sum(d2) as b from t1 group by i having a <> b; -- a != b +------+-------------------+------+ | i | a | b | +------+-------------------+------+ | 2 | 76.80000000000001 | 76.8 | +------+-------------------+------+ 1 row in set (0.00 sec) 

Básicamente, si sums obtienes 0-13.2 + 59.6 + 30.4 = 76.8. Si summos b obtenemos 0 + 0 + 46.4 + 30.4 = 76.8. La sum de a y b es la misma, pero la documentación de MySQL dice:

Un valor de coma flotante tal como está escrito en una statement SQL puede no ser el mismo que el valor representado internamente.

Si repetimos lo mismo con el decimal:

 mysql> create table t2 (i int, d1 decimal(60,30), d2 decimal(60,30)); Query OK, 0 rows affected (0.09 sec) mysql> insert into t2 values (2, 0.00 , 0.00), (2, -13.20, 0.00), (2, 59.60 , 46.40), (2, 30.40 , 30.40); Query OK, 4 rows affected (0.07 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select i, sum(d1) as a, sum(d2) as b from t2 group by i having a <> b; Empty set (0.00 sec) 

El resultado esperado es un conjunto vacío.

Por lo tanto, mientras no realice ninguna operación aritmética SQL puede usar DOBLE, pero aún así preferiría DECIMAL.

Otra cosa a tener en cuenta acerca de DECIMAL es redondear si la parte fraccional es demasiado grande. Ejemplo:

 mysql> create table t3 (d decimal(5,2)); Query OK, 0 rows affected (0.07 sec) mysql> insert into t3 (d) values(34.432); Query OK, 1 row affected, 1 warning (0.10 sec) mysql> show warnings; +-------+------+----------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------+ | Note | 1265 | Data truncated for column 'd' at row 1 | +-------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from t3; +-------+ | d | +-------+ | 34.43 | +-------+ 1 row in set (0.00 sec) 

Acabamos de pasar por este mismo problema, pero al revés. Es decir, almacenamos cantidades en dólares como DECIMAL, pero ahora descubrimos que, por ejemplo, MySQL calculaba un valor de 4.389999999993, pero al almacenar esto en el campo DECIMAL, lo almacenaba como 4.38 en vez de 4.39 como queríamos a. Entonces, aunque DOUBLE puede causar problemas de redondeo, parece que DECIMAL también puede causar algunos problemas truncados.

“¿Hay algún problema que deberíamos esperar al solo almacenar y recuperar una cantidad de dinero en una columna DOBLE?”

Parece que no se pueden producir errores de redondeo en su escenario y, si los hubiera, se verían truncados por la conversión a BigDecimal.

Entonces yo diría que no.

Sin embargo, no hay garantía de que algún cambio en el futuro no introduzca un problema.

De tus comentarios,

el monto del impuesto redondeado al 4º decimal y el precio total redondeado al 2º decimal.

Usando el ejemplo en los comentarios, podría prever un caso en el que tenga 400 ventas de $ 1.47. Las ventas antes de impuestos serían de $ 588.00, y las ventas después de impuestos sumrían a $ 636.51 (que representan $ 48.51 en impuestos). Sin embargo, el impuesto a las ventas de $ 0.121275 * 400 sería de $ 48.52.

Esta fue una forma, aunque artificial, de forzar la diferencia de un centavo.

Me gustaría señalar que hay formularios de impuestos de nómina del IRS donde no les importa si un error está por debajo de cierta cantidad (si la memoria sirve, $ 0,50).

Su gran pregunta es: ¿a alguien le importa si ciertos informes se cancelan por un centavo? Si sus especificaciones dicen: sí, sea exacto al centavo, entonces debe realizar el esfuerzo de convertir a DECIMAL.

He trabajado en un banco donde se informó un error de un centavo como un defecto de software. Intenté (en vano) citar las especificaciones del software, que no requieren este grado de precisión para esta aplicación. (Estaba realizando muchas multiplicaciones encadenadas). También señalé la prueba de aceptación del usuario. (El software fue verificado y aceptado).

Por desgracia, a veces solo tienes que hacer la conversión. Pero lo animo a que A) se asegure de que sea importante para alguien y luego B) escriba pruebas para mostrar que sus informes son precisos al grado especificado.