Corso MariaDB / Scegliere i tipi numerici: interi, DECIMAL, FLOAT e DOUBLE

Usare tipi interi per quantità e identificatori e considerare DECIMAL quando serve una rappresentazione decimale esatta con una scala definita. FLOAT e DOUBLE conservano approssimazioni adatte a molte misurazioni, ma aumentare la precisione in virgola mobile non rende automaticamente esatti i confronti di uguaglianza decimale.

Partire dal significato del numero

Decidere se sono ammessi valori negativi, quanto può crescere il numero e che cosa significano le cifre frazionarie. La quantità di un ordine è normalmente intera, mentre una temperatura può essere continua. Un numero telefonico o un codice prodotto può contenere soltanto cifre e richiedere comunque una colonna testuale perché zeri iniziali e formato hanno significato. Il tipo di memorizzazione non sostituisce una politica di arrotondamento e conversione dei calcoli.

Tipo Ruolo tipico Decisione da verificare
TINYINT, SMALLINT, INT, BIGINT Conteggi, identificatori, valori interi Segno, intervallo e crescita attesa
DECIMAL(M,D) Quantità e importi decimali esatti Cifre totali M e cifre frazionarie D
FLOAT Misurazioni approssimate Accettabilità dell'errore di rappresentazione
DOUBLE Approssimazioni con maggiore precisione di FLOAT La precisione superiore non garantisce decimali esatti

Scegliere il tipo più piccolo soltanto per risparmiare spazio può creare problemi futuri di intervallo. Usare il tipo più grande per tutto non è una politica di progettazione. Adeguare tipi e operazioni ai valori reali, aggiungendo poi i vincoli richiesti dalle regole dell'applicazione.

Confrontare intervalli interi e UNSIGNED

Tipo Intervallo SIGNED Intervallo UNSIGNED
TINYINT da −128 a 127 da 0 a 255
SMALLINT da −32768 a 32767 da 0 a 65535
INT da −2147483648 a 2147483647 da 0 a 4294967295
BIGINT da −9223372036854775808 a 9223372036854775807 da 0 a 18446744073709551615

SIGNED consente valori negativi e positivi ed è la scelta predefinita per questi interi. UNSIGNED sposta l'intervallo a zero e valori positivi. INT(11) non indica un intervallo di memorizzazione a undici cifre: la larghezza di visualizzazione non amplia INT. Il riferimento INT spiega gli attributi del tipo.

Controllare anche applicazione e driver: le loro rappresentazioni numeriche potrebbero non conservare esattamente ogni valore BIGINT. Intervallo del database, interi rappresentabili esattamente nel linguaggio di programmazione e limiti degli input del modulo sono vincoli distinti.

Leggere correttamente precisione e scala di DECIMAL

DECIMAL(6,2) ha sei cifre totali, di cui due frazionarie. Restano quattro cifre per la parte intera e il massimo positivo è 9999.99. DECIMAL(10,2) ha otto cifre intere e due frazionarie. Il segno e il punto decimale non rientrano nel conteggio M. Nei valori SQL di questi esempi il separatore decimale è il punto.

L'input 1.235 non può restare invariato in una colonna con due cifre frazionarie: viene arrotondato alla scala disponibile. Anche in modalità rigorosa, l'arrotondamento frazionario può produrre una nota anziché lo stesso errore di un valore fuori intervallo. Controllare valore memorizzato e SHOW WARNINGS. La memorizzazione esatta non stabilisce quando arrotondare imposte o sconti: definire separatamente tale politica. Il riferimento DECIMAL descrive precisione, scala e arrotondamento.

Confrontare gli stessi valori in tre tipi numerici

Collegarsi come course_user al server di studio con shopcourse disponibile. La guida al client e ai file SQL spiega collegamento e SOURCE. Eseguire i controlli dentro la connessione, non nella shell del sistema operativo:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

Il database selezionato deve essere shopcourse. Salvare l'esempio normale completo in un file UTF-8 .sql ed eseguirlo con SOURCE e il percorso locale effettivo. Tenere separate le istruzioni intenzionalmente errate perché possono interrompere il file. Usare un'istanza di studio e verificare se numeric_lab esiste già prima di crearla.

L'esempio salva il modo SQL della connessione corrente e seleziona STRICT_TRANS_TABLES per rendere prevedibili i controlli di intervallo. Cambia la sessione, non il modo globale del server. Eseguire questo file normale prima dei singoli comandi di errore:

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
CREATE TABLE numeric_lab (
  item_id INT PRIMARY KEY,
  quantity TINYINT NOT NULL,
  exact_value DECIMAL(6,2) NOT NULL,
  float_value FLOAT NOT NULL,
  double_value DOUBLE NOT NULL
) ENGINE=InnoDB;
INSERT INTO numeric_lab VALUES
  (1, 10, 0.10, 0.1, 0.1),
  (2, 20, 0.20, 0.2, 0.2);
SELECT SUM(exact_value) AS exact_total,
       SUM(exact_value) = 0.30 AS exact_equal,
       SUM(float_value) = 0.30 AS float_equal,
       SUM(double_value) = 0.30 AS double_equal
FROM numeric_lab;
INSERT INTO numeric_lab VALUES (3, 127, 1.235, 1.235, 1.235);
SHOW WARNINGS;
SELECT item_id, quantity, exact_value FROM numeric_lab ORDER BY item_id;
Risultato Valore atteso Significato
exact_total 0.30 Somma dei due valori DECIMAL iniziali
exact_equal 1 La somma esatta è uguale a 0.30
float_equal 0 La somma approssimata FLOAT differisce da 0.30 esatto
double_equal 0 Anche questa somma decimale DOUBLE presenta una differenza di rappresentazione
exact_value della riga 3 1.24 Input arrotondato a due cifre frazionarie

Nei confronti, 1 significa vero e 0 falso. Le cifre stampate per un risultato approssimato possono dipendere da client ed espressione. Osservare il confronto invece di memorizzare una lunga frazione stampata. I riferimenti di FLOAT e DOUBLE distinguono precisione e rappresentazione.

Provare separatamente valori fuori intervallo

Dopo l'esempio normale, eseguire queste istruzioni una alla volta nella stessa connessione rigorosa. La prima supera il massimo di TINYINT con segno, pari a 127; la seconda supera l'intervallo della parte intera di DECIMAL(6,2). Entrambe devono fallire per valore fuori intervallo:

INSERT INTO numeric_lab VALUES (4, 128, 1.00, 1.0, 1.0);
INSERT INTO numeric_lab VALUES (4, 1, 10000.00, 1.0, 1.0);

Dopo ogni errore, SELECT COUNT(*) FROM numeric_lab deve ancora restituire tre righe. Non inserire questi errori intenzionali a metà del file normale, dove potrebbero interrompere le istruzioni successive.

Disattivare la modalità rigorosa può sostituire un input rifiutato con un valore limitato all'estremo dell'intervallo e un avviso, perdendo il significato originale. Leggere SHOW WARNINGS subito dopo l'istruzione analizzata: una query successiva può cambiare la diagnostica disponibile.

Rivedere la scelta del tipo e ripristinare la sessione

  • Conservare codici e numeri telefonici come testo quando il formato conta e non servono operazioni aritmetiche.
  • Scegliere intervallo e segno degli interi in base a una crescita plausibile.
  • Documentare scala decimale e momento di arrotondamento dei calcoli.
  • Per misurazioni approssimate, definire una tolleranza adatta invece di affidarsi all'uguaglianza decimale esatta.
SET SESSION sql_mode = @saved_sql_mode;

Ripristinare le impostazioni nella stessa connessione che ha salvato la variabile iniziale. Dopo una disconnessione, quella variabile non conserva più il valore originario: esaminare la nuova connessione invece di applicare una variabile salvata vuota.

Prima di ripetere l'esercizio, usare SELECT DATABASE() e SHOW CREATE TABLE numeric_lab per confermare che la destinazione sia la tabella di prova creata qui. Salvare i dati da conservare. Solo dopo la verifica, rimuoverla con:

DROP TABLE shopcourse.numeric_lab;

Il comando elimina la tabella nominata e tutte le righe. DROP TABLE non viene annullato da un normale ROLLBACK. Si può poi ripetere l'esempio normale completo. La tabella products della prima esercitazione sui prodotti resta fuori da questa reimpostazione.