Nelle operazioni di manipolazione dei dati contenuti in una database attraverso interrogazioni in linguaggio SQL, rientrano spesso anche i calcoli matematici; MySQL fornisce per questo scopo il supporto per vari tipi di dato numerici e funzioni appositamente dedicate; ma quanto sono precisi i calcoli effettuati tramite le istruzioni SQL utilizzate per le query? Nel corso di questa trattazione verranno analizzati gli strumenti e le caratteristiche che rendono MySQL un'applicazione affidabile per quanto riguarda i calcoli matematici.
Gli strumenti di MySQL per la precisione dei calcoli
Durante la realizzazione delle varie versioni di MySQL gli sviluppatori hanno sempre dedicato particolare attenzione agli strumenti dedicati alla precisione matematica dei calcoli, questa attenzione non è mancata anche nelle fasi di sviluppo della release numero 6, al momento in fase Alpha.
MySQL mette così a disposizione un valido supporto per la precisione dei calcoli, permettendo di ottenere risultati precisi e accurati e di effettuare un controllo efficace sui valori considerati non validi. In particolare, la precisione dei calcoli è il risultato di due strumenti:
- gli SQL modes, a cui spetta il controllo sui dati accettati o rifiutati dal database
- una libreria MySQL appositamente dedicata all'aritmetica frazionaria.
Queste funzionalità fornisco alcuni indubbi vantaggi durante le interrogazione mirate all'esecuzione di calcoli:
- per i valori numerici esatti, le procedure di calcolo non prevedono la restituzione di errori dovuti ai valori in virgola mobile; per fare un esempio, un valore come 0,001 viene utilizzato come valore esatto e non come semplice approssimazione, per cui sommando questo valore 1.000 volte si otterrà un risultato esattamente pari a 1 e non un valore approssimativo arrotondato al primo intero.
- Per quanto riguarda gli arrotondamenti, gli output generati tramite l'utilizzo della funzione
ROUND()nelle interrogazioni non saranno determinati da fattori relativi all'ambiente in cui gira il database, ma dai parametri che verranno passati alla funzione. - Sarà possibile avvalersi della stessa precisione matematica indipendentemente dalla piattaforma di riferimento, l'accuratezza dei risultati sarà così garantita su sistemi operativi Windows così come Unix o Unix-Like come Linux.
- Sarà possibile individuare, controllare e gestire le operazioni e i valori non considerati validi da MySQL, ad esempio, saranno rilevati i tentativi di divisione di valori per divisore pari 0 (division by zero) e gestiti come errori; una divisione per 0 potrà essere gestita come una comune operazione in grado di restituire il non valore NULL o come un errore sulla base dell'impostazione relativa alla variabile di sistema denominata sql_mode.
Le modalità di sql_modes e lo strict mode
Una delle modalità associate alla variabile sql_modes è appunto ERROR_FOR_DIVISION_BY_ZERO, che è stata implementata a partire dalla versione 5.0.2 di MySQL, essa produce una notifica di errore (warning) nel caso in cui si cerchi di dividere un dividendo per un divisore uguale a 0 durante interrogazioni che prevedono l'inserimento o la modifica di uno o più record. Se la modalità ERROR_FOR_DIVISION_BY_ZERO non è stata abilitata, il database restituirà il non valore NULL come risultato dell'operazione, nel caso in cui si utilizzino nelle queries le istruzioni INSERT IGNORE o UPDATE IGNORE, l'applicazione risponderà producendo una notifica di errore ma restituirà ugualmente NULL come risultato.
Un'altra modalità importante della variabile di sistema sql_mode, è denominata STRICT_ALL_TABLES, essa permette di utilizzare o meno lo strict mode che (quando abilitato) presiede al rifiuto dei dati considerati non validi per tutti i motori di archiviazione del database; esiste anche una modalità molto simile sia nel nome che nella funzione chiamata STRICT_TRANS_TABLES, il cui compito è quello di abilitare lo strict mode per i motori di archiviazione transazionali e (quando questo è possibile) anche per quelli che non lo sono.
Lo strict mode effettua un monitoraggio su come MySQL gestisce i valori di input non validi o semplicemente omessi; esistono diversi motivi perché un valore non venga considerato valido, un tentativo di INSERT o UPDATE può essere caratterizzato da un valore con tipo di dato non coincidente con quello attribuito ad un determinato campo, inoltre, un certo valore può essere troppo grande in base all'intervallo di valori previsto per uno specifico campo etc.
Più semplicemente, un input viene considerato invece omesso (missing), quando si tenta di inserire un nuovo record che non contiene un valore da registrare in un campo NOT NULL per cui non è stato previsto un valore predefinito tramite la clausola DEFAULT; ciò vale anche per i campi NULL se in fase di inserimento o modifica viene omesso il valore NULL.
Per quanto riguarda le tabelle transazionali, si verifica un errore per un valore non valido o omesso presente in un'istruzione quando sono abilitate sia la modalità STRICT_ALL_TABLES che STRICT_TRANS_TABLES, in questo caso l'interrogazione verrà semplicemente rifiutata.
Per quanto riguarda le tabelle non transazionali, verrà prodotto lo stesso risultato, infatti quando un'istruzione INSERT o UPDATE contiene un valore non accettabile questa verrà rifiutata senza che la tabella subisca modifiche; ma se le query hanno il compito di inserire o aggiornare più record e l'errore coinvolge uno dei record successivi al primo, il risultato prodotto dipenderà dalla modalità abilitata, quindi:
- con la modalità STRICT_ALL_TABLES il database restituirà un errore ignorando i record successivi anche se il primo inserimento o il primo aggiornamento ha avuto successo, il risultato della query potrebbe quindi essere solo parziale; per questo motivo con una configurazione del genere sarebbe più corretto effettuare interrogazioni che coinvolgano solo singole righe.
- Con la modalità STRICT_TRANS_TABLES, il database si occuperà di convertire un valore non valido nel valore valido più prossimo ad esso correggendo in parte i termini della query; nel caso in cui venga omesso un valore richiesto, il DBMS utilizzerà in luogo di esso il valore predefinito per il tipo dato previsto dal relativo campo; in entrambi i casi il Database Manager produrrà una notifica di errore senza per questo interrompere l'esecuzione dell'interrogazione.
Quando per esempio MySQL non ha lo strict mode abilitato, il tentativo di inserire in un campo un valore superiore a quello previsto porterà ad un'approssimazione, cioè al calcolo di un valore quanto più vicino possibile a quello richiesto in interrogazione:
mysql> CREATE TABLE tabella (campo TINYINT); mysql> INSERT INTO tabella SET campo = 128; mysql> SELECT campo FROM tabella; +-------+ |campo | +-------+ | 127 | +-------+
Lo strict mode potrà però essere abilitato sulle tabelle con una semplice interrogazione:
mysql> SET sql_mode='STRICT_ALL_TABLES';
In questo caso il tentativo di un inserimento si concluderà con la notifica di un errore come il seguente:
ERROR 1264 (22003): Out of range value adjusted for column 'campo' at row 1
la tabella coinvolta nell'interrogazione risulterà perciò invariata e quindi, nell'esempio proposto, vuota.
Stesso discorso per quanto riguarda gli errori generati da un divisore 0, nel caso in cui lo strict mode non sia abilitato una query che presenta una divisione per zero porterà alla generazione di un valore NULL:
mysql> INSERT INTO tabella SET campo = 5 / 0; mysql> SELECT campo FROM tabella; +--------+ | campo | +--------+ | NULL | +--------+
Abilitando invece lo strict mode, per esempio tramite un'istruzione SQL come la seguente:
mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
La query d'inserimento non verrà eseguita e di conseguenza la tabella rimarrà invariata.
Conclusioni
MySQL mette a disposizione alcuni strumenti per migliorare la precisione matematica dei calcoli nell'esecuzione di interrogazioni per l'inserimento e la modifica dei records; nel corso di questa breve trattazione sono state descritte, con particolare attenzione alle modalità delle variabili di sistema, le direttive utilizzate dal DBMS per la restituzione di risultati accurati e per la gestione di eventuali errori prodotti dalle queries.
Per eventuali approfondimenti sarà possibile consultare la sezione della guida ufficiale di MySQL appositamente dedicata alla precisione matematica e preziosa fonte di informazioni per la stesura di questo articolo.
