DOUBLE vs DECIMAL in MySQL

OK, quindi so che ci sono tonnellate di articoli che affermano che non dovrei usare DOUBLE per immagazzinare soldi su un database MySQL, o finirò con insidiosi bug di precisione. Il punto è che non sto progettando un nuovo database, mi viene chiesto di trovare il modo di ottimizzare un sistema esistente. La versione più recente contiene 783 DOPPIE colonne digitate, la maggior parte delle quali utilizzate per memorizzare denaro o formula per calcolare l’importo del denaro.

Quindi la mia prima opinione sull’argomento era che dovrei raccomandare caldamente una conversione da DOUBLE a DECIMAL nella prossima versione, perché il documento MySQL e tutti lo dicono. Ma poi non ho trovato alcun argomento valido per giustificare questa raccomandazione, per tre ragioni:

  • Non eseguiamo alcun calcolo sul database. Tutte le operazioni sono eseguite in Java utilizzando BigDecimal e MySQL viene utilizzato come semplice archivio per i risultati.
  • La precisione di 15 cifre offerta da DOUBLE è sufficiente perché memorizziamo principalmente importi con 2 cifre decimali e occasionalmente piccoli numeri con 8 cifre decimali per argomenti formula.
  • Abbiamo un record di produzione di 6 anni senza problemi noti a causa di una perdita di precisione sul lato MySQL.

Anche eseguendo operazioni su una tabella di righe di 18 millesimi, come SOMMA e moltiplicazioni complesse, non potevo eseguire un errore di mancanza di precisione. E in realtà non facciamo questo genere di cose in produzione. Posso mostrare la precisione persa facendo qualcosa di simile

SELECT columnName * 1.000000000000000 FROM tableName;

Ma non riesco a capire un modo per trasformarlo in un bug alla seconda cifra decimale. La maggior parte dei problemi reali che ho trovato su Internet sono le voci del forum del 2005 e precedenti, e non ho potuto riprodurle su un server MySQL 5.0.51.

Quindi, fintanto che non eseguiamo operazioni di aritmetica SQL, che non intendiamo fare, ci sono problemi che dovremmo aspettarci solo memorizzando e recuperando una sum in una colonna DOPPIA?

In realtà è abbastanza diverso. DOUBLE causa problemi di arrotondamento. E se fai qualcosa come 0.1 + 0.2 ti dà qualcosa come 0.30000000000000004 . Personalmente non mi fiderei dei dati finanziari che usano la matematica in virgola mobile. L’impatto potrebbe essere piccolo, ma chi lo sa. Preferirei avere quello che so essere dati affidabili rispetto ai dati che sono stati approssimati, specialmente quando si tratta di valori monetari.

L’esempio della documentazione MySQL http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html (lo accorgi, la documentazione per questa sezione è la stessa per 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) 

Fondamentalmente se sommi a ottieni 0-13.2 + 59.6 + 30.4 = 76.8. Se sommiamo b otteniamo 0 + 0 + 46,4 + 30,4 = 76,8. La sum di aeb è la stessa, ma la documentazione di MySQL dice:

Un valore a virgola mobile come scritto in un’istruzione SQL potrebbe non essere uguale al valore rappresentato internamente.

Se ripetiamo lo stesso con decimale:

 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) 

Il risultato come previsto è un set vuoto.

Quindi, finché non esegui operazioni di aritmetica SQL, puoi utilizzare DOPPIO, ma preferirei comunque DECIMAL.

Un’altra cosa da notare su DECIMAL è l’arrotondamento se la parte frazionaria è troppo grande. Esempio:

 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) 

Abbiamo appena affrontato lo stesso problema, ma il contrario. Cioè, memorizziamo importi in dollari come DECIMAL, ma ora stiamo scoprendo che, per esempio, MySQL stava calcolando un valore di 4.389999999993, ma quando lo memorizzavo nel campo DECIMAL, lo stava memorizzando come 4.38 invece di 4.39 come volevamo a. Quindi, sebbene DOUBLE possa causare problemi di arrotondamento, sembra che DECIMAL possa causare anche alcuni problemi di troncamento.

“C’è qualche problema che dovremmo aspettarci solo memorizzando e recuperando un importo in una colonna DOPPIA?”

Sembra che nessun errore di arrotondamento possa essere prodotto nel tuo scenario e se ce ne fossero, verrebbero troncati dalla conversione in BigDecimal.

Quindi direi di no.

Tuttavia, non vi è alcuna garanzia che alcuni cambiamenti in futuro non introdurranno un problema.

Dai tuoi commenti,

l’importo delle imposte arrotondato al 4 ° decimale e il prezzo totale arrotondato al 2 ° decimale.

Utilizzando l’esempio nei commenti, potrei prevedere un caso in cui si hanno 400 vendite di $ 1,47. Le vendite al lordo delle imposte sarebbero $ 588,00 e le vendite al netto delle imposte sarebbero pari a $ 636,51 (pari a $ 48,51 in tasse). Tuttavia, l’imposta sulle vendite di $ 0,121275 * 400 sarebbe $ 48,52.

Questo era un modo, anche se forzato, per forzare la differenza di un centesimo.

Vorrei notare che ci sono moduli di imposta sui salari dall’IRS in cui a loro non importa se un errore è inferiore a un certo importo (se la memoria serve, $ 0,50).

La tua grande domanda è: a qualcuno importa se certi rapporti sono spenti di un centesimo? Se le tue specifiche dicono: sì, sii accurato al centesimo, quindi dovresti passare allo sforzo di convertire in DECIMAL.

Ho lavorato in una banca in cui un errore di un centesimo è stato segnalato come un difetto del software. Ho provato (invano) a citare le specifiche del software, che non richiedevano questo grado di precisione per questa applicazione. (Stava eseguendo molte moltiplicazioni concatenate.) Ho anche indicato il test di accettazione dell’utente. (Il software è stato verificato e accettato.)

Ahimè, a volte devi solo fare la conversione. Ma vorrei incoraggiarti a A) assicurarti che sia importante per qualcuno e poi B) scrivere test per dimostrare che i tuoi rapporti sono accurati al grado specificato.