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 TINYINT, SMALLINT,
MEDIUMINT, INT 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
presente REAL_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
DEC, NUMERIC 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 0x00 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 e
LONGTEXT.
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 e ARCHIVE.