Torna all'elenco degli articoli

Ricerche su un database MYSQL
scritto da ghiaccio il 26/06/2007

Spessissimo si ha la necessità di dover ricercare in un database degli elementi e molte volte la quantità di dati presenti nel database può essere molto grande per esempio come in catalogo di foto, libri ecc.

Corre in nostro aiuto la ricerca full-text che permette di effettuare una ricerca con una serie di parametri per escludere parole, dare più rilevanza ad altre e molto altro (BOOLEAN MODE).
Non per ultimo, questo tipo di ricerca permette di ordinare i risultati rispetto alla loro rilevanza!
Infatti il comando che effettua la ricerca full-text


oltre ad essere usato ovviamente nella clausola WHERE della query SQL, viene usato nella clausola ORDER BY.
Tutto questo funziona perchè questo comando restituisce non restituisce solo due possibili valori come accade con il LIKE (ovvero 0 ed 1), bensì un numero decimale che rappresenta la rilevanza della corrispondeza.
Questa rilevanza può essere 0 (corrispondenza nulla) o qualcosa di superiore, ed ordinando per questo valore si ottiene l'effetto desiderato.
Anche sul WHERE, una condizione che testa lo 0 significa il non superamento della condizione mentre con un numero maggiore di 0 (anche 0,01) la condizione viene superata.

Attenzione, questo articolo non vuole essere un tutorial sul funzionamento delle ricerce full-text in MySql.
Per eventuali chiarimenti al riguardo vi rimando alla pagina di spiegazione delle funzioni full-text del sito di MySql .

Ci sono però casi in cui la ricerca full-text non aderisce alle nostre esigenze.

Innanzitutto, dal punto di vista tecnico è possibile creare indici FullText solo su campi di tabelle MyISAM e, pur essendo molto performanti.
Questo tipo di tabelle, ad esempio, non consente la creazione delle utili Foreign Key (chiavi esterne) utilizzate per far mantenere automaticamente dal DBMS i vincoli di integrità referenziale.

Dal un punto di vista più pratico invece bisogna considerare che il parser full-text prende in considerazione come delimitatori la virgola, il punto e lo spazio ma ci possono essere casi in cui vorremmo cercare una precisa parola contenente il punto ad esempio una sigla come v.i.p od un codice di un oggetto di un catalogo come cod.12345.

Inoltre, c'è un problema più fastidioso: la ricerca esclude automaticamente le parole più corte di 4 caratteri.
Questo penalizza ad esempio la ricerca della stringa 'vip italiani' tenendo in considerazione la sola parola italiani.
Peggio ancora se si cerca solo 'vip' non si otterrà nessun risultato anche se la tabella ne contiene molte ricorrenze.

Per concludere la lista di svantaggi, bisogna ricordare che la ricerca full-text esclude a priori una lista di parole inglesi comunemente usate, chiamate stopwords.
Lo svantaggio è che tali parole potrebbero essere proprio ciò che si cerca, ad esempio 'five' (il gruppo musicale), ed in qualche caso sono parole che hanno un significato anche in italiano oltre ad essere parole comunemente usate anche in italia, ad esempio 'come' che in inglese significa venire ma in italiano ha un significato letterale oppure 'zero' che ha lo stesso significato nelle due lingue.

La lunghezza minima dei caratteri (4 per default) e la lista delle stopwords possono essere modificati a partire dalla versione 5 di MySql se non che sul sito di MySql consigliano di non farlo se non siete veri esperti in quanto nella maggior parte dei casi potreste peggiorare le prestazioni.
Oppure più semplicemente potreste non aver la possibilità di modificare questi valori sul vostro hosting.

Insomma, ad ogni modo, se volete evitare i problemi sopra elencati la soluzione è: tornare al buon vecchio LIKE !

Personalmente nel codice che ho scritto per il mio CMS, uso le ricerche full-text, ma passo al LIKE quando mi accorgo che ogni parola (suddivisa tenendo conto dei delimitatori full-text) da ricercare è più corta di 4 lettere e sò che il risultato sarà una ricerca senza risultati.

Con il LIKE, è più semplice capire perchè una ricerca non va a finire nel modo desiderato, mentre con full-text bisogna prendere in considerazione molti fattori e magari perdere anche un bel pò di tempo.

Inoltre non sì può creare un indice full-text (e quindi poter effettuare le ricerche full-text) su tutti i campi, ma solo su campi (VAR)CHAR e (LONG/MEDIUM/TINY)TEXT.
In effetti sarebbe assurdo usare un meccanismo di ricerca così sofisticato su dei numeri o delle date.

Il LIKE è molto comodo perchè permette di usare di poter cercare delle parole con un certo ordine all'interno del testo oppure che inizino con una determinata sequenza di lettere ecc.
In pratica la sintassi del LIKE permette di utilizzare all'interno del testo i due caratteri speciali % e _
Tali caratteri consentono la costruzione di una stringa da cercare che è una sorta di espressione regolare , in cui si possono indicare le sequenze di qualsiasi carattere di lunghezza arbitraria (%) o di lunghezza 1 (_).
Tutto ciò è utile anche se non molto avanzato in fatto di flessibilità.

In realtà esiste un altro operatore di MySql che permette un tipo di ricerca utilizzando delle vere e proprie espressioni regolari ovvero l'operatore REGEXP.
Questo operatore che si applica in modo simile al LIKE (campo REGEXP 'valore') permette di delle espressioni regolari in stile POSIX e quindi potete rendervi conto della potenza di questo strumento se ben utilizzato.
Per maggiorni informazioni, accedete alla pagina di spiegazione dell'operatore REGEXP di MySql.

Bisogna però avere alcune piccole accortezze con il LIKE ed a maggior ragione con REGEXP in quanto il testo da ricercare può contenere i caratteri che hanno un significato speciale, a cui bisogna necessariamente applicare l'eadeguato escape.
Ad esempio se si vuol cercare la frase "pagamento del 30% anticipato" e non si esegue l'escape la query


restituirà valori non desiderati come "....pagamento del 30/09/2007 anticipato...."
Lo stesso discorso vale per REGEXP in cui gli effetti sono sicuramente più dannosi.

Nei valori da cercare bisogna quindi eseguire l'escape dei caratteri % , _ per il LIKE e di ? , . , [ , ] ,  ( , ) , |, { , } , $ , ^ , * per REGEXP

Se usate PHP non fatevi trarre trarre in inganno dal nome delle funzioni mysql_escape_string() e mysql_real_escape_string() : esse non eseguono l'escape dei caratteri sopra mensionati. Dovete farlo da soli.

In particolare per i caratteri di cui eseguire l'escape per il LIKE, basta anteporre il backslash \ al carattere ( \% , \_ ) mentre per i caratteri speciali di REGEXP è richiesto il doppio backslash ( \\. , \\? ecc.)

Concludo con un ultimo consiglio riguardante la ricerca di un valore contenente più valori separati da un separatore.
Questi campi sono di solito campi di tipo SET in cui si possono memorizzare molti valori tra quelli impostati separati da virgola oppure campi che contengono serie di id numerici che collegano ad altre tabelle (questo stile non è molto elegante ma talvolta viene utilizzato).

Si potrebbe affrettare una query del tipo

ma questo esclude inevitabilmente i casi in cui il valore sia all'inizio della lista, alla fine, oppure sia il solo valore nel campo.

Una corretta query è invece



che contampla tutti i possibili casi.
La query è però alquanto lunghina e difficile da gestire.
Potremmo quindi sfruttare l'operatore REGEXP.


Il risultato è identico.

Il metacarattere ^ corrisponde all'inizio del contenuto del campo, mentre $ corrisponde alla fine del testo.
Il metacarattere . significa qualsiasi carattere mentre l'operatore * a destra di un singolo carattere, in questo caso del punto, o di una sotto-espressione chiusa tra parentesi tonde, indica che la sua parte sinistra deve ripetersi zero o più volte, il + indica che la sequenza deve ripetersi una o più volte, ? indica che deve ripetersi al più una volta.
Quindi ^(.*,)?valore(,.*)?$ corrisponde ad un testo formato per prima cosa da una sequenza di qualsiasi carattere seguita da una virgola, che può esserci o meno (ovvero (.*,)? ), poi il valore da cercare e un'altra sequenza come quella descritta, che può esserci o meno (ancora (.*,)? ).
 







Lista commenti