MySQL Espressioni regolari (Regexp)

Che cosa sono MySQL espressioni regolari?

MySQL espressioni regolari Ti aiutano a cercare dati che corrispondono a criteri complessi. Un'espressione regolare è un modello che descrive la forma del valore che stai cercando, piuttosto che il valore stesso.

Se hai già lavorato con MySQL jollyPotresti chiederti perché valga la pena imparare le espressioni regolari quando LIKE produce risultati simili. La risposta sta nella potenza espressiva: i caratteri jolly offrono solo due simboli, mentre le espressioni regolari descrivono intervalli di caratteri, alternative, ripetizioni e posizioni delle parole in un unico schema.

Una volta chiarito lo scopo, la sezione successiva introduce la sintassi che utilizzerete in ogni query REGEXP.

Sintassi di base delle espressioni regolari (REGEXP)

La sintassi di base per un'espressione regolare è la seguente.

SELECT * FROM table_name
WHERE fieldname REGEXP 'pattern';

QUI:

• "Istruzione SELECT" è lo standard Istruzione SELECT.
• “DOVE nome campo” è il nome della colonna su cui viene eseguita l'espressione regolare.
• “Modello REGEXP”” — REGEXP è l'operatore di espressione regolare e 'pattern' rappresenta il modello da confrontare. RLIKE è un sinonimo di REGEXP e restituisce gli stessi risultati. Per evitare di confonderlo con l'operatore LIKE, è meglio usare REGEXP.

Vediamo ora un esempio pratico.

SELECT * FROM `movies`
WHERE `title` REGEXP 'code';

La query sopra riportata cerca tutti i titoli dei film che contengono la parola "code". Non importa se "code" compare all'inizio, al centro o alla fine del titolo. Finché il titolo contiene il pattern, viene restituita la riga.

Corrispondenza tra l'inizio di un valore e un elenco di caratteri

Supponiamo di voler selezionare i film i cui titoli iniziano con le lettere a, b, c o d, seguite da un numero qualsiasi di altri caratteri. Per ottenere questo risultato, combiniamo un elenco di caratteri con il metacarattere accento circonflesso.

SELECT * FROM `movies`
WHERE `title` REGEXP '^[abcd]';

Eseguendo lo script precedente in MySQL banco di lavoro Il confronto con il database myflixdb ci fornisce i seguenti risultati.

Nel modello '^[abcd]', l'accento circonflesso (^) richiede che la corrispondenza inizi dall'inizio del valore e l'elenco di caratteri [abcd] accetta solo titoli la cui prima lettera è a, b, c o d. Il confronto non è sensibile alle maiuscole/minuscole con la collazione predefinita, motivo per cui "Code Viene restituito il nome "Black".

Escludendo i personaggi con un elenco di personaggi negati

Modifichiamo ora lo script e invertiamo l'ordine dei caratteri nell'elenco per vedere quali righe vengono restituite.

SELECT * FROM `movies`
WHERE `title` REGEXP '^[^abcd]';

Eseguendo lo script precedente in MySQL L'analisi con Workbench sul database myflixdb ci fornisce i seguenti risultati.

All'interno di un elenco di caratteri, il significato del simbolo di accento circonflesso cambia: '^[^abcd]' continua ad ancorare la corrispondenza all'inizio, mentre [^abcd] ora esclude ogni titolo che inizia con uno dei caratteri racchiusi tra parentesi.

Questi due esempi utilizzano solo ancore ed elenchi di personaggi. La sezione successiva illustra l'insieme completo dei metacaratteri.

Metacaratteri delle espressioni regolari

Gli esempi sopra riportati mostrano la forma più semplice di un'espressione regolare. I metacaratteri consentono di perfezionare una ricerca di pattern: esprimono ripetizioni, alternative, intervalli e posizioni. La tabella seguente elenca tutti i metacaratteri supportati da MySQL Operatore REGEX, con un esempio corretto per ciascuno.

La barra rovesciata (\) è il carattere di escape. Perché MySQL analizza prima la stringa e poi il pattern; una barra rovesciata letterale deve essere scritta come una doppia barra rovesciata (\\) all'interno di un pattern REGEXP.

Ora che tutti i metacaratteri sono stati definiti, sorge spontanea una domanda: quando le espressioni regolari (REGEXP) dovrebbero sostituire il più semplice operatore LIKE?

REGEXP o LIKE: quale utilizzare?

Entrambi gli operatori filtrano le righe in base a un modello, ma risolvono problemi diversi. LIKE comprende solo due simboli, mentre REGEXP comprende l'intero set di metacaratteri mostrato sopra. Questa maggiore potenza ha un costo, quindi la scelta è un compromesso piuttosto che una preferenza.

Scegli LIKE per corrispondenze semplici, perché è di facile lettura e può comunque utilizzare un indice. Scegli REGEXP quando un singolo pattern deve esprimere più regole contemporaneamente, ad esempio "inizia con c o d e termina con una cifra". Su tabelle di grandi dimensioni, restringi prima le righe con una condizione indicizzata, quindi applica REGEXP al set più piccolo.

MySQL 8.0 Funzioni di espressione regolare

L'operatore REGEXP risponde a una sola domanda: il valore corrisponde al modello? MySQL 8.0 ha aggiunto quattro funzioni che vanno oltre e ti permettono di individuare, ad esempiotract, e riscrivi il testo corrispondente. Ciascuno accetta un argomento opzionale match_type, dove 'c' forza un confronto sensibile alle maiuscole e 'i' forza un confronto non sensibile alle maiuscole.

• REGEXP_LIKE(espressione, modello) Restituisce 1 quando il valore corrisponde al modello. È la forma funzionale dell'operatore REGEXP e l'argomento match_type rende esplicita la distinzione tra maiuscole e minuscole.
• REGEXP_INSTR(espressione, modello) Restituisce la posizione del primo carattere della corrispondenza, oppure 0 quando il modello non viene trovato.
• REGEXP_SUBSTR(espressione, modello) restituisce la sottostringa corrispondente, utile per estrarre un anno, un codice o un numero da un testo più lungo.
• REGEXP_REPLACE(espressione, modello, sostituzione) restituisce il valore con ogni corrispondenza sostituita, quindi può pulire i dati all'interno di un query di aggiornamento SQL.

SELECT title,
       REGEXP_SUBSTR(title, '[0-9]+') AS number_in_title
FROM `movies`
WHERE REGEXP_LIKE(title, '[0-9]');

La query sopra riporta tutti i titoli dei film che contengono una cifra, insieme alle cifre stesse. MySQL 5.7 Queste funzioni non sono disponibili, quindi l'operatore REGEXP rimane l'unica opzione.

DOMANDE FREQUENTI