Tipi di dato

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:

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
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'
bit a 1 (corrispondenti al valore decimale 7).

I dati di tipo TINYINT SMALLINT
MEDIUMINT INT
BIGINT
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 DOUBLE
in virgola mobile. M
rappresentate e D

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 D
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
M D
sono 10 per M D
dati sono gli stessi di DOUBLE. Il massimo di cifre consentite è 65 per
M D
vengono compressi in forma binaria.

Esistono numerosi sinonimi per i dati numerici: BOOL
BOOLEAN
sia prevista in futuro l'introduzione di un vero dato booleano per MySQL.
INTEGER DOUBLE
PRECISION REAL
DOUBLE (a meno che tra le opzioni dell'SQL mode - v. lez. 4 - non sia
presente REAL_AS_FLOAT FLOAT(p)
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
colonna risultante non avrà i valori M D
DEC NUMERIC FIXED

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:

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

Una colonna DATE
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
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
  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
  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
  strict mode verrà generato errore, oppure inserita una data a 0 con
  IGNORE; senza strict mode saranno accettati con un warning

In un TIMESTAMP
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

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
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
in DATETIME.

Una colonna TIME
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

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:

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 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 ucs2

CHAR BYTE
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
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 VARBINARY
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
TINYTEXT BLOB
TEXT MEDIUMBLOB
MEDIUMTEXT LONGBLOB
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
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

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:

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.