Tipi di dati

Le colonne che possono essere definite in una tabella MySQL sono, ovviamente, di diversi tipi. Possiamo suddividerle in dati numerici, dati relativi a date e tempo, stringhe e dati geometrici.

Prima di tutto però dobbiamo ricordare che tutti i tipi di colonne possono contenere (se dichiarato nella loro definizione) il valore NULL, previsto dallo standard SQL per indicare un “non valore”, cioè il fatto che una certa colonna può non avere valore su alcune righe della tabella.

Dati numerici

Vediamo quali sono i tipi di dati numerici:

BIT[(M)]
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INT[(M)] [UNSIGNED] [ZEROFILL]
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

Le indicazioni comprese fra parentesi quadre sono opzionali. Come vedete, tutti i dati numerici escluso il BIT possono avere le opzioni UNSIGNED e ZEROFILL. Con la prima si specifica che il numero è senza segno, per cui non saranno consentiti valori negativi. Con la seconda si indica al server di memorizzare i numeri con degli zeri davanti nel caso in cui la lunghezza sia inferiore a quella massima prevista. Se usate ZEROFILL MySQL aggiungerà automaticamente UNSIGNED.

Il dato di tipo BIT è disponibile a partire da MySQL 5.0.3 per le tabelle MyISAM e dalla versione 5.0.5 per tabelle MEMORY, InnoDB e BDB. È un dato che contiene il numero di bit specificato con M (1 per default), che può andare da 1 a 64. Nelle versioni precedenti era considerato sinonimo di TINYINT(1). Un valore di questo tipo può essere indicato ad es. con b’111′, che rappresenta in questo caso tre bit a 1 (corrispondenti al valore decimale 7).

I dati di tipo TINYINTSMALLINTMEDIUMINTINT e BIGINT rappresentano numeri interi composti rispettivamente da 1, 2, 3, 4 e 8 bytes. Il TINYINT può contenere 256 valori, che vanno da -128 a +127 oppure da 0 a 255 nel caso di UNSIGNED. Allo stesso modo, SMALLINT può contenere 65536 valori, MEDIUMINT 16.777.216, INT oltre 4 miliardi, BIGINT circa 18 miliardi di miliardi.

In tutti i casi i valori massimi assoluti vanno dimezzati se non si usa UNSIGNED. Nel caso di BIGINT è però sconsigliato l’uso di UNSIGNED perchè può dare problemi con alcuni calcoli. L’indicazione del parametro M sugli interi non influisce sui valori memorizzabili, ma rappresenta la lunghezza minima visualizzabile per il dato. Se il valore occupa meno cifre, viene riempito a sinistra con degli spazi, o con degli zeri nel caso di ZEROFILL.

FLOAT e DOUBLE rappresentano i numeri in virgola mobile. M rappresenta il numero totale di cifre rappresentate e D il numero di cifre decimali.

FLOAT è a “precisione singola”: i suoi limiti teorici vanno da -3.402823466E+38 a -1.175494351E-38 e da 1.175494351E-38 a 3.402823466E+38, oltre allo zero.

I valori DOUBLE sono invece a “precisione doppia”: i limiti teorici sono da -1.7976931348623157E+308 a -2.2250738585072014E-308 e da 2.2250738585072014E-308 a 1.7976931348623157E+308, oltre allo zero.

Per entrambi i tipi di dato i limiti reali dipendono dall’hardware e dal sistema operativo. Se M e D non sono indicati i valori possono essere memorizzati fino ai limiti effettivi. Per questi dati l’uso di UNSIGNED disabilita i valori negativi, ma non ha effetto sui valori massimi positivi memorizzabili. La precisione dei numeri in virgola mobile è affidabile fino (circa) alla settima cifra decimale per i FLOAT e alla quindicesima per i DOUBLE. Una colonna FLOAT occupa 4 byte, una DOUBLE ne occupa 8.

I dati DECIMAL rappresentano infine numeri “esatti”, con M cifre totali di cui D decimali. I valori di default sono 10 per M e 0 per D. I valori limite per questi dati sono gli stessi di DOUBLE. Il massimo di cifre consentite è 65 per M e 30 per D. A partire da MySQL 5.0.3 questi dati vengono compressi in forma binaria.

Esistono numerosi sinonimi per i dati numerici: BOOL e BOOLEAN equivalgono attualmente a TINYINT(1), sebbene sia prevista in futuro l’introduzione di un vero dato booleano per MySQL. INTEGER equivale a INT. DOUBLE PRECISION equivale a DOUBLE. REAL equivale a DOUBLE (a meno che tra le opzioni dell’SQL mode – v. lez. 4 – non sia presenteREAL_AS_FLOAT). FLOAT(p) è un numero in virgola mobile la cui precisione in bit è indicata da p.

MySQL converte la dichiarazione in FLOAT o DOUBLE in base al valore di p: FLOAT da 0 a 24, DOUBLE da 25 a 53; in entrambi i casi la colonna risultante non avrà i valori M e D. Infine DECNUMERIC e FIXED sono sinonimi di DECIMAL.

Consultate la lezione 4 dove parla dell’SQL strict mode per verificare come vengono trattati da MySQL eventuali valori numerici non validi in fase di inserimento.

Date e tempo

Le colonne relative a date e tempo sono le seguenti:

DATE
DATETIME
TIMESTAMP[(M)]
TIME
YEAR[(2|4)]

Una colonna DATE può contenere date da ’1000-01-01′ (1° gennaio 1000) a ’9999-12-31′ (31 dicembre 9999). MySQL visualizza le date nel formato che vi abbiamo appena mostrato, ma vi consente di inserirle sotto forma di stringhe o numeri.

Una colonna DATETIME contiene una data e un’ora, con lo stesso range visto per DATE. La visualizzazione è nel formato ‘AAAA-MM-GG HH:MM:SS’, ma anche in questo caso possono essere usati formati diversi per l’inserimento.

Prima di MySQL 5.0.2 era sempre possibile inserire date o datetime a 0, oppure valorizzare a zero il giorno (o il giorno e mese) di una data. Era anche possibile indicare date non valide (ad es. ’1999-04-31′). A partire da MySQL 5.0.2 questi comportamenti sono controllati da alcuni valori di SQL mode (v.lez.4):

  • ALLOW_INVALID_DATES è necessario per consentire date non valide: in sua assenza, le date non valide in strict mode provocheranno un errore; senza strict mode verranno convertite a 0 con un warning;
  • NO_ZERO_DATE non accetta date a 0 (’0000-00-00′): in strict mode verrà causato un errore a meno che non sia usata IGNORE; senza strict mode saranno comunque accettate con un warning;
  • NO_ZERO_IN_DATE non accetta valori 0 per giorno e mese: in strict mode verrà generato errore, oppure inserita una data a 0 con IGNORE; senza strict mode saranno accettati con un warning

In un TIMESTAMP possono essere memorizzati i valori corrispondenti al timestamp Unix, che vanno dalla mezzanotte del 1° gennaio 1970 ad un momento imprecisato dell’anno 2037.

Questo tipo di dato è utile per memorizzare automaticamente il momento dell’aggiornamento di una riga di tabella: infatti MySQL può impostare in automatico una colonna TIMESTAMP di una tabella nel momento in cui viene effettuata una INSERT o un UPDATE. La visualizzazione del timestamp avviene nello stesso formato del DATETIME; è possibile ottenerlo in formato numerico aggiungendo un +0 alla colonna nella SELECT.

Fino a MySQL 4.0 le caratteristiche del timestamp erano diverse da quelle attuali. Innanzitutto veniva visualizzato in formato numerico, ed esisteva la possibilità di determinare il numero di cifre visualizzate indicando il valore di M. Da MySQL 4.1 in poi ciò non è più possibile. Inoltre la funzione di aggiornamento automatico era possibile solo per la prima colonna definita come TIMESTAMP in ogni tabella. Ora invece è possibile avere questo funzionamento anche per una colonna successiva alla prima.

Vediamo la possibile definizione di una colonna TIMESTAMP:

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP

Con questa dichiarazione, la colonna viene automaticamente inizializzata e successivamente aggiornata ad ogni modifica della riga con il timestamp del momento. Se omettete una delle due dichiarazioni, solo l’altra sarà attiva; ovviamente per la clausola DEFAULT potete anche fornire un valore costante.

Se omettete entrambe le dichiarazioni sulla prima colonna, questa sarà comunque automaticamente inizializzata e aggiornata. Se volete usare i valori automatici su una colonna successiva alla prima, dovete disabilitare questo comportamento per la prima colonna usando un valore esplicito di default (ad es. DEFAULT 0), e indicare le clausole viste sopra per la colonna che vi interessa. In questo caso l’omissione di entrambe non darà luogo all’inizializzazione e all’aggiornamento automatici.

Quando inserite un valore in un timestamp indicando NULL, se la colonna non supporta valori NULL verrà inserito il CURRENT_TIMESTAMP. Se usate l’SQL mode MAXDB tutte le colonne TIMESTAMP saranno convertite in DATETIME.

Una colonna TIME contiene un valore di tempo (ore, minuti e secondi) che va da ‘-838:59:59′ a ’838:59:59′. Anche qui la visualizzazione avviene nel formato indicato, ma è possibile usare formati diversi per l’inserimento.

Infine la colonna YEAR rappresenta, su quattro cifre, un anno compreso fra 1901 e 2155, oppure 0000. Su due cifre invece i valori vanno da 70 (1970) a 69 (2069).

I valori relativi al tempo possono essere inseriti sia come stringhe che come numeri, e MySQL vi consente di utilizzare, nel caso delle stringhe, molti caratteri diversi come separatori.
L’importante però è che l’ordine dei valori sia sempre anno-mese-giorno-ore-minuti-secondi. Quando usate i separatori nelle stringhe potete anche omettere gli zeri non significativi (ad es. è consentito ’2005-9-21′ ma dovete usare ’20050921′).

Stringhe

Le colonne di tipo stringa, a partire da MySQL 4.1, possono avere un attributo CHARACTER SET che indica l’insieme di caratteri utilizzato per la colonna, e un attributo COLLATE che indica la collation relativa. Vediamo un esempio:

CREATE TABLE tabella
(
    c1 CHAR(20) CHARACTER SET utf8,
    c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);

In questa tabella abbiamo la colonna c1 definita col set di caratteri utf8 e la relativa collation di default; e la colonna c2 col set di caratteri latin1 e la relativa collation binaria. La lunghezza specificata è relativa al numero di caratteri (il numero di byte infatti può variare in base ai set di caratteri usati e al contenuto della colonna).

Vediamo i tipi di campi previsti:

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
[NATIONAL] VARCHAR(M) [BINARY]
BINARY(M)
VARBINARY(M)
TINYBLOB
TINYTEXT
BLOB[(M)]
TEXT[(M)]
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM(‘valore1′,’valore2′,…)
SET(‘valore1′,’valore2′,…)

CHAR è una stringa di lunghezza fissa (M) riempita con spazi a destra al momento della memorizzazione, che vengono eliminati in fase di lettura. La lunghezza prevista va da 0 a 255 caratteri. L’opzione NATIONAL indica che la stringa deve usare il set di caratteri di default. L’attributo BINARY indica che deve essere usata la collation binaria del set di caratteri utilizzato. ASCII assegna il character set latin1, UNICODE assegna ucs2.

CHAR BYTE equivale a CHAR BINARY. Notate che se una riga ha lunghezza variabile (cioè se almeno una colonna è definita a lunghezza variabile) qualsiasi campo CHAR di lunghezza superiore a 3 caratteri viene convertito in VARCHAR.

VARCHAR è una stringa a lunghezza variabile; le sue caratteristiche sono variate a partire da MySQL 5.0.3: in precedenza infatti la lunghezza massima era 255 e gli spazi vuoti a destra venivano eliminati in fase di memorizzazione; ora invece ciò non avviene più e la lunghezza massima dichiarabile è salita a 65535 caratteri. Gli attributi NATIONAL e BINARY hanno lo stesso significato visto in CHAR. Se definite una colonna VARCHAR con meno di 4 caratteri sarà trasformata in CHAR.

BINARY e VARBINARY corrispondono a CHAR e VARCHAR, ma memorizzano stringhe di byte invece che di caratteri. Non hanno quindi character set. I valori BINARY ricevono un riempimento a destra di byte 0×00 a partire da MySQL 5.0.15; in precedenza il riempimento era a spazi e veniva rimosso in fase di lettura. Nei valori VARBINARY, fino a MySQL 5.0.3 gli spazi finali venivano rimossi in lettura.

I formati di tipo BLOB e TEXT sono utilizzati rispettivamente per valori binari e di testo.
La lunghezza massima è 255 caratteri per TINYBLOB e TINYTEXT, 65535 per BLOB e TEXT, 16.777.215 per MEDIUMBLOB e MEDIUMTEXT, 4 gigabyte per LONGBLOB eLONGTEXT.

Per queste ultime però bisogna tenere presenti i limiti del packet size nel protocollo client/server nonchÈ quelli della memoria. È possibile anche dichiarare una colonna BLOB o TEXT specificando una lunghezza in byte: in questo caso il server sceglierà il tipo più piccolo in grado di contenere i caratteri richiesti (ad es. con BLOB(100000) verrà creato un MEDIUMBLOB).

Se cercate di inserire un valore troppo lungo nei campi, con strict mode avrete un errore; senza strict mode il valore sarà troncato a destra e ci sarà un warning se i caratteri troncati non sono spazi.

Una colonna ENUM può contenere uno dei valori elencati nella definizione, oppure NULL o una stringa vuota, che viene assegnata quando si cerca di inserire un valore non valido. I valori possibili possono essere fino a 65535.

Una colonna SET, come la ENUM, prevede un insieme di valori possibili (fino a 64), ma in questo caso la colonna può assumere anche più di un valore, oppure nessuno.

Dati geometrici

I dati geometrici sono stati introdotti con la versione 4.1 di MySQL, e si basano sulle specifiche dell’Open GIS Consortium. Dovreste conoscere il Modello Geometrico proposto da tale ente per poter utilizzare con proprietà questi dati. Sul manuale di MySQL troverete informazioni sul modello e rimandi ad altri siti web sull’argomento.

Qui vediamo quali sono i tipi di dati geometrici previsti da MySQL:

GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION

Il significato di ogni dato è piuttosto intuitivo: GEOMETRY può contenere un valore geometrico generico; POINT contiene un punto, LINESTRING una linea, POLYGON un poligono. GEOMETRYCOLLECTION rappresenta un insieme di dati geometrici di qualsiasi tipo, mentre gli altri tre sono insiemi di dati del tipo relativo.

Questi dati sono disponibili sulle tabelle MyISAM e, a partire da MySQL 5.0.16, anche su tabelle InnoDB, BDB e ARCHIVE.

Both comments and pings are currently closed.

Comments are closed.