DOUBLE vs DECIMAL dans MySQL

Question

OK, donc je sais qu'il y a des tonnes d'articles disant que je ne devrais pas utiliser le DOUBLE pour stocker de l'argent sur une base de données MySQL, ou je vais finir avec une délicate précision de bugs. Le point est que je ne suis pas la conception d'une nouvelle base de données, je me suis demander trouver de façon à optimiser un système existant. La nouvelle version contient 783 DOUBLE tapé colonnes, la plupart d'entre eux utilisés pour stocker de l'argent ou de la formule de calcul montant d'argent.

Donc mon premier avis sur le sujet a été, je devrais vous conseillons fortement de conversion de DOUBLE à VIRGULE dans la prochaine version, parce que la doc MySQL et tout le monde à le dire. Mais alors je ne pouvais pas trouver tout bon argument pour justifier cette recommandation, et ce pour trois raisons :

• Nous n'effectuons pas de calcul sur la base de données. Toutes les opérations sont effectuées en Java à l'aide de BigDecimal, et MySQL est juste utilisé comme un simple stockage des résultats.
• Les 15 chiffres de précision DOUBLE propose est amplement suffisant car nous stockons essentiellement montants avec 2 chiffres après la virgule, et occasionnellement de petits nombres wit 8 chiffres après la virgule pour la formule des arguments.
• Nous avons un 6 années d'expérience en production sans problème connu de bug dû à une perte de précision sur le MySQL côté.

Même en effectuant des opérations sur les 18 millions de lignes de la table, comme la SOMME et le complexe de multiplications, je ne pouvais pas exécuter un bug du manque de précision. Et nous ne sommes pas réellement faire ce genre de choses dans la production. Je peux montrer la précision perdu en faisant quelque chose comme

SELECT columnName * 1.000000000000000 FROM tableName;

Mais je ne peux pas trouver un moyen de le transformer en un bug lors de la 2ème décimale. La plupart des problèmes réels que j'ai trouvé sur internet le sont de 2005 et les anciennes entrées de forum, et je ne pouvais pas reproduire l'un d'eux sur un serveur MySQL 5.0.51.

Donc, tant que nous n'effectuons pas de SQL opérations arithmétiques, dont nous n'avons pas l'intention de le faire, il y a aucune question, nous devons nous attendre à partir de seulement le stockage et la récupération d'un montant d'argent dans une DOUBLE colonne ?

Answer 1

En fait, c'est assez différent. DOUBLE provoque des problèmes d'arrondi. Et si vous faites quelque chose comme 0.1 + 0.2 cela vous donne quelque chose comme 0.30000000000000004 . Personnellement, je ne ferais pas confiance aux données financières qui utilisent des mathématiques en virgule flottante. L'impact peut être faible, mais qui sait. Je préférerais avoir ce que je sais être des données fiables plutôt que des données approximées, en particulier lorsqu'il s'agit de valeurs monétaires.

Answer 2

L'exemple de la documentation de MySQL http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html (j'ai rétrécir, la documentation de cette section est le même pour 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;
+------+-------------------+------+
| i    | a                 | b    |
+------+-------------------+------+
|    2 | 76.80000000000001 | 76.8 |
+------+-------------------+------+
1 row in set (0.00 sec)

Fondamentalement, si vous le montant que vous obtenez 0-13.2+59.6+30.4 = 76.8. Si nous résumons b nous obtenons 0+0+46.4+30.4=78.8. La somme de a et b est le même mais la documentation de MySQL dit:

Une valeur à virgule flottante comme écrit dans une instruction SQL ne peut pas être la même que la valeur représentée en interne.

Si nous répétons la même chose avec les décimales:

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)

Le résultat attendu est l'ensemble vide.

Donc, tant que vous n'effectuez aucune SQL arithemetic opérations, vous pouvez utiliser des DOUBLES, mais je préfère encore DÉCIMAL.

Une autre chose à noter à propos de la DÉCIMALE est arrondi si la partie fractionnaire est trop grand. Exemple:

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)

Answer 3

Nous venons de vivre le même problème, mais dans l’inverse. C'est-à-dire que nous stockons des montants en dollars sous DECIMAL, mais nous constatons maintenant que, par exemple, MySQL calculait une valeur de 4,389999999993, mais en le stockant dans le champ DECIMAL, il le stockait sous la forme 4,38 au lieu de 4,39 comme nous le souhaitions. c'est à. Ainsi, bien que DOUBLE puisse causer des problèmes d’arrondi, il semble que DECIMAL puisse également causer des problèmes de troncature.

Answer 4

"y a-t-il un problème auquel nous devrions nous attendre en ne stockant et en récupérant un montant d'argent que dans une colonne DOUBLE?"

Il semble qu'aucune erreur d'arrondi ne puisse être produite dans votre scénario et, le cas échéant, elle serait tronquée par la conversion en BigDecimal.

Donc je dirais non.

Cependant, rien ne garantit qu'un changement à l'avenir ne posera pas de problème.

Answer 5

À partir de vos commentaires,

le montant de la taxe est arrondi à la 4e décimale et le prix total arrondi à la 2ème décimale.

À l'aide de l'exemple dans les commentaires, j'ai peut prévoir les cas où vous avez 400 $de ventes de 1,47. Vente-avant impôt serait de $588.00, et les ventes après impôt somme de $636.51 (comptabilité de $48.51 en impôts). Toutefois, la taxe de vente de $0.121275 * 400 serait $48.52.

C'était une manière, certes artificiel, pour forcer un centime de différence.

Je remarque qu'il y a de la paie de l'impôt des formulaires de l'IRS où ils ne se soucient pas si une erreur est inférieure à un certain montant (si ma mémoire est bonne, de 0,50$).

Votre grande question est: quelqu'un soin si certains rapports sont désactivés par un sou? Si votre specs dire: oui, être précis au centime près, alors vous devez passer par l'effort de convertir en DÉCIMAL.

J'ai travaillé dans une banque où un penny erreur a été signalée comme un défaut logiciel. J'ai essayé (en vain) de citer des spécifications du logiciel, qui n'a pas besoin de ce degré de précision pour cette application. (C'était l'exécution de nombreuses enchaîné multiplications.) J'ai également fait le test d'acceptation des utilisateurs. (Le logiciel a été vérifié et accepté.)

Hélas, parfois, vous avez juste à faire la conversion. Mais je vous encourage à A) assurez-vous qu'il est important pour quelqu'un, puis B) écrire des tests pour montrer que vos rapports sont précis au degré spécifié.