Il tipo di campo più importante dal punto di vista dell?utente in materia di database è rappresentato dalle stringhe, utili per immagazzinare descrizioni comprensibili. MySQL, come ogni altro database server, è in grado di gestire molteplici campi di testo ed applicarvi un gran numero di funzioni integrate: in questo articolo vedremo come gestire questa potenzialità.
I campi di tipo testuale
MySQL supporta principalmente due tipi di campi testuali a cui ne vanno aggiunti altri che presentano alcune particolarità.
CHAR e VARCHAR
Il CHAR è un campo a dimensione fissa, vengono utilizzati tanti bytes quanti sono i caratteri massimi per i quali è stato impostato il campo: se il campo viene impostato a CHAR(5), con la possibilità cioè di salvare 5 caratteri, le stringhe 'abaco' e 'ape' occuperanno lo stesso spazio nel database.
Il VARCHAR in maniera opposta utilizza per la memorizzazione un numero di bytes pari al numero di caratteri salvati più un byte per la dimensione della stringa. I campi VARCHAR, pur presentando il vantaggio di occupare un minor spazio in termini di bytes, impediscono alla tabella di diventare ?fixed" e ne rallentano quindi l'utilizzo. Una tabella può essere impostata come ?fixed" solo se tutti i campi sono di tipo numerico o CHAR.
È possibile immagazzinare fino a 255 caratteri ed è necessario specificare la dimensione del campo al momento della creazione dello stesso; se il dato immesso superasse la dimensione prefissata verrebbe troncato. Esempio: La stringa 'Alberto' inserita in un campo CHAR(5) verrà immagazzinata come 'Alber'.
I due tipi di campo differiscono anche per l'applicazione della funzione trim, ovvero l'eliminazione di caratteri di spaziatura ad inizio o fine di riga: VARCHAR elimina gli spazi in fase di inserimento dei dati, CHAR nel momento in cui vengono richiamati.
Tipo del campo | CHAR(9) | VARCHAR(9) | CHAR(9) | VARCHAR(9) | Dato inserito | 'Cane ' | 'Cane ' | 'Astronave ' | 'Astronave ' | Bytes usati | 9 | 5 | 9 | 10 | Dato memorizzato | 'Cane ' | 'Cane' | 'Astronave' | 'Astronave' | Dato restituito | 'Cane' | 'Cane' | 'Astronave' | 'Astronave' |
TEXT (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT)
Questo tipo di campo è molto simile al VARCHAR ma se ne differenzia per il numero di caratteri che è possibile memorizzare. Ha quattro differenti "sottotipi":
- TINYTEXT fino a 2^8 caratteri (256 bytes)
- EXT fino a 2^16 caratteri (oltre 65000, 64 Kbytes)
- MEDIUMTEXT fino a 2^24 caratteri (oltre 16 milioni di bytes, 16 Mbytes)
- LONGTEXT fino a 2^32 caratteri (oltre 4 miliardi di bytes, 4 Gbytes)
Generalmente impostando un campo come TEXT non dovreste avere alcun tipo di problema a meno che non abbiate necessità di inserire enormi moli di dati. C'è da considerare inoltre che questi limiti teorici vanno ridimensionati nell'uso reale a causa del passaggio di dati tra client e server (ad esempio tra uno script php e il database) che è a sua volta limitato dall'opzione max_allowed_packet impostato generalmente ad un Mbyte e del limite fisico della dimensione dei file che dipende dal sistema operativo (è da ricordare infatti che una tabella di mysql è salvata nel filesystem come un file qualsiasi).
BLOB
Questo è il classico campo per la memorizzazione di dati binari e viene utilizzato per memorizzare nel database immagini, documenti, eseguibili e file in genere. Per approfondire l'argomento potete leggere l'articolo di Giancarlo Moschitta Files dentro a MySQL. Anche in questo caso sono presenti quattro "sottotipi" (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB) e la limitazione sulla dimensione è la stessa degli equivalenti campi di tipo TEXT.
ENUM/SET
Altri due campi che possono contenere stringhe sono ENUM e SET che vengono utilizzati nei casi in cui la stringa inserita debba fare parte di un ristretto cerchio di opzioni specificate in fase di creazione del campo. Il vantaggio di questi tipi di campo è dato dal fatto che per ogni record inserito verrà utilizzato solo un byte (o due se si superano i 256 elementi) per memorizzare la chiave numerica corrispondente al valore desiderato.
ENUM consente di specificare oltre 65000 elementi (2^16) e viene utilizzato per opzioni prefissate. Ad esempio si può impostare un campo ENUM con tre elementi ('Mucca', 'Pecora', 'Cavallo'), a questo punto per quel campo ad ogni record non verrà salvata l'intera stringa, ma solamente un numero corrispondente alla stessa; nel nostro caso NULL per NULL, 0 per una stringa vuota (""), 1 per 'Mucca', 2 per 'Pecora' e 3 per 'Cavallo'.
SET è utilizzato invece in quei casi in cui i valori si possono presentare contemporaneamente (l'esempio classico è dato dai permessi su un file). A causa della modalità in cui vengono salvati i valori inseriti, essi non possono contenere virgole. Il campo può contenere fino ad un massimo di 64 specificazioni.
Considerazioni sui campi di testo
Case sensitive o unsensitive?
I campi stringa di MySQL sono gestiti in maniera case unsensitive dalla versione 3.23, questo significa che da quella versione le stringhe 'Milano' e 'MILANO' vengono considerate uguali anche se vengono salvate nelle rispettive forme originarie. Per far sì che vengano considerate le differenze tra le due forme è necessario usare la parola chiave BINARY in fase di query. Esempio: Regione, capoluogo = 'Lombardia', 'Milano'.
SELECT regione FROM regioni WHERE BINARY capoluogo = 'Milano'; // una corrispondenza
SELECT regione FROM regioni WHERE BINARY capoluogo = 'MILANO'; // nessuna corrispondenza
La codifica di memorizzazione
Dalla versione 4.1 MySQL permette di specificare la codifica da utilizzare per una specifica tabella e addirittura per ogni singola colonna; questo può essere molto utile nel caso in cui si lavori in un sistema multilingua. Per maggiori informazioni su questa caratteristica, che comunque nella maggior parte dei casi non comporta alcun cambiamento, si rimanda al manuale ufficiale.
Le funzioni stringa
Molte delle funzioni che agiscono sulle stringhe che utilizziamo in php sono disponibili già a livello di query per MySQL. I vantaggi nell'utilizzo delle funzioni a livello di query sono l'estrema velocità dell'elaborazione fornita da MySQL e il fatto che il motore è in grado di decidere da solo a quali record è necessario applicare una funzione e per quali questo è superfluo, ad esempio non verrà applicata una funzione per rendere la stringa minuscola se questa è già minuscola, e quindi la funzione viene eseguita soltanto dove veramente necessario.
Oltre a questo va considerato il Ottimizzare l'uso di MySQL con PHP fornito dalle versioni 4.0 di MySQL che permette di avere un veloce accesso ai dati già prelevati da una precedente query. Per contro le query saranno difficilmente portabili su altre piattaforme, per cui le applicazioni dovranno essere progettate unicamente per girare su MySQL.
Facciamo velocemente una carrellata delle principali funzioni che possono tornare utili raggruppandole per categoria.
Ricerca e sostituzione
INSTR(pagliaio, ago)
Equivalente in php: strpos($pagliaio, $ago)+1
Questa funzione dà come risultato la posizione della sottostringa ago nella stringa pagliaio. Da notare che il valore varia da 1 alla lunghezza della stringa e che 0 viene restituito quando non c'è corrispondenza. In pratica restituisce sempre un'unità in meno di quello che restituirebbe la funzione analoga in php.
SELECT INSTR('Ballatoio', 'io') FROM tabella; // 8
LOCATE(ago, pagliaio[, inizio])
Equivalente in php: strpos($pagliaio, $ago[, $inizio])+1
Questa funzione dà come risultato la posizione della sottostringa ago nella stringa pagliaio partendo dal carattere inizio. Da notare che l'ordine della stringa in cui cercare e della sottostringa da cercare sono invertiti rispetto alla funzione in php e alla funzione INSTR e che inoltre il valore varia da 1 alla lunghezza della stringa. 0 viene restituito quando non c'è corrispondenza. In pratica restituisce sempre un'unità in meno di quello che restituirebbe la funzione analoga in php.
SELECT LOCATE('io', 'Ballatoio') FROM tabella; // 8
REPLACE(stringa, cosa, concosa)
Equivalente in php: str_replace($cosa, $concosa, $stringa)
Questa funzione restituisce la stringa passata come primo argomento a cui sono state sostituite tutte le occorrenze di cosa con concosa. Attenzione all'ordine diverso dei parametri rispetto alla funzione in php.
SELECT REPLACE('Ballatoio', 'io', 'ao') FROM tabella; // Ballatoao
INSERT(stringa, inizio, lunghezza, sostituto)
Equivalente in php: substr_replace($stringa, $sostituto, $inizio[, $lunghezza])
Questa funzione restituisce la stringa passata come primo parametro dove i caratteri da inizio e lunghezza sono stati sostituiti con la stringa sostituto. Attenzione all'ordine diverso dei parametri rispetto alla funzione php e alla necessità di specificare tutti e quattro i parametri.
SELECT INSERT('Ballatoio', 8, 2, 'ao') FROM tabella; // Ballatoao
Sottostringhe
LEFT(stringa, lunghezza)
Equivalente in php: substr($stringa, 0, $lunghezza)
Questa funzione restituisce una sottostringa composta dai primi lunghezza caratteri di stringa.
SELECT LEFT('Ballatoio', 2) FROM tabella; // Ba
RIGHT(stringa, lunghezza)
Equivalente in php: substr($stringa, -$lunghezza)
Questa funzione restituisce gli ultimi lunghezza caratteri di stringa.
SELECT RIGHT('Ballatoio', 2) FROM tabella; // io
SUBSTRING(stringa, inizio, lunghezza)
Equivalente in php: substr($stringa, $inizio, $lunghezza)
Questa funzione restituisce una sottostringa di lunghezza caratteri a cominciare dal carattere in posizione inizio.
SELECT SUBSTRING('Ballatoio', 2, 4) FROM tabella; // llat
SUBSTRING_INDEX(stringa, pattern, inizio)
Equivalente in php: substr($stringa, strpos($stringa, $pattern, $inizio))
Questa funzione restituisce una sottostringa di stringa a cominciare dalla prima occorrenza di pattern.
SELECT RIGHT('Ballatoio', 'a', 0) FROM tabella; // llatoio
Modifica di stringa
LOWER(stringa)
Equivalente in php: strtolower($stringa)
Questa funzione restituisce stringa con tutti i caratteri maiuscoli trasformati in minuscoli.
SELECT LOWER('BaLLatoio') FROM tabella; // ballatoio
UPPER(stringa)
Equivalente in php: strtoupper($stringa)
Questa funzione restituisce stringa con tutti i caratteri minuscoli trasformati in maiscoli.
SELECT UPPER('BaLLatoio') FROM tabella; // BALLATOIO
LTRIM(stringa)
Equivalente in php: ltrim($stringa)
Questa funzione restituisce stringa eliminando gli spazi a sinistra.
SELECT LTRIM(' Ballatoio ') FROM tabella; // ?Ballatoio ?
RTRIM(stringa)
Equivalente in php: rtrim($stringa)
Questa funzione restituisce stringa eliminando gli spazi a destra.
SELECT RTRIM(' Ballatoio ') FROM tabella; // ? Ballatoio"
TRIM([(BOTH|LEADING|TRAILING) [carattere] FROM] stringa)
Equivalente in php: trim($stringa[, carattere])
Questa funzione restituisce stringa eliminando carattere dalla stessa a sinistra se è specificato LEADING, a destra se è specificato TRAILING e da entrambe le parti se è specificato BOTH o niente. Se carattere non è passato viene considerato lo spazio come elemento da eliminare. Attenzione alla notazione particolare di questa funzione.
SELECT TRIM('o' FROM 'Ballatoio') FROM tabella; // Ballati
Altre
CHAR_LENGHT(stringa)
Equivalente in php: strlen($stringa)
Questa funzione restituisce il numero di caratteri di cui è composta stringa. Da notare bene il funzionamento diverso della funzione LENGHT che restituisce la dimensione in bytes e non i caratteri, falsando i risultati nel caso in cui si utilizzino caratteri multibytes.
SELECT CHAR_LENGHT('Ballatoio') FROM tabella; // 9
CONCAT(stringa1, stringa2, stringa3, ...)
Equivalente in php: $stringa1 . $stringa2 . $stringa3
Questa funzione permette di concatenare due o più stringhe.
SELECT CONCAT('Bal', 'la', 'to', 'io') FROM tabella; // Ballatoio
CONCAT_WS(colla, stringa1, stringa2, stringa3)
Equivalente in php: implode($colla, array($stringa1, $stringa2, $stringa3))
Questa funzione permette di creare un'unica stringa composta dalle stringhe passate dal secondo parametro in poi separandole da un divisore indicato dal primo parametro.
SELECT CONCAT_WS('.', 'Bal', 'la', 'to', 'io') FROM tabella; // Bal.la.to.io.
