Guida alle espressioni regolari MySQL (REGEXP): sintassi, funzioni MySQL 8, esempi e migliori pratiche

1. Introduzione

Utilizzare le espressioni regolari in MySQL

MySQL è un potente sistema di gestione di database per la ricerca e la manipolazione dei dati, e supporta anche espressioni regolari (REGEXP) per consentire ricerche più flessibili. Con le espressioni regolari, è possibile estrarre in modo efficiente i dati che corrispondono a modelli specifici, nonché formattare o convalidare i dati.

Vantaggi dell’uso delle espressioni regolari in MySQL

L’uso delle espressioni regolari di MySQL offre vantaggi come:

• Ricerca avanzata : Estrarre facilmente dati che contengono stringhe o modelli specifici.
• Verifica dell’integrità dei dati : Utile per la convalida dell’input (ad esempio, convalidare i formati degli indirizzi email).
• Più potente dell’operatore LIKE : Rispetto a LIKE, che utilizza i caratteri jolly ( % e _ ), REGEXP consente di definire condizioni di ricerca complesse in modo più flessibile.

Scopo e struttura di questo articolo

Questo articolo spiega tutto, dalle basi delle espressioni regolari in MySQL all’uso avanzato, all’ottimizzazione delle prestazioni e alle nuove funzioni di espressioni regolari introdotte in MySQL 8.0 e versioni successive. Con esempi pratici, il contenuto è progettato per essere facile da comprendere anche per i principianti.

Struttura dell’articolo:

1. Nozioni di base e sintassi delle espressioni regolari MySQL
2. Funzioni di espressioni regolari introdotte in MySQL 8.0 e versioni successive
3. Esempi pratici (validazione, estrazione dati, conversione di formato)
4. Considerazioni importanti quando si usano le espressioni regolari (prestazioni, sicurezza)
5. FAQ (Domande frequenti)
6. Riepilogo

2. Nozioni di base e sintassi delle espressioni regolari in MySQL

Come utilizzare le espressioni regolari in MySQL

In MySQL, si utilizza l’operatore REGEXP (o RLIKE) per lavorare con le espressioni regolari. Simile all’operatore LIKE, viene usato per determinare se il valore di una colonna corrisponde a un modello regex specificato.

Sintassi di base

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Or

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE è un alias per REGEXP, e entrambi si comportano allo stesso modo.

Esempio
Ad esempio, per cercare record in cui il nome contiene il carattere “山”:

SELECT * FROM users WHERE name REGEXP '山';

Modelli di espressioni regolari di base

Nelle espressioni regolari MySQL, è possibile utilizzare modelli come i seguenti.

Differenze rispetto all’operatore LIKE

MySQL fornisce anche l’operatore LIKE, comunemente usato per il matching di pattern semplici. Tuttavia, REGEXP è più potente di LIKE e supporta ricerche con condizioni più complesse.

Esempio 1: Utilizzare l’operatore LIKE

SELECT * FROM users WHERE name LIKE '%山%';

• Con LIKE, è possibile cercare dati che contengono 山, ma si possono usare solo caratteri jolly semplici ( % e _ ).

Esempio 2: Utilizzare l’operatore REGEXP

SELECT * FROM users WHERE name REGEXP '^山';

• Con REGEXP, è possibile estrarre solo i dati in cui il nome inizia con 山 .

Come specificare più modelli

È possibile usare | per specificare più modelli con una condizione OR.

Esempio: Ottenere gli utenti il cui cognome è “佐藤” o “田中”

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

Corrispondenza negativa

Se si usa ^ all’interno di parentesi quadre, è possibile recuperare dati che “non contengono i caratteri specificati”.

Esempio: Dati che iniziano con un carattere diverso da “山”

SELECT * FROM users WHERE name REGEXP '^[^山]';

In questo caso, restituisce i record in cui il nome non inizia con 山.

Riepilogo

• In MySQL, REGEXP consente ricerche di pattern più potenti rispetto a LIKE.
• Comprendere la sintassi di base e i pattern ( . , ^ , $ , [] , * , + , ecc.) consente ricerche flessibili.
• È importante capire le differenze rispetto a LIKE e utilizzare ciascuna in modo appropriato.
• L’uso di | permette di cercare con più pattern.
• L’uso di classi di caratteri negative [^ ] aiuta a cercare dati che non iniziano con caratteri specifici.

3. Funzioni di espressione regolare aggiunte in MySQL 8.0 e versioni successive

In MySQL 8.0, oltre all’operatore tradizionale REGEXP, sono state aggiunte quattro nuove funzioni per consentire ricerche di espressioni regolari più flessibili. Queste permettono di recuperare le posizioni delle corrispondenze, estrarre sottostringhe e eseguire sostituzioni, ampliando notevolmente ciò che è possibile fare con le regex in MySQL.

In questa sezione, spieghiamo in dettaglio ciascuna nuova funzione di espressione regolare e mostriamo esempi concreti.

3.1 REGEXP_LIKE()

Panoramica

REGEXP_LIKE(), come l’operatore REGEXP, verifica se il valore di una colonna corrisponde a un pattern regex specificato.

Sintassi

REGEXP_LIKE(column_name, 'regex_pattern' [, flags])

• column_name : la colonna da cercare
• 'regex_pattern' : il pattern regex usato come condizione
• flags (optional) : sensibilità al maiuscolo/minuscolo (usa i per corrispondenza case-insensitive)

Esempi

“Ottieni gli utenti il cui email termina con gmail.com o yahoo.co.jp”

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail\.com|@yahoo\.co\.jp)$');

“Cerca i nomi utente contenenti ‘admin’ (case-insensitive)”

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

✅ REGEXP_LIKE() ha lo stesso scopo dell’operatore REGEXP, ma consente uno stile più standard e compatibile con SQL.

3.2 REGEXP_INSTR()

Panoramica

REGEXP_INSTR() restituisce la posizione (indice di inizio) in cui si verifica una corrispondenza regex all’interno di una stringa.

Sintassi

REGEXP_INSTR(column_name, 'regex_pattern' [, start_position, occurrence, return_option, flags])

• start_position (optional): dove iniziare la ricerca (default: 1)
• occurrence (optional): quale occorrenza della corrispondenza restituire (default: 1)
• return_option (optional): 0 (restituisce l’indice di inizio) o 1 (restituisce l’indice di fine della corrispondenza)
• flags (optional): usa i per corrispondenza case-insensitive

Esempi

“Se il numero di telefono inizia con 090 o 080, ottieni la posizione di inizio”

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

“Ottieni la posizione di inizio della parte dominio in un indirizzo email”

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• Restituisce la posizione in cui si trova @ (ad esempio, user@example.com restituisce 5 ).

✅ Con REGEXP_INSTR(), è possibile recuperare informazioni posizionali, ampliando le opzioni per l’elaborazione delle stringhe.

3.3 REGEXP_SUBSTR()

Panoramica

REGEXP_SUBSTR() estrae la sottostringa che corrisponde a un pattern regex all’interno di una stringa.

Sintassi

REGEXP_SUBSTR(column_name, 'regex_pattern' [, start_position, occurrence, flags])

• occurrence (optional): ottieni la n-esima corrispondenza (default: 1)
• flags (optional): sensibilità al maiuscolo/minuscolo (i per ignorare il case)

Esempi

“Estrai solo la parte dominio da un indirizzo email”

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

“Ottieni la prima sequenza numerica in un messaggio”

SELECT message, REGEXP_SUBSTR(message, '[0-9]+') AS first_number FROM logs;

✅ Con REGEXP_SUBSTR(), è possibile estrarre pattern specifici e usarli per organizzare e trasformare i dati.

3.4 REGEXP_REPLACE()

Panoramica

REGEXP_REPLACE() sostituisce le sottostringhe che corrispondono a un pattern regex con un’altra stringa.

Sintassi

REGEXP_REPLACE(column_name, 'regex_pattern', 'replacement' [, occurrence, flags])

• replacement : la stringa con cui sostituire la parte corrispondente
• occurrence (opzionale): sostituisci solo la n‑esima occorrenza (se omessa, sostituisce tutte le occorrenze)
• flags (opzionale): usa i per il matching case‑insensitive

Esempi

“Rimuovere i trattini (-) dai numeri di telefono”

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

“Rimuovere i tag HTML”

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

✅ Con REGEXP_REPLACE(), la conversione di formato e la pulizia dei dati diventano facili.

3.5 Sommario

4. Esempi pratici di espressioni regolari MySQL

Sfruttando le espressioni regolari di MySQL, è possibile semplificare le attività di elaborazione dei dati reali, come la convalida dei dati, l’estrazione di dati specifici e la conversione di formato. In questa sezione, presentiamo casi d’uso pratici con codice SQL e spiegazioni.

4.1 Convalida degli indirizzi email

Panoramica

Utilizza le espressioni regolari per verificare se gli indirizzi email memorizzati nel database rispettano un formato valido.

SQL da utilizzare

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

Spiegazione

• ^[a-zA-Z0-9._%+-]+ → uno o più caratteri alfanumerici, punti, underscore o segni più prima di @
• @[a-zA-Z0-9.-]+ → un nome di dominio dopo @
• \.[a-zA-Z]{2,}$ → termina con un TLD (Top-Level Domain) di almeno 2 lettere (es., .com, .jp, .net)

Con questa query, è possibile escludere indirizzi email non validi (es., user@@example.com, user@.com).

4.2 Verifica dei formati dei numeri di telefono giapponesi

Panoramica

Determina se i numeri di telefono giapponesi comuni (es., 090-1234-5678, 03-1234-5678) rispettano il formato corretto.

SQL da utilizzare

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

Spiegazione

• 0[789]0-[0-9]{4}-[0-9]{4} → numeri mobili ( 090-xxxx-xxxx, 080-xxxx-xxxx, ecc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → numeri fissi ( 03-xxxx-xxxx, 06-xxxx-xxxx, ecc.)

In questo modo, le espressioni regolari sono utili quando è richiesta coerenza di formato.

4.3 Convalida del formato dei numeri di carta di credito

Panoramica

Convalida i formati dei numeri di carta di credito (Visa, Mastercard, American Express, ecc.).

SQL da utilizzare

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

Spiegazione

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa (16 cifre o 13 cifre)
• ^5[1-5][0-9]{14}$ → Mastercard (16 cifre)
• ^3[47][0-9]{13}$ → American Express (15 cifre)

Con questa query, è possibile filtrare i numeri di carta non validi nel database (es., lunghezza errata o cifre iniziali non valide).

4.4 Rimozione dei tag HTML

Panoramica

Se i dati inviati dagli utenti contengono tag HTML, è possibile rimuoverli e convertire il contenuto in testo semplice.

SQL da utilizzare

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

Spiegazione

• '<[^>]+>' → rimuove tutti i tag HTML compresi tra < e >

Esempio

Questo è utile per rimuovere i tag HTML da commenti degli utenti e post di blog.

4.5 Convalida del formato del codice postale (Giappone)

Panoramica

Convalida se i codici postali giapponesi (es., 123-4567) rispettano il formato corretto.

SQL da utilizzare

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

Spiegazione

• ^[0-9]{3}-[0-9]{4}$ → un codice postale nel formato “3 cifre-4 cifre”

Utilizzare questa regex aiuta a mantenere la coerenza dei dati e prevenire formati errati.

4.6 Filtraggio dei nomi utente (Rilevamento di parole non consentite)

Panoramica

Check whether usernames contain disallowed words reserved for system use (e.g., admin, root, system) during registration.

SQL da utilizzare

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

Spiegazione

• admin|root|system → rileva i nomi utente che contengono questi termini

Questa query aiuta a impedire agli utenti normali di utilizzare nomi destinati agli amministratori di sistema.

Riepilogo

• Le espressioni regolari possono essere usate per validare indirizzi email, numeri di telefono e numeri di carte di credito .
• Possono anche essere applicate per rimuovere tag HTML e convertire formati di dati .
• Sono efficaci per migliorare la sicurezza e la coerenza del database attraverso attività come filtrare parole non consentite e validare codici postali .

5. Considerazioni importanti quando si usano le espressioni regolari (Prestazioni e sicurezza)

L’uso delle espressioni regolari in MySQL consente ricerche potenti e pulizia dei dati. Tuttavia, un uso improprio può causare degrado delle prestazioni e introdurre rischi di sicurezza. Questa sezione spiega le considerazioni chiave per utilizzare le espressioni regolari di MySQL in modo sicuro ed efficiente.

5.1 Ottimizzazione delle prestazioni per le espressioni regolari

La ricerca con regex in MySQL è comoda, ma ha un inconveniente: gli indici generalmente non si applicano, il che può rendere le query più lente.

Misure per migliorare le prestazioni

1. Combina indici LIKE o FULLTEXT

   SELECT * FROM users WHERE email LIKE '%gmail.com';
   

2. Usa espressioni regolari più semplici

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';
   

3. Filtra prima i dati target

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

5.2 Rischi di sicurezza e contromisure

1. Denial of Service con espressioni regolari (ReDoS)

• Evita corrispondenze non limitate come .* .
• Usa ^ e $ per limitare l’ambito della ricerca.
• Non passare direttamente l’input dell’utente a REGEXP .

2. Iniezione SQL e espressioni regolari

❌ Query non sicura

SELECT * FROM users WHERE username REGEXP '$input';

✅ Query sicura (usa segnaposto)

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 Compatibilità di versione

Riepilogo

• Poiché REGEXP non utilizza gli indici, le misure di prestazione sono importanti.
• Per prevenire ReDoS (Denial of Service con espressioni regolari), evita l’uso eccessivo di caratteri jolly.
• Per prevenire l’iniezione SQL, non applicare direttamente l’input dell’utente a REGEXP.

6. FAQ (Domande frequenti)

Ecco le domande comuni sull’uso delle espressioni regolari MySQL (REGEXP). Risolvere questi problemi ti aiuterà a utilizzare le regex in modo più efficace.

6.1 Quali pattern regex posso usare in MySQL?

6.2 Qual è la differenza tra LIKE e REGEXP?

✅ Poiché LIKE è più veloce, preferisci LIKE per ricerche semplici e usa REGEXP quando è necessario un matching complesso.

6.3 Quali sono le differenze nelle funzionalità regex tra MySQL 5.x e MySQL 8.x?

6.4 Cosa devo fare se una regex non funziona come previsto?

✅ Checklist

1. Verifica che l’escaping sia corretto

   SELECT * FROM users WHERE email REGEXP '\.com$';
   

2. Prova il flag i con REGEXP_LIKE()

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');
   

3. Verifica la codifica dei dati

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Come posso migliorare le prestazioni delle query REGEXP?

1. Pre-filtra con LIKE

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

2. Usa gli indici dove appropriato

   ALTER TABLE users ADD FULLTEXT(email);
   

Riepilogo

• REGEXP è più potente di LIKE, ma è necessario tenere presente le prestazioni.
• In MySQL 8.0, sono state aggiunte nuove funzioni regex (come REGEXP_LIKE()), consentendo un’elaborazione più flessibile.
• Per migliorare le prestazioni: combinare con LIKE, utilizzare indici e progettare pattern regex semplici.

7. Riepilogo

Le espressioni regolari MySQL (REGEXP) sono molto utili per la ricerca dei dati, la convalida e la conversione di formati. Questo articolo ha trattato le basi delle regex MySQL, le nuove funzionalità introdotte in MySQL 8.0 e versioni successive, esempi pratici, considerazioni importanti e le FAQ.

7.1 Punti Chiave

Ecco un rapido riepilogo dei punti più importanti.

1. Nozioni di base sulle espressioni regolari MySQL

• L’utilizzo dell’operatore REGEXP consente un abbinamento di pattern più flessibile rispetto a LIKE.
• Pattern regex comuni
• ^ (inizio), $ (fine), . (qualsiasi singolo carattere), [] (classe di caratteri), + (una o più ripetizioni), ecc.

2. Nuove funzioni di espressioni regolari in MySQL 8.0+

In MySQL 8.0, sono state aggiunte queste quattro funzioni, consentendo un’elaborazione più flessibile:

• REGEXP_LIKE() : un’alternativa all’operatore REGEXP
• REGEXP_INSTR() : restituisce la posizione iniziale della sottostringa corrispondente
• REGEXP_SUBSTR() : estrae la sottostringa corrispondente
• REGEXP_REPLACE() : sostituisce le sottostringhe usando le regex

3. Esempi pratici

• Validazione degli indirizzi email
• Verifica di numeri di telefono e di carte di credito
• Rimozione dei tag HTML
• Validazione dei formati dei codici postali
• Rilevamento di parole non consentite
• Pulizia dei dati (rimozione spazi, virgole, ecc.)

4. Considerazioni importanti quando si usano le espressioni regolari

• Ottimizzazione delle prestazioni
• Poiché REGEXP non utilizza indici, può comportare scansioni complete della tabella.
• È possibile migliorare le prestazioni combinando LIKE o indici FULLTEXT.
• Misure di sicurezza
• Per prevenire ReDoS (Denial of Service tramite espressioni regolari), evitare l’uso eccessivo di .*.
• Per prevenire SQL injection, utilizzare i placeholders.

5. FAQ (Domande frequenti)

• Pattern regex disponibili in MySQL
• Differenze tra LIKE e REGEXP
• Differenze di funzionalità tra MySQL 5.x e 8.x
• Risoluzione dei problemi quando le regex non si comportano come previsto
• Modi per migliorare le prestazioni

7.2 Best practice per l’uso delle espressioni regolari

1. Mantenere i pattern regex semplici

• Esempio: evitare l’uso eccessivo di .* e utilizzare ^ (inizio) e $ (fine).

1. Combinare con LIKE o FULLTEXT quando opportuno

• Pre-filtrare con LIKE, quindi applicare REGEXP per ridurre il costo della query.

1. Utilizzare REGEXP_REPLACE() per la pulizia dei dati

• Esempio: rimuovere i tag HTML e normalizzare gli spazi superflui.

1. Sfruttare MySQL 8.0 e versioni successive

• L’uso di nuove funzioni come REGEXP_LIKE() può rendere il SQL più leggibile.

1. Applicare le misure di sicurezza in modo completo

• Non applicare direttamente l’input dell’utente a REGEXP (prevenzione SQL injection).
• Utilizzare i placeholders per le query dinamiche.

7.3 Risorse per approfondire

Ecco dei riferimenti per aiutarti a approfondire le espressioni regolari MySQL.

Documentazione ufficiale

• MySQL 8.0 Regular Expression Reference
• MySQL 5.x Regular Expression Basics

7.4 Note finali

Le espressioni regolari MySQL possono essere utilizzate in molte situazioni, inclusa la ricerca, la pulizia e la convalida dei dati. Tuttavia, è importante usarle in modo appropriato comprendendo le considerazioni relative a prestazioni e sicurezza.

Speriamo che questo articolo ti sia utile quando lavori con le espressioni regolari MySQL. Prova ad applicare queste tecniche nei tuoi progetti!