Tipi di dati SQL: riferimento e best practice

Un tipo di dati SQL è una specifica fondamentale che definisce quali valori una colonna può contenere e quanto spazio di archiviazione richiedono tali valori in una tabella di database. Comprendere i tipi di dati SQL è essenziale per chiunque crei pipeline di dati, scriva query o progetti schemi di database, poiché questi tipi controllano direttamente l'integrità dei dati, l'efficienza di archiviazione e le prestazioni delle query. Quando definisci una colonna in una tabella di database, non stai solo specificando un nome: stai stabilendo un contratto sul tipo di informazioni che risiederanno in quella colonna e su come il database dovrebbe trattarle.

L'importanza di scegliere il tipo di dati corretto non può essere sopravvalutata. I tipi di dati SQL impongono regole logiche sui valori che possono essere memorizzati, impedendo fin dall'inizio l'inserimento di dati non validi. Inoltre, influiscono notevolmente sulla velocità di esecuzione delle query e sullo spazio su disco consumato dalle tabelle. Un tipo di dati scelto male può rallentare le query, sprecare spazio di archiviazione e creare bug impercettibili nelle pipeline di dati. Al contrario, la selezione di tipi appropriati può migliorare la scalabilità a lungo termine e ottimizzare notevolmente le prestazioni del database tra carichi di lavoro di analisi, applicazioni in tempo reale e pipeline di feature di machine learning.

I tipi di dati SQL sono ampiamente suddivisi in quattro gruppi principali: tipi di dati numerici per i calcoli matematici, tipi di dati carattere e stringa per il testo, tipi di dati data e ora per registrare quando si verificano gli eventi e tipi di dati specializzati per dati binari e altri formati. I diversi sistemi di database (MySQL, PostgreSQL, SQL Server e Oracle) implementano ciascuno queste categorie con lievi variazioni nella denominazione, nella precisione e nei requisiti di archiviazione. Questa guida fornisce un riferimento pratico per comprendere i tipi di dati SQL nei sistemi di database più diffusi, insieme alle best practice per scegliere il tipo giusto per il tuo caso d'uso.

Comprendere cosa impone un tipo di dati

Un tipo di dati è molto più di una semplice etichetta. Quando dichiari che una colonna è di tipo INTEGER o VARCHAR, stai dicendo esattamente al tuo sistema di gestione del database quale tipo di valori appartiene a quella colonna e come trattarli durante le query e l'archiviazione. Il database utilizza queste informazioni per convalidare i dati al momento dell'inserimento, impedendo inserimenti che violano i vincoli del tipo. I moderni sistemi di database, come quelli basati sulle transazioni ACID, garantiscono che questa convalida avvenga in modo affidabile anche durante pattern di accesso simultanei.

Considera un semplice esempio: se definisci una colonna come INTEGER, il database rifiuterà qualsiasi tentativo di inserire testo come "hello" o valori non interi come 3.14. Questa convalida avviene automaticamente, imponendo l'integrità dei dati rifiutando di memorizzare formati di dati errati. Senza questa imposizione, le query e le analisi a valle incontrerebbero dati corrotti o incoerenti, portando a risultati errati e a una perdita di tempo per il debug.

I tipi di dati comunicano anche le intenzioni ad altri sviluppatori e data engineer che lavorano con il tuo schema. Quando qualcuno vede che una colonna è definita come DECIMAL anziché FLOAT, capisce immediatamente che questa colonna memorizza valori monetari precisi che non possono tollerare errori di arrotondamento. Questa documentazione implicita riduce i malintesi e rende gli schemi più gestibili nel tempo.

In che modo i tipi di dati influiscono sull'archiviazione e sulle prestazioni delle query

La scelta del tipo di dati ha conseguenze dirette sullo spazio su disco consumato dalle tabelle e sulla velocità di esecuzione delle query. L'efficienza di archiviazione influisce sui costi del cloud, sui tempi di backup e sul numero di righe che puoi inserire in memoria per l'elaborazione. Le prestazioni delle query dipendono in parte dalle dimensioni del tipo di dati: i tipi più piccoli possono essere elaborati più rapidamente perché un numero maggiore di righe entra nella cache della CPU e meno dati devono essere trasferiti tra l'archiviazione e il calcolo. Per i team che creano pipeline ETL che elaborano milioni di righe al giorno, queste ottimizzazioni si traducono in miglioramenti misurabili in termini di costi e latenza.

I tipi di dati stringa variano in modo significativo nella loro impronta di archiviazione. Una colonna CHAR riserva sempre l'intera lunghezza dichiarata, aggiungendo spazi anche se si memorizza un valore breve. Una colonna VARCHAR, al contrario, utilizza solo lo spazio necessario per il valore effettivamente memorizzato. Se la maggior parte dei nomi dei tuoi clienti è inferiore a 30 caratteri, memorizzarli come VARCHAR(50) consente di risparmiare molto spazio rispetto a CHAR(50). Questo risparmio di spazio si accumula su milioni di righe e può ridurre la latenza delle query perché una quantità maggiore di dati entra nella memoria disponibile.

Anche i tipi numerici influenzano le prestazioni. L'uso di BIGINT quando INT sarebbe sufficiente spreca spazio di archiviazione ed elaborazione. Al contrario, l'uso di SMALLINT per una colonna che deve memorizzare valori superiori a 32.000 causa errori di overflow. Comprendere i requisiti di intervallo e precisione dei dati ti consente di scegliere il tipo di dati più piccolo che contenga in sicurezza i tuoi valori, mantenendo il database veloce ed efficiente.

Gli indici, che accelerano notevolmente le prestazioni delle query, sono più veloci se definiti su tipi di dati appropriati. Un indice su una colonna TINYINT è più efficiente di un indice su una colonna TEXT. Scegliendo tipi numerici di dimensioni adeguate ed evitando indici su colonne di testo molto grandi, moltiplichi i vantaggi prestazionali dell'indicizzazione sull'intero carico di lavoro. I motori di query distribuiti come Apache Spark beneficiano in modo particolare di tipi di dati di dimensioni adeguate, poiché i tipi più piccoli riducono il trasferimento di rete durante le operazioni di shuffle.

Scegliere il tipo di dati giusto

La regola d'oro per la selezione del tipo di dati consiste nell'utilizzare il tipo più piccolo che contenga in sicurezza i tuoi dati. Questo principio, applicato in modo coerente durante la progettazione dello schema, produce vantaggi in termini di efficienza di archiviazione, velocità delle query e scalabilità del sistema. Prima di selezionare un tipo, chiediti: qual è il valore massimo che questa colonna potrebbe contenere? Di quanta precisione ho bisogno? Questo valore sarà mai NULL?

Per i dati numerici, esamina la distribuzione effettiva dei dati. Se una colonna contiene valori compresi tra 0 e 100, TINYINT è perfetto. Se memorizzi ID cliente che potrebbero superare i 2 miliardi, INT è sufficiente; usa BIGINT solo se hai realmente bisogno di spazio di archiviazione per valori superiori a 2 miliardi. Fare questa distinzione su decine di colonne nel tuo schema può ridurre le dimensioni totali della tabella del 20-30%, migliorando direttamente le prestazioni delle query.

Quando lavori con le stringhe, considera il compromesso tra archiviazione e flessibilità. CHAR ti costringe a scegliere una lunghezza massima e utilizza sempre quello spazio. VARCHAR consente di memorizzare in modo efficiente dati a lunghezza variabile, ma richiede di scegliere un valore massimo che non causi il troncamento. VARCHAR(50) per i nomi rappresenta un buon compromesso: è sufficientemente grande per quasi tutti i nomi, ma impedisce la memorizzazione accidentale di valori estremamente lunghi che potrebbero causare problemi di qualità dei dati. Per blocchi di testo molto grandi, come i corpi degli articoli o i messaggi di log, utilizza tipi TEXT o CLOB che non richiedono la specifica preventiva della lunghezza.

Convalida le tue scelte con dati di esempio prima di procedere alla distribuzione in produzione. Inserisci dati reali in una tabella di test con lo schema proposto e osserva l'utilizzo effettivo dell'archiviazione. Esegui le query previste e misura le prestazioni. Questo approccio empirico rivela se le tue scelte supportano il carico di lavoro che stai effettivamente eseguendo. Le piattaforme di database offrono in genere strumenti per analizzare i piani di esecuzione delle query e identificare le operazioni lente causate da tipi di dati non ottimali.

Tipi di dati numerici

I tipi di dati numerici memorizzano numeri e si dividono in due famiglie principali: tipi interi per numeri interi e tipi decimali o a virgola mobile per numeri con componenti frazionari.

I tipi interi rappresentano numeri interi senza cifre decimali. Il tipo di dati INTEGER, chiamato anche INT, è la scelta più comune per i valori interi e memorizza un numero a 4 byte che può rappresentare valori da circa -2 miliardi a +2 miliardi. Quando hai bisogno di un intervallo più piccolo, ad esempio per memorizzare valori di età che non superano 127, TINYINT utilizza un solo byte ed è perfetto. SMALLINT occupa due byte e gestisce valori fino a circa 32.000, utile per colonne come quantità o conteggi che rimangono relativamente piccoli. BIGINT, un intero a 8 byte, accoglie numeri astronomici ed è necessario quando si memorizzano ID generati da sistemi distribuiti o timestamp misurati in millisecondi.

Il tipo di dati DECIMAL, a volte chiamato NUMERIC nella documentazione dello standard SQL, memorizza numeri a precisione fissa adatti per calcoli finanziari e altri contesti in cui gli errori di arrotondamento non sono accettabili. DECIMAL memorizza valori esatti senza l'approssimazione intrinseca dell'aritmetica a virgola mobile. Quando definisci DECIMAL(10,2), stai dicendo "Voglio memorizzare numeri con un massimo di 10 cifre totali, di cui esattamente 2 a destra della virgola decimale". Questa precisione significa che DECIMAL(10,2) memorizza in sicurezza valori come 99999999.99 ma rifiuterà qualsiasi valore con più di due cifre decimali. Le banche e i sistemi di contabilità si affidano a DECIMAL perché le normative finanziarie richiedono calcoli esatti e verificabili senza errori di arrotondamento.

NUMERIC funge da nome standard SQL per i dati decimali a precisione fissa e si comporta in modo identico a DECIMAL nella maggior parte dei sistemi di database. Alcuni database utilizzano NUMERIC e DECIMAL in modo intercambiabile, mentre altri li documentano separatamente per motivi storici. Controlla la documentazione del tuo database per confermare il comportamento esatto, ma considerali funzionalmente equivalenti nella pratica.

La creazione di una tabella con colonne numeriche illustra questi tipi nel contesto. Una tipica tabella delle vendite potrebbe essere simile a questa:

Qui, employee_id utilizza INT perché gli ID dei dipendenti sono in genere nell'ordine dei milioni. Age utilizza TINYINT perché l'età umana non supera mai i 127 anni. Salary e bonus_percentage utilizzano DECIMAL per garantire calcoli precisi durante l'elaborazione delle buste paga, dove anche i minimi errori di arrotondamento si accumulano all'interno di un'organizzazione. Le moderne piattaforme di dati come Delta Lake applicano questi tipi in modo rigoroso, garantendo che i dati con tipi non corretti non possano essere inseriti nelle tabelle di produzione.

Comprendere i numeri a virgola mobile

I tipi a virgola mobile memorizzano valori numerici approssimativi con una precisione specificata. FLOAT e DOUBLE utilizzano la rappresentazione binaria IEEE 754, che scambia l'esattezza con la velocità e l'intervallo. Un FLOAT occupa in genere 4 byte e memorizza valori approssimativi, mentre DOUBLE occupa 8 byte e offre una precisione maggiore.

La rappresentazione a virgola mobile introduce artefatti di arrotondamento perché molti valori decimali non possono essere rappresentati esattamente in formato binario. Ad esempio, 0.1 non può essere rappresentato esattamente in virgola mobile binaria, quindi qualsiasi calcolo che coinvolga 0.1 potrebbe essere leggermente impreciso. Questi piccoli errori si accumulano in lunghe catene di calcoli, producendo alla fine risultati visibilmente errati. Per questo motivo, non dovresti mai usare FLOAT o DOUBLE per dati monetari o altri valori in cui l'esattezza è fondamentale.

La scelta appropriata tra DECIMAL e FLOAT dipende dal tuo caso d'uso. Utilizza DECIMAL per tutti i dati finanziari, le misurazioni scientifiche precise o i calcoli in cui la correttezza deve essere verificabile. Utilizza FLOAT per le approssimazioni, il calcolo scientifico in cui sono accettabili piccoli errori o le feature di machine learning in cui la leggera imprecisione non influisce sulla qualità del modello. Le prestazioni delle query migliorano con l'uso di tipi di dati di dimensioni adeguate e le operazioni FLOAT sono più veloci delle operazioni DECIMAL perché la matematica a virgola mobile è accelerata dall'hardware su tutti i processori moderni.

Confronta questi due approcci per memorizzare i prezzi dei prodotti:

La seconda versione garantisce che i prezzi come 19.99 vengano memorizzati esattamente, senza mai subire errori di arrotondamento durante i calcoli o la visualizzazione. La prima versione potrebbe rappresentare internamente 19.99 come 19.989999... internamente, causando sottili discrepanze nei calcoli totali e nei prezzi mostrati ai clienti.

Tipi di dati di data e ora

I tipi di data e ora memorizzano informazioni temporali, ovvero il momento in cui si sono verificati gli eventi o in cui i dati devono essere considerati rilevanti. Questi tipi sono essenziali per l'analisi delle serie temporali, la registrazione degli eventi e i processi aziendali che tracciano quando accadono le cose.

Il tipo DATE memorizza solo la parte della data (anno, mese e giorno) nel formato YYYY-MM-DD senza alcuna componente oraria. Utilizza DATE quando devi registrare solo il giorno in cui è accaduto qualcosa, come la data di nascita di un cliente o la data di una transazione, senza preoccuparti dell'ora o del minuto esatto. DATE occupa uno spazio di archiviazione minimo (in genere 3 byte) e semplifica le query che raggruppano gli eventi per giorno di calendario.

Il tipo TIME memorizza solo la parte dell'ora (ore, minuti e secondi) senza una data. TIME è meno comune di DATE o TIMESTAMP, ma appare negli schemi che registrano orari ricorrenti, come l'orario di lavoro o gli orari degli appuntamenti all'interno di una giornata.

Il tipo TIMESTAMP (chiamato DATETIME in alcuni sistemi come MySQL e SQL Server) memorizza sia le informazioni sulla data che sull'ora nel formato YYYY-MM-DD HH:MM:SS. TIMESTAMP acquisisce il momento completo in cui si è verificato un evento, con precisione al secondo (o superiore, a seconda del database). La maggior parte dei sistemi basati su eventi utilizza TIMESTAMP per registrare esattamente quando sono state create le voci di log, quando sono stati effettuati gli ordini o quando sono arrivate le letture dei sensori. Molti sistemi analitici basati su progetti con star schema utilizzano chiavi TIMESTAMP per un'analisi temporale efficiente e il tracciamento dei fatti storici.

Scegli DATE rispetto a TIMESTAMP in base ai tuoi pattern di query. Se la tua logica aziendale raggruppa gli eventi per data di calendario e non ha mai bisogno di precisione all'interno della giornata, DATE è più pulito ed efficiente. Se devi calcolare il tempo trascorso tra gli eventi, rilevare i trend all'interno di un'ora o mantenere un ordine cronologico preciso, TIMESTAMP è necessario.

Esempi di definizioni di colonne di data e ora:

Qui, birthdate utilizza DATE perché ti interessa solo la data di nascita della persona, non l'ora in cui è nata. account_creation_date utilizza TIMESTAMP perché devi sapere esattamente quando è stato creato l'account, potenzialmente per rilevare pattern di frode o calcolare l'età dell'account in giorni. preferred_contact_time utilizza TIME perché stai memorizzando un orario ricorrente come "chiamami alle 14:00" senza una data specifica.

Considerazioni sul fuso orario per i dati di data e ora

Un problema sottile ma critico nei dati temporali è la gestione del fuso orario. Quando registri che un evento si è verificato alle "2024-03-15 14:30:00", significa le 14:30 a New York, Tokyo o UTC? La risposta è importante perché la stessa ora locale ha significati diversi in fusi orari diversi.

La best practice consiste nel memorizzare tutti i timestamp in UTC (Coordinated Universal Time), un riferimento temporale indipendente dal fuso orario. Quando la tua applicazione riceve un evento da un utente in qualsiasi fuso orario, convertilo in UTC prima di memorizzarlo nel database. Questo approccio garantisce che tutti i timestamp siano confrontabili e che tu possa rispondere in modo univoco a domande come "quali eventi si sono verificati per primi?" o "quanto tempo è trascorso tra questi eventi?".

Alcuni database come PostgreSQL supportano TIMESTAMPTZ (timestamp con fuso orario), che memorizza sia il timestamp che le informazioni sul fuso orario associato. Quando recuperi i dati, il database riconverte il timestamp UTC nel fuso orario originale, se necessario. Questo approccio preserva il contesto del fuso orario originale garantendo al contempo la coerenza interna.

DATETIME di SQL Server e DATETIME di MySQL non includono informazioni sul fuso orario, quindi converti gli orari in UTC prima di memorizzarli e riconvertili quando li mostri agli utenti. Le impostazioni della sessione influiscono sul modo in cui i timestamp vengono interpretati in alcuni database, quindi documenta chiaramente le tue ipotesi.

Dati di tipo carattere e Unicode

I tipi di dati carattere memorizzano testo e sono disponibili in varianti a lunghezza fissa e a lunghezza variabile, ciascuna adatta a scenari diversi.

CHAR memorizza stringhe a lunghezza fissa e utilizza sempre l'intera lunghezza dichiarata, aggiungendo spazi se il valore effettivo è più breve. CHAR(10) occupa sempre esattamente 10 byte per riga, anche se inserisci "hello" (5 caratteri). CHAR eccelle quando quasi tutti i valori hanno la stessa lunghezza, come i codici ZIP degli Stati Uniti (5 cifre) o i codici paese (2 lettere). L'archiviazione a lunghezza fissa semplifica l'indicizzazione e rende le scansioni delle tabelle prevedibili in termini di dimensioni.

VARCHAR memorizza stringhe a lunghezza variabile e utilizza solo lo spazio necessario per i dati effettivi, oltre a un piccolo sovraccarico per registrare la lunghezza. VARCHAR(100) che memorizza "hello" occupa circa 7 byte (5 per "hello" più 2 per la codifica della lunghezza), risparmiando 93 byte rispetto a CHAR(100) sullo stesso valore. VARCHAR dovrebbe essere dimensionato tenendo conto dei dati reali: scegli VARCHAR(50) per i nomi solo se hai la certezza che i nomi non supereranno i 50 caratteri. Se i nomi sono in genere di 30 caratteri ma occasionalmente possono raggiungere i 50, VARCHAR(50) è una scelta prudente.

TEXT ospita grandi blocchi di testo non strutturato senza una lunghezza massima dichiarata. Utilizza TEXT per articoli, commenti o documenti che variano notevolmente in termini di dimensioni. Alcuni database distinguono tra TEXT e tipi più specializzati come CLOB (Character Large Object), ma la maggior parte dei sistemi moderni gestisce TEXT in modo efficiente con compressione interna e streaming.

Per il testo internazionale che contiene caratteri di più lingue, utilizza tipi compatibili con Unicode: varianti NVARCHAR o UTF8 a seconda del database. NVARCHAR (VARCHAR nazionale) in SQL Server memorizza testo codificato in UTF-16 che supporta qualsiasi carattere Unicode. PostgreSQL e MySQL supportano i set di caratteri UTF-8 direttamente in VARCHAR con le impostazioni di collation appropriate. Imposta sempre esplicitamente la codifica dei caratteri quando crei le tabelle per evitare comportamenti imprevisti in caso di modifica delle impostazioni predefini del database.

Esempi di definizioni di colonne stringa:

Qui, first_name e last_name utilizzano VARCHAR perché i nomi sono in genere brevi ma variabili, risparmiando spazio rispetto a CHAR. biography utilizza TEXT perché le biografie dei clienti possono essere qualsiasi cosa, da una singola frase a un intero paragrafo. country_code utilizza CHAR(2) perché tutti i codici paese sono composti esattamente da 2 lettere, rendendo appropriata l'archiviazione a lunghezza fissa.

Tipi di dati binari e stringhe binarie

I tipi di dati binari memorizzano dati binari grezzi (sequenze di byte) anziché testo. Questi tipi sono utili per memorizzare immagini, file, hash crittografici e altri contenuti non testuali.

BLOB (Binary Large Object) memorizza dati binari arbitrari senza un limite massimo di dimensioni. Utilizza BLOB per immagini, documenti PDF, video o qualsiasi contenuto binario non strutturato che non rientra nei tipi standard. BLOB è appropriato quando è necessario memorizzare file nel database, sebbene molti sistemi di produzione preferiscano memorizzare file di grandi dimensioni in sistemi di object storage come Amazon S3 e conservare solo i riferimenti ai file nel database.

VARBINARY memorizza dati binari a lunghezza variabile con una dimensione massima esplicita. VARBINARY(256) memorizza fino a 256 byte di dati binari, occupando solo lo spazio necessario per il contenuto effettivo. VARBINARY funziona bene per dati binari a dimensione fissa come firme crittografiche, checksum o UUIDs.

BINARY memorizza dati binari a lunghezza fissa, aggiungendo byte nulli se necessario. BINARY(16) occupa sempre esattamente 16 byte, utile per memorizzare identificatori a dimensione fissa come gli UUID a 128 bit. BINARY deve essere dimensionato con attenzione, poiché lo spazio sprecato a causa di dichiarazioni eccessivamente grandi riduce le prestazioni.

In pratica, memorizzare file di grandi dimensioni in un object storage invece che nei database è solitamente preferibile. L'object storage è più economico, più veloce per i file di grandi dimensioni e si scala più facilmente rispetto allo storage del database. Mantieni il riferimento al file e i metadati nel database, non il file stesso.

Esempi di definizioni di colonne binarie:

Qui, content utilizza BLOB per memorizzare i dati effettivi del documento. checksum utilizza VARBINARY per memorizzare un hash SHA-256 (32 byte) che verifica che il documento non sia stato corrotto. uuid utilizza BINARY(16) per memorizzare un identificatore UUID a dimensione fissa.

Considerazioni pratiche per i dati binari

L'indicizzazione delle colonne binarie è complessa perché i tradizionali indici B-tree presuppongono che i valori siano ordinabili e confrontabili. È possibile indicizzare le colonne BINARY per corrispondenze esatte, ma non per query di intervallo. Evita di indicizzare le colonne BLOB a meno che il database non disponga di indici bitmap o hash specializzati progettati per i dati binari.

Per il controllo dell'integrità dei dati sul contenuto binario, mantieni una colonna checksum separata che memorizza un hash dei dati binari. Se sospetti una corruzione, ricalcola l'hash e confrontalo con il valore memorizzato. Questo approccio è molto più rapido rispetto al riesame dell'intero contenuto binario.

Tipo di dati booleano e dati booleani

I tipi di dati booleani memorizzano valori vero/falso, essenziali per flag, indicatori di stato e decisioni sì/no. I valori vero e falso semplificano la modellazione dei dati ed evitano stati non validi come NULL o stringhe ambigue come "sì" o "1".

Database diversi implementano i booleani in modo diverso. PostgreSQL ha un tipo BOOLEAN nativo che accetta true/false, yes/no, on/off, 1/0 in vari formati. MySQL tratta BOOLEAN come un intero piccolo, associandolo a TINYINT(1), dove 1 rappresenta vero e 0 rappresenta falso. SQL Server utilizza BIT per dati di tipo booleano, memorizzando 1 per vero e 0 per falso utilizzando un singolo bit per valore (anche se lo storage effettivo varia).

Comprendere le differenze tra i provider è importante quando si migrano gli schemi tra database diversi. Un BOOLEAN di PostgreSQL non ha un equivalente diretto in SQL Server: al suo posto si utilizzerebbe BIT. Il codice dell'applicazione che presuppone l'input flessibile true/false di PostgreSQL (che accetta "yes", "on", "1") potrebbe non funzionare con i requisiti rigidi 0/1 di SQL Server.

Esempi di definizioni booleane:

Tutti e tre sono funzionalmente identici nel memorizzare valori vero/falso, ma i nomi dei tipi sottostanti differiscono e il comportamento di input/output varia leggermente.

Type casting e conversione

Il type casting converte un valore da un tipo di dati a un altro, ed è essenziale quando si devono combinare dati provenienti da fonti diverse o quando è necessario modificare il modo in cui un valore viene interpretato.

La conversione implicita avviene automaticamente quando il database converte i tipi per rendere possibile un'operazione. INSERT INTO table_name (int_column) VALUES ('123') potrebbe convertire implicitamente la stringa '123' nell'intero 123. La conversione implicita è comoda ma rischiosa: il database potrebbe eseguire conversioni non intenzionali o la conversione potrebbe non riuscire in modo invisibile, producendo risultati imprevisti.

La conversione esplicita tramite CAST o CONVERT offre un controllo preciso e rende chiare le intenzioni agli altri sviluppatori. Il casting esplicito previene sorprese impreviste e rende le prestazioni delle query più prevedibili.

Le conversioni comuni includono il casting di stringhe in numeri per i calcoli, il casting di numeri in stringhe per la concatenazione e il casting in DATE o TIMESTAMP per filtrare in base a intervalli temporali.

Esempio di utilizzo di CAST:

Queste conversioni esplicite rendono il codice chiaro: chiunque legga la query capisce immediatamente che è in corso una conversione e sa esattamente quale tipo viene prodotto.

Tipi di dati tra i diversi database

I tipi di dati SQL variano tra MySQL, PostgreSQL, SQL Server e Oracle. Sebbene i concetti fondamentali (numerico, carattere, data/ora, binario) siano universali, i nomi dei tipi specifici, la precisione e le caratteristiche di memorizzazione differiscono.

MySQL utilizza TINYINT per i valori booleani (associando BOOLEAN a TINYINT(1)), VARCHAR per le stringhe variabili e BLOB per i dati binari. PostgreSQL supporta BOOLEAN in modo nativo, TEXT per testi di grandi dimensioni senza limiti di dimensione e BYTEA per i dati binari. SQL Server utilizza INT e BIGINT come la maggior parte dei database, VARCHAR per le stringhe e IMAGE per dati binari di grandi dimensioni. Oracle ha NUMBER per i valori numerici, VARCHAR2 per le stringhe (non VARCHAR) e BLOB per i dati binari.

Queste differenze sono importanti quando si migrano gli schemi. Una colonna TEXT di PostgreSQL può contenere qualsiasi quantità di dati, ma TEXT di MySQL ha un limite di 64 KB e richiede LONGTEXT per contenuti più grandi. VARCHAR(MAX) di SQL Server è necessario per testi molto grandi, mentre TEXT di PostgreSQL lo gestisce direttamente. Il tipo NUMBER di Oracle è più flessibile rispetto ai tipi numerici della maggior parte dei database, consentendo di specificare la precisione e la scala in modo diverso.

Consulta la documentazione ufficiale del tuo database prima di progettare schemi destinati a essere portabili. Testa i dati effettivi con il sistema di database di destinazione per individuare i casi limite in cui le ipotesi sul comportamento dei tipi non sono valide.

Best practice per i tipi di dati

Preferisci tipi a precisione fissa come DECIMAL per i dati finanziari, evitando completamente i tipi a virgola mobile nei sistemi di contabilità o fatturazione. L'accuratezza finanziaria non è negoziabile e l'esattezza di DECIMAL vale il piccolo costo in termini di prestazioni. Le organizzazioni che creano sistemi di analytics su moderne piattaforme di data warehouse danno sempre più priorità alla corretta selezione dei tipi di dati come base per la governance e le prestazioni.

Evita di utilizzare stringhe per date o booleani, anche se tecnicamente possibile. La memorizzazione delle date come VARCHAR rende difficile l'aritmetica delle date, impedisce al database di ottimizzare le query basate sulle date e rende più difficile la convalida. La memorizzazione dei booleani come stringhe introduce ambiguità (ad esempio, "false" equivale a "no"?) e spreca spazio di archiviazione. Utilizza i tipi nativi DATE, TIMESTAMP e BOOLEAN, progettati appositamente per questi valori.

Esamina e ottimizza i tipi durante gli audit degli schemi, specialmente quando i database esistono da anni e i pattern di utilizzo sono cambiati. Una colonna definita come VARCHAR(1000) per motivi non più validi spreca spazio in ogni riga. Utilizza EXPLAIN PLAN o gli strumenti di analisi delle query del database per identificare le query lente causate da scelte errate dei tipi, quindi procedi al refactoring.

Documenta le tue scelte relative ai tipi, in particolare i casi limite e le ipotesi. Un commento che spiega perché una colonna è TINYINT invece di INT impedisce a qualcuno di modificarla in seguito sulla base di una comprensione incompleta. Questa documentazione è particolarmente importante per i tipi numerici in cui l'intervallo è rilevante.

FAQ

Qual è la differenza tra CHAR e VARCHAR?

CHAR memorizza stringhe a lunghezza fissa e utilizza sempre l'intera dimensione dichiarata, aggiungendo spazi di riempimento. VARCHAR memorizza stringhe a lunghezza variabile e utilizza solo lo spazio necessario per i dati effettivi. CHAR è più efficiente per i dati a dimensione fissa come i codici paese (sempre di 2 lettere), mentre VARCHAR è più efficiente per i dati a lunghezza variabile come i nomi. I tipi di dati stringa impongono regole sull'inserimento dei dati nelle colonne SQL e la scelta tra CHAR e VARCHAR influisce sia sullo storage che sulle prestazioni nel sistema di database.

Quando dovrei usare DECIMAL invece di FLOAT?

DECIMAL memorizza valori esatti ed evita errori di arrotondamento, rendendolo essenziale per i dati finanziari in cui la precisione è fondamentale. FLOAT memorizza valori approssimativi più velocemente ma introduce anomalie di arrotondamento. Utilizza DECIMAL per importi monetari, misurazioni scientifiche precise e calcoli in cui l'esattezza è verificabile. Utilizza FLOAT per approssimazioni, feature di machine learning e calcolo scientifico in cui piccoli errori sono accettabili. La scelta del tipo di dati corretto è fondamentale per l'integrità dei dati nei sistemi finanziari.

Come scelgo tra DATE e TIMESTAMP?

Utilizza DATE quando hai solo bisogno di registrare la data del calendario senza informazioni sull'ora, come la data di nascita di un cliente o la data di una transazione. Utilizza TIMESTAMP quando hai bisogno di informazioni temporali precise che includano ore, minuti e secondi, come i timestamp degli eventi o gli orari di completamento delle transazioni. I tipi di data e ora vengono utilizzati per registrare il momento in cui si verificano gli eventi e la selezione del tipo corretto semplifica le query ed evita sprechi di spazio di archiviazione.

Quali sono le lunghezze massime per i tipi di dati carattere?

Le lunghezze massime variano a seconda del database. VARCHAR in genere supporta lunghezze fino a 65.535 byte in MySQL, illimitate in PostgreSQL e fino a 8.000 byte in SQL Server (o VARCHAR(MAX) per valori più grandi). Controlla sempre la documentazione del tuo database specifico per i limiti esatti. La scelta di tipi appropriati può migliorare la scalabilità a lungo termine ed evitare di raggiungere limiti di archiviazione imprevisti.

Come dovrei gestire i fusi orari nel mio database?

Memorizza tutti i timestamp in UTC per garantire coerenza e comparabilità. Converti i timestamp in UTC prima di memorizzarli e riconvertili nel fuso orario locale dell'utente durante la visualizzazione. Alcuni database come PostgreSQL supportano TIMESTAMPTZ per gestire automaticamente questa conversione. Una gestione coerente dei fusi orari previene i bug nei calcoli basati sul tempo e rende univoco l'ordine degli eventi.

Quale tipo di dati dovrei usare per gli identificatori univoci?

I valori UUID in genere utilizzano BINARY(16) per l'archiviazione a dimensione fissa o CHAR(36) per la rappresentazione di stringhe standard, inclusi i trattini. Alcuni database come PostgreSQL supportano tipi UUID nativi. INT o BIGINT funzionano per gli ID numerici ad incremento automatico. Scegli in base al tuo schema di identificazione: gli ID numerici sequenziali sono semplici ma rendono più facile indovinare gli ID, mentre gli UUID sono casuali e adatti per i sistemi distribuiti.

Perché la scelta del tipo di dati è importante per le prestazioni delle query?

Tipi di dati più piccoli consentono di inserire più righe nella cache della CPU, velocizzando le query. Gli indici sono più efficaci su tipi numerici di dimensioni adeguate. L'efficienza di archiviazione riduce l'I/O del disco e migliora la latenza delle query. Le prestazioni delle query migliorano con l'uso di tipi di dati di dimensioni adeguate e la scelta del tipo più piccolo in grado di contenere i dati in modo sicuro mantiene i database veloci ed efficienti.

(Questo post sul blog è stato tradotto utilizzando strumenti basati sull'intelligenza artificiale) Post originale