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
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.
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’,…)
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.
in questo caso la colonna può assumere anche più di un valore, oppure nessuno.