Guide des expressions régulières MySQL (REGEXP) : syntaxe, fonctions MySQL 8, exemples et bonnes pratiques

1. Introduction

Utilisation des expressions régulières dans MySQL

MySQL est un système de gestion de bases de données puissant pour la recherche et la manipulation de données, et il prend également en charge les expressions régulières (REGEXP) afin de permettre des recherches plus flexibles. Avec les expressions régulières, vous pouvez extraire efficacement les données qui correspondent à des motifs spécifiques, ainsi que formater ou valider des données.

Avantages de l’utilisation des expressions régulières dans MySQL

L’utilisation des expressions régulières MySQL offre des avantages tels que :

• Recherche avancée : Extraire facilement les données contenant des chaînes ou des motifs spécifiques.
• Vérification de l’intégrité des données : Utile pour la validation des entrées (par ex., validation des formats d’adresses e‑mail).
• Plus puissant que l’opérateur LIKE : Comparé à LIKE, qui utilise les caractères génériques (% et _), REGEXP vous permet de définir des conditions de recherche complexes de manière plus flexible.

Objectif et structure de cet article

Cet article explique tout, des bases des expressions régulières dans MySQL à l’utilisation avancée, l’optimisation des performances et les nouvelles fonctions d’expression régulière introduites dans MySQL 8.0 et versions ultérieures. Avec des exemples pratiques, le contenu est conçu pour être facile à comprendre même pour les débutants.

Structure de l’article :

1. Bases et syntaxe des expressions régulières MySQL
2. Fonctions d’expression régulière ajoutées dans MySQL 8.0 et versions ultérieures
3. Exemples pratiques (validation, extraction de données, conversion de format)
4. Considérations importantes lors de l’utilisation des expressions régulières (performance, sécurité)
5. FAQ (Foire aux questions)
6. Résumé

2. Bases des expressions régulières et syntaxe dans MySQL

Comment utiliser les expressions régulières dans MySQL

Dans MySQL, vous utilisez l’opérateur REGEXP (ou RLIKE) pour travailler avec les expressions régulières. Similaire à l’opérateur LIKE, il sert à déterminer si la valeur d’une colonne correspond à un motif regex spécifié.

Syntaxe de base

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Ou

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE est un alias de REGEXP, et les deux se comportent de la même façon.

Exemple
Par exemple, pour rechercher les enregistrements où le nom contient le caractère « 山 » :

SELECT * FROM users WHERE name REGEXP '山';

Modèles d’expressions régulières de base

Dans les expressions régulières MySQL, vous pouvez utiliser des modèles comme ceux‑ci.

Différences avec l’opérateur LIKE

MySQL fournit également l’opérateur LIKE, couramment utilisé pour le filtrage de motifs simples. Cependant, REGEXP est plus puissant que LIKE et prend en charge la recherche avec des conditions plus complexes.

Exemple 1 : Utilisation de l’opérateur LIKE

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

• Avec LIKE, vous pouvez rechercher des données contenant 山, mais vous ne pouvez utiliser que des caractères génériques simples (% et _).

Exemple 2 : Utilisation de l’opérateur REGEXP

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

• Avec REGEXP, vous pouvez extraire uniquement les données où le nom commence par 山.

Comment spécifier plusieurs modèles

Vous pouvez utiliser | pour spécifier plusieurs modèles avec une condition OU.

Exemple : Obtenir les utilisateurs dont le nom de famille est « 佐藤 » ou « 田中 »

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

Correspondance négative

Si vous utilisez ^ à l’intérieur de crochets, vous pouvez récupérer les données qui « ne contiennent pas les caractères spécifiés ».

Exemple : Données qui commencent par un caractère autre que « 山 »

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

Dans ce cas, cela renvoie les enregistrements où le nom ne commence pas par 山.

Résumé

• Dans MySQL, REGEXP permet une recherche de motifs plus puissante que LIKE .
• Comprendre la syntaxe de base et les motifs ( . , ^ , $ , [] , * , + , etc.) permet des recherches flexibles.
• Il est important de comprendre les différences avec LIKE et d’utiliser chaque opérateur de manière appropriée.
• Utiliser | permet de rechercher avec plusieurs motifs.
• Utiliser des classes de caractères négatives [^ ] vous aide à rechercher des données qui ne commencent pas par des caractères spécifiques.

3. Fonctions d’expression régulière ajoutées dans MySQL 8.0 et versions ultérieures

Dans MySQL 8.0, en plus de l’opérateur traditionnel REGEXP, quatre nouvelles fonctions ont été ajoutées pour permettre une recherche d’expressions régulières plus flexible. Elles permettent de récupérer les positions des correspondances, d’extraire des sous‑chaînes et d’effectuer des remplacements, élargissant considérablement ce que vous pouvez faire avec les regex dans MySQL.

Dans cette section, nous expliquons chaque nouvelle fonction d’expression régulière en détail et présentons des exemples concrets.

3.1 REGEXP_LIKE()

Overview

REGEXP_LIKE(), comme l’opérateur REGEXP, vérifie si la valeur d’une colonne correspond à un motif regex spécifié.

Syntax

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

• column_name : la colonne à rechercher
• 'regex_pattern' : le motif regex utilisé comme condition
• flags (optional) : sensibilité à la casse (utilisez i pour une correspondance insensible à la casse)

Examples

« Obtenir les utilisateurs dont l’e‑mail se termine par gmail.com ou yahoo.co.jp »

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

« Rechercher les noms d’utilisateur contenant ‘admin’ (insensible à la casse) »

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

✅ REGEXP_LIKE() remplit le même rôle que l’opérateur REGEXP, mais il permet un style plus conforme au SQL standard.

3.2 REGEXP_INSTR()

Overview

REGEXP_INSTR() renvoie la position (indice de départ) où une correspondance regex se produit dans une chaîne.

Syntax

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

• start_position (optional) : où commencer la recherche (par défaut : 1)
• occurrence (optional) : quelle occurrence de correspondance retourner (par défaut : 1)
• return_option (optional) : 0 (renvoie la position de départ) ou 1 (renvoie la position de fin de la correspondance)
• flags (optional) : utilisez i pour une correspondance insensible à la casse

Examples

« Si le numéro de téléphone commence par 090 ou 080, obtenir la position de départ »

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

« Obtenir la position de départ de la partie domaine d’une adresse e‑mail »

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

• Elle renvoie la position où @ est trouvé (par ex., user@example.com renvoie 5 ).

✅ Avec REGEXP_INSTR(), vous pouvez récupérer des informations de position, élargissant vos possibilités de traitement de chaînes.

3.3 REGEXP_SUBSTR()

Overview

REGEXP_SUBSTR() extrait la sous‑chaîne qui correspond à un motif regex dans une chaîne.

Syntax

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

• occurrence (optional) : obtenir la n‑ième correspondance (par défaut : 1)
• flags (optional) : sensibilité à la casse (i pour ignorer la casse)

Examples

« Extraire uniquement la partie domaine d’une adresse e‑mail »

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

« Obtenir la première séquence de chiffres dans un message »

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

✅ Avec REGEXP_SUBSTR(), vous pouvez extraire des motifs spécifiques et les utiliser pour organiser et transformer les données.

3.4 REGEXP_REPLACE()

Overview

REGEXP_REPLACE() remplace les sous‑chaînes qui correspondent à un motif regex par une autre chaîne.

Syntax

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

• replacement : la chaîne de caractères à remplacer la partie correspondante
• occurrence (optionnel) : ne remplacer que la nième correspondance (si omis, remplace toutes les correspondances)
• flags (optionnel) : utilisez i pour une correspondance insensible à la casse

Exemples

« Supprimer les tirets (-) des numéros de téléphone »

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

« Supprimer les balises HTML »

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

✅ Avec REGEXP_REPLACE(), la conversion de format et le nettoyage des données deviennent faciles.

3.5 Résumé

4. Exemples pratiques d’expressions régulières MySQL

En exploitant les expressions régulières MySQL, vous pouvez rationaliser les tâches de traitement de données réelles telles que la validation des données, l’extraction de données spécifiques et la conversion de format. Dans cette section, nous présentons des cas d’utilisation pratiques avec du code SQL et des explications.

4.1 Validation d’adresses e‑mail

Vue d’ensemble

Utilisez des expressions régulières pour vérifier si les adresses e‑mail stockées dans la base de données respectent un format valide.

SQL à utiliser

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

Explication

• ^[a-zA-Z0-9._%+-]+ → un ou plusieurs caractères alphanumériques, points, tirets bas ou signes plus avant @
• @[a-zA-Z0-9.-]+ → un nom de domaine après @
• \.[a-zA-Z]{2,}$ → se termine par un TLD (domaine de premier niveau) d’au moins 2 lettres (par ex., .com, .jp, .net)

Avec cette requête, vous pouvez exclure les adresses e‑mail invalides (par ex., user@@example.com, user@.com).

4.2 Vérification des formats de numéros de téléphone japonais

Vue d’ensemble

Déterminez si les numéros de téléphone japonais courants (par ex., 090-1234-5678, 03-1234-5678) respectent le format correct.

SQL à utiliser

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})$';

Explication

• 0[789]0-[0-9]{4}-[0-9]{4} → numéros mobiles ( 090-xxxx-xxxx, 080-xxxx-xxxx, etc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → numéros fixes ( 03-xxxx-xxxx, 06-xxxx-xxxx, etc.)

De cette manière, les expressions régulières sont utiles lorsque la cohérence du format est requise.

4.3 Validation du format des numéros de carte de crédit

Vue d’ensemble

Validez les formats des numéros de carte de crédit (Visa, Mastercard, American Express, etc.).

SQL à utiliser

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})$';

Explication

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

Avec cette requête, vous pouvez filtrer les numéros de carte invalides dans la base de données (par ex., longueur incorrecte ou chiffres de départ invalides).

4.4 Suppression des balises HTML

Vue d’ensemble

Si les données soumises par les utilisateurs contiennent des balises HTML, vous pouvez les supprimer et convertir le contenu en texte brut.

SQL à utiliser

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

Explication

• '<[^>]+>' → supprime toutes les balises HTML entre < et >

Exemple

Ceci est utile pour supprimer les balises HTML des commentaires d’utilisateurs et des articles de blog.

4.5 Validation du format du code postal (Japon)

Vue d’ensemble

Validez si les codes postaux japonais (par ex., 123-4567) respectent le format correct.

SQL à utiliser

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

Explication

• ^[0-9]{3}-[0-9]{4}$ → un code postal au format « 3 chiffres‑4 chiffres »

Utiliser cette expression régulière aide à maintenir la cohérence des données et à éviter les formats incorrects.

4.6 Filtrage des noms d’utilisateur (détection de mots interdits)

Vue d’ensemble

Vérifiez si les noms d’utilisateur contiennent des mots interdits réservés à l’usage du système (par ex., admin, root, system) lors de l’inscription.

SQL à utiliser

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

Explication

• admin|root|system → détecte les noms d’utilisateur contenant ces termes

Cette requête aide à empêcher les utilisateurs ordinaires d’utiliser des noms destinés aux administrateurs système.

Résumé

• Les expressions régulières peuvent être utilisées pour valider les adresses e‑mail, les numéros de téléphone et les numéros de carte de crédit .
• Elles peuvent également être appliquées à la suppression des balises HTML et à la conversion de formats de données .
• Elles sont efficaces pour améliorer la sécurité et la cohérence de la base de données grâce à des tâches telles que le filtrage des mots interdits et la validation des codes postaux .

5. Considérations importantes lors de l’utilisation des expressions régulières (Performance et sécurité)

L’utilisation des expressions régulières dans MySQL permet des recherches puissantes et le nettoyage des données. Cependant, une utilisation incorrecte peut entraîner une dégradation des performances et introduire des risques de sécurité. Cette section explique les considérations clés pour utiliser les expressions régulières MySQL de manière sûre et efficace.

5.1 Optimisation des performances pour les expressions régulières

La recherche regex dans MySQL est pratique, mais elle présente un inconvénient : les index ne s’appliquent généralement pas, ce qui peut ralentir les requêtes.

Mesures pour améliorer les performances

1. Combiner les index LIKE ou FULLTEXT

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

2. Utiliser des expressions régulières plus simples

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

3. Filtrer d’abord les données cibles

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

5.2 Risques de sécurité et contre‑mesures

1. Déni de service par expression régulière (ReDoS)

• Évitez les correspondances non limitées comme .* .
• Utilisez ^ et $ pour limiter la portée de la recherche.
• Ne passez pas directement les entrées utilisateur dans REGEXP .

2. Injection SQL et expressions régulières

❌ Requête non sécurisée

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

✅ Requête sécurisée (utiliser des paramètres)

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

5.3 Compatibilité des versions

Résumé

• Comme REGEXP n’utilise pas d’index, les mesures de performance sont importantes.
• Pour prévenir le ReDoS (déni de service par expression régulière), évitez l’utilisation excessive de caractères génériques.
• Pour prévenir les injections SQL, n’appliquez pas directement les entrées utilisateur à REGEXP.

6. FAQ (Foire aux questions)

Voici les questions courantes concernant l’utilisation des expressions régulières MySQL (REGEXP). Résoudre ces problèmes vous aidera à utiliser les regex plus efficacement.

6.1 Quels modèles regex puis-je utiliser dans MySQL ?

6.2 Quelle est la différence entre LIKE et REGEXP ?

✅ Comme LIKE est plus rapide, privilégiez LIKE pour les recherches simples et utilisez REGEXP lorsque des correspondances complexes sont requises.

6.3 Quelles sont les différences de fonctionnalités regex entre MySQL 5.x et MySQL 8.x ?

6.4 Que faire si une regex ne fonctionne pas comme prévu ?

✅ Liste de contrôle

1. Vérifier que l’échappement est correct

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

2. Essayer le drapeau i avec REGEXP_LIKE()

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

3. Vérifier l’encodage des données

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Comment améliorer les performances des requêtes REGEXP ?

1. Pré‑filtrer avec LIKE

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

2. Utiliser des index lorsque c’est approprié

   ALTER TABLE users ADD FULLTEXT(email);
   

Résumé

• REGEXP est plus puissant que LIKE, mais vous devez faire attention aux performances.
• Dans MySQL 8.0, de nouvelles fonctions regex (telles que REGEXP_LIKE()) ont été ajoutées, permettant un traitement plus flexible.
• Pour améliorer les performances : combinez avec LIKE, utilisez des index, et concevez des motifs regex simples.

7. Résumé

Les expressions régulières MySQL (REGEXP) sont très utiles pour la recherche de données, la validation et la conversion de formats. Cet article a couvert les bases des regex MySQL, les nouvelles fonctionnalités ajoutées dans MySQL 8.0 et versions ultérieures, des exemples pratiques, des considérations importantes et des FAQ.

7.1 Points Clés

Voici un rappel rapide des points les plus importants.

1. Bases des Expressions Régulières MySQL

• L’utilisation de l’opérateur REGEXP permet un appariement de motifs plus flexible que LIKE .
• Motifs regex courants
• ^ (début), $ (fin), . (n’importe quel caractère unique), [] (classe de caractères), + (une ou plusieurs répétitions), etc.

2. Nouvelles Fonctions d’Expressions Régulières dans MySQL 8.0+

Dans MySQL 8.0, ces quatre fonctions ont été ajoutées, permettant un traitement plus flexible :

• REGEXP_LIKE() : une alternative à l’opérateur REGEXP
• REGEXP_INSTR() : obtenir la position de début de la sous-chaîne correspondante
• REGEXP_SUBSTR() : extraire la sous-chaîne correspondante
• REGEXP_REPLACE() : remplacer des sous-chaînes à l’aide de regex

3. Exemples Pratiques

• Validation d’adresses e-mail
• Vérifications de numéros de téléphone et de cartes de crédit
• Suppression de balises HTML
• Validation de formats de codes postaux
• Détection de mots interdits
• Nettoyage de données (suppression d’espaces, de virgules, etc.)

4. Considérations Importantes Lors de l’Utilisation des Expressions Régulières

• Optimisation des performances
• Comme REGEXP n’utilise pas d’index, cela peut entraîner des balayages complets de table .
• Vous pouvez améliorer les performances en combinant LIKE ou des index FULLTEXT.
• Mesures de sécurité
• Pour prévenir ReDoS (Regular Expression DoS) , évitez un usage excessif de .* .
• Pour prévenir l’injection SQL , utilisez des paramètres .

5. FAQ (Questions Fréquemment Posées)

• Motifs regex disponibles dans MySQL
• Différences entre LIKE et REGEXP
• Différences de fonctionnalités entre MySQL 5.x et 8.x
• Dépannage lorsque regex ne se comporte pas comme prévu
• Façons d’améliorer les performances

7.2 Meilleures Pratiques pour l’Utilisation des Expressions Régulières

1. Gardez les motifs regex simples

• Exemple : Évitez un usage excessif de .* et utilisez ^ (début) et $ (fin).

1. Combinez avec LIKE ou FULLTEXT lorsque approprié

• Pré-filtrez avec LIKE , puis appliquez REGEXP pour réduire le coût de la requête.

1. Utilisez REGEXP_REPLACE() pour le nettoyage de données

• Exemple : Supprimez les balises HTML et normalisez les espaces inutiles.

1. Profitez de MySQL 8.0 et versions ultérieures

• L’utilisation de nouvelles fonctions telles que REGEXP_LIKE() rend le SQL plus lisible.

1. Appliquez des mesures de sécurité rigoureuses

• N’appliquez pas directement les entrées utilisateur à REGEXP (prévention de l’injection SQL).
• Utilisez des paramètres pour les requêtes dynamiques.

7.3 Ressources pour un Apprentissage Approfondi

Voici des références pour vous aider à apprendre les expressions régulières MySQL plus en profondeur.

Documentation Officielle

• Référence des Expressions Régulières MySQL 8.0
• Bases des Expressions Régulières MySQL 5.x

7.4 Notes Finales

Les expressions régulières MySQL peuvent être utilisées dans de nombreuses situations, y compris la recherche, le nettoyage et la validation de données. Cependant, il est important de les utiliser de manière appropriée en comprenant les considérations de performance et de sécurité.

Nous espérons que cet article vous aide lorsque vous travaillez avec les expressions régulières MySQL. Essayez d’appliquer ces techniques dans vos projets !