Comment remplacer des chaînes dans MySQL : REPLACE() et REGEXP_REPLACE() (MySQL 8.0+)

Introduction

Lorsque vous travaillez avec MySQL, il n’est pas rare de rencontrer des situations comme « Je veux remplacer uniquement une chaîne spécifique dans toute une table » ou « Je dois corriger des données incorrectes en masse ». Par exemple, lorsqu’une URL utilisée dans les descriptions de produits d’un site e‑commerce change, lorsque vous voulez corriger d’un coup des erreurs de saisie passées, ou lorsque vous devez uniformiser le formatage (p. ex. : convertir des tirets en barres obliques), vous avez souvent besoin de remplacement de chaînes.

Dans cet article, nous expliquons en détail les méthodes et techniques pratiques pour remplacer des chaînes dans MySQL, des bases aux usages avancés. Vous apprendrez les fondamentaux de la fonction REPLACE() la plus couramment utilisée, avec des exemples concrets, des moyens efficaces d’effectuer plusieurs remplacements, et le remplacement avancé à l’aide d’expressions régulières.

Nous couvrons également les différences de fonctions disponibles selon votre version de MySQL, les précautions importantes pour éviter toute perte de données accidentelle, et les considérations de performance — fournissant des informations réellement utiles en environnement réel.

Après avoir lu cet article, vous serez capable de résoudre toutes les questions et défis suivants :

• Quelle est la façon la plus simple de remplacer une chaîne spécifique dans MySQL ?
• Comment remplacer plusieurs motifs en masse ?
• Le remplacement flexible à l’aide d’expressions régulières est‑il possible ?
• Quels sont les risques et les précautions lors de la mise à jour d’un grand volume de données d’un coup ?
• Comment sauvegarder les données pour prévenir les échecs ?

Ce contenu est idéal pour tous, des débutants aux praticiens expérimentés qui souhaitent maîtriser en toute confiance le remplacement de chaînes dans MySQL.

MySQL String Replacement Basics (REPLACE Function)

Si vous souhaitez remplacer des chaînes dans MySQL, l’outil le plus couramment utilisé est la fonction REPLACE(). REPLACE() trouve un motif précis dans une chaîne ou la valeur d’une colonne donnée et le remplace en masse par une autre chaîne. Elle est utile dans de nombreuses situations, y compris les corrections de données récurrentes et les conversions par lots à grande échelle dans les bases de données.

Basic Syntax of REPLACE()

REPLACE(original_string, search_string, replacement_string)

• original_string : La chaîne à modifier, ou le nom d’une colonne de table.
• search_string : La partie que vous souhaitez remplacer.
• replacement_string : La nouvelle chaîne à insérer.

Par exemple :

SELECT REPLACE('I love MySQL!', 'MySQL', 'PostgreSQL');

Cette requête recherche « MySQL » dans la chaîne « I love MySQL! » et la remplace par « PostgreSQL », de sorte que le résultat devienne « I love PostgreSQL! ».

Case Sensitivity

REPLACE() effectue une recherche et un remplacement sensibles à la casse. Par exemple, « mysql » et « MySQL » sont traités comme des chaînes différentes.
Si la chaîne cible n’est pas trouvée, la chaîne originale est renvoyée telle quelle.

Supported Data Types

REPLACE() peut être utilisée avec les colonnes de chaîne courantes telles que CHAR, VARCHAR et TEXT. Cependant, soyez prudent avec les types de données spéciaux comme BLOB, car le comportement peut ne pas être celui attendu.

De cette façon, REPLACE() est attrayante parce qu’elle est simple et intuitive à utiliser.
Dans les sections suivantes, nous expliquerons des exemples SQL pratiques utilisant REPLACE() et comment l’appliquer aux données d’une table.

Basic Usage and Practical Examples

Bien que REPLACE() soit très simple, dans le travail réel vous avez souvent besoin de « remplacer des chaînes dans une colonne de base de données en masse ». Ici, nous expliquerons les opérations de base avec REPLACE() et comment corriger par lots les données d’une table à l’aide d’exemples SQL concrets.

Simple String Replacement with SELECT

Tout d’abord, voici l’utilisation la plus basique : remplacer du texte dans une chaîne spécifique.

SELECT REPLACE('Hello, mysql user!', 'mysql', 'MySQL');

Cette requête remplace « mysql » par « MySQL », renvoyant « Hello, MySQL user! ».

Bulk Replacement in a Table Column with UPDATE

Un cas d’utilisation réel courant consiste à remplacer des chaînes dans une colonne spécifique d’une table.
Par exemple, si vous souhaitez mettre à jour toutes les occurrences de l’ancien domaine oldsite.com vers le nouveau domaine newsite.com dans les descriptions de produits, vous pouvez exécuter le SQL suivant :

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com');

Ce SQL remplace chaque occurrence de « oldsite.com » dans la colonne description de la table products par « newsite.com ».

Précautions lors de l’exécution

Un UPDATE utilisant REPLACE() s’applique à tous les enregistrements, ce qui signifie qu’il existe un risque de réécriture au‑delà de ce qui était prévu.
Avant de l’exécuter en production, effectuez toujours une sauvegarde et vérifiez le comportement dans un environnement de test.

Limiter l’impact avec une clause WHERE

Si vous ne voulez cibler qu’un sous‑ensemble de données, utilisez une clause WHERE. Par exemple, pour ne viser que les produits ajoutés en 2024 ou plus tard :

UPDATE products
SET description = REPLACE(description, 'oldsite.com', 'newsite.com')
WHERE created_at >= '2024-01-01';

Cela permet de prévenir les réécritures inutiles.

Une fois que vous avez compris ces bases, les opérations quotidiennes et les tâches de nettoyage de données deviennent beaucoup plus efficaces.

Collection d’exemples d’utilisations courantes

REPLACE() est utile dans de nombreuses situations réelles où l’on se demande « Puis‑je remplacer cela ? ». Voici des exemples pratiques pour des cas d’usage fréquents.

1. Correction de fautes de frappe et de texte mal saisi

Par exemple, c’est pratique lorsque vous devez corriger en masse des erreurs de saisie fréquentes.

UPDATE users
SET profile = REPLACE(profile, 'htto://', 'http://');

Ce SQL corrige en lot le texte mal saisi « htto:// » en « http:// ».

2. Remplacement en masse d’URL ou de domaines

C’est utile lorsque vous refondez ou migrez un site web et que vous devez remplacer un ancien domaine par un nouveau dans l’ensemble de vos données.

UPDATE blog_posts
SET content = REPLACE(content, 'old-domain.jp', 'new-domain.jp');

3. Suppression d’espaces, de sauts de ligne ou de symboles indésirables

Si des espaces ou des codes de saut de ligne indésirables se sont glissés dans vos données, vous pouvez les supprimer en masse avec REPLACE().

UPDATE addresses
SET zipcode = REPLACE(zipcode, ' ', '');

Cet exemple supprime tous les espaces des codes postaux. Pour les sauts de ligne, spécifiez '\n' ou '\r'.

4. Normalisation de formats (trait d’union → slash, caractères plein‑large → demi‑large, etc.)

Vous pouvez également normaliser facilement les formats de données avec REPLACE().

UPDATE products
SET code = REPLACE(code, '-', '/');

Si vous souhaitez convertir des caractères plein‑large en demi‑large en masse, vous pouvez imbriquer plusieurs appels REPLACE().

5. Remplacement de plusieurs motifs en une fois

Si vous voulez remplacer plusieurs motifs simultanément, imbriquez les appels REPLACE().

UPDATE contacts
SET note = REPLACE(REPLACE(note, '株式会社', '（株）'), '有限会社', '（有）');

Cela convertit « 株式会社 » et « 有限会社 » en leurs formes abrégées en une seule opération.

REPLACE() est une fonctionnalité puissante pour gérer efficacement les tâches qui nécessitent « de nombreuses modifications » et qui seraient pénibles à réaliser manuellement.

Techniques avancées et prévention des problèmes

REPLACE() est très pratique, mais selon la façon dont vous l’utilisez, vous pouvez rencontrer des problèmes inattendus — ou bien optimiser votre travail de façon considérable. Nous expliquons ici des techniques avancées concrètes et des astuces pour éviter les pièges.

1. Gestion des valeurs NULL

Si la colonne cible est NULL, REPLACE() renvoie également NULL. Cela peut laisser les données inchangées de manière inattendue. Si vous voulez vous assurer que le remplacement s’applique même lorsque des valeurs NULL existent, combinez‑le avec IFNULL().

UPDATE users
SET comment = REPLACE(IFNULL(comment, ''), 'NGワード', '***');

Cela traite NULL comme une chaîne vide afin que le remplacement soit appliqué.

2. Remplacement insensible à la casse

REPLACE() est sensible à la casse par défaut. Si vous souhaitez remplacer à la fois les variantes majuscules et minuscules, il est courant de combiner LOWER() / UPPER() pour la comparaison et d’effectuer le remplacement en deux passes selon les besoins.

UPDATE articles
SET title = REPLACE(REPLACE(title, 'MySQL', 'MariaDB'), 'mysql', 'MariaDB');

3. Remplacement en plusieurs étapes (REPLACE imbriqué)

Si vous souhaitez remplacer plusieurs motifs différents en même temps, imbriquez les appels à REPLACE().

UPDATE logs
SET message = REPLACE(REPLACE(message, 'error', 'warning'), 'fail', 'caution');

4. Limiter l’impact avec UPDATE + WHERE

Au lieu de mettre à jour toutes les données d’un coup, utilisez une clause WHERE pour cibler uniquement les lignes dont vous avez besoin.

UPDATE customers
SET email = REPLACE(email, '@oldmail.com', '@newmail.com')
WHERE registered_at >= '2023-01-01';

5. Vérifiez toujours dans un environnement de test et effectuez des sauvegardes

Les mises à jour utilisant REPLACE() sont souvent difficiles à annuler. Avant de les exécuter en production, effectuez toujours une sauvegarde. Tester minutieusement avec des données d’exemple ou dans un environnement de préproduction réduit considérablement le risque.

En appliquant correctement REPLACE(), vous pouvez effectuer des tâches de remplacement de chaînes de manière plus sûre et plus efficace.

Remplacement de chaînes avec expressions régulières (MySQL 8.0+ uniquement)

Dans MySQL 8.0 et versions ultérieures, vous pouvez utiliser non seulement REPLACE() mais aussi REGEXP_REPLACE() pour effectuer des remplacements avancés à l’aide d’expressions régulières. Cela permet une correspondance de motifs flexible et un nettoyage de données efficace pour des cas complexes.

Syntaxe de base de REGEXP_REPLACE()

REGEXP_REPLACE(original_string, regex_pattern, replacement_string)

• original_string : La chaîne ou le nom de colonne à modifier.
• regex_pattern : Le motif à correspondre (par ex., [0-9]{3}-[0-9]{4} ).
• replacement_string : La nouvelle chaîne à insérer.

Exemple 1 : Suppression des tirets des numéros de téléphone

Si vous souhaitez supprimer tous les tirets des numéros de téléphone, vous pouvez écrire :

UPDATE users
SET tel = REGEXP_REPLACE(tel, '-', '');

Exemple 2 : Normalisation du format du code postal

Les expressions régulières sont également utiles pour normaliser les codes postaux dans différents formats (par ex., « 123-4567 » et « 1234567 »).

UPDATE addresses
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

Cette requête SQL normalise à la fois « 1234567 » et « 123-4567 » au format « 123-4567 ».

Exemple 3 : Suppression des caractères non alphanumériques

Vous pouvez également supprimer tous les caractères autres que les lettres et les chiffres.

UPDATE records
SET code = REGEXP_REPLACE(code, '[^a-zA-Z0-9]', '');

Cela supprime tous les caractères non alphanumériques de la colonne code.

Comment vérifier votre version de MySQL

REGEXP_REPLACE() est disponible uniquement dans MySQL 8.0 et versions ultérieures. Vous pouvez vérifier votre version actuelle de MySQL avec cette requête :

SELECT VERSION();

Si vous utilisez une version plus ancienne comme MySQL 5.x, REGEXP_REPLACE() n’est pas disponible, pensez donc à utiliser REPLACE() ou à effectuer le remplacement côté application.

Le remplacement basé sur les expressions régulières est extrêmement puissant lorsque les motifs de données varient largement ou lorsque des transformations complexes sont requises.

Comparaison avec d’autres fonctions de chaîne et notes

MySQL propose plusieurs fonctions de chaîne utiles. Chaque fonction ayant des objectifs et des caractéristiques différents, il est important de choisir la meilleure pour les tâches de remplacement et d’édition. Ici, nous comparons les fonctions courantes telles que REPLACE(), REGEXP_REPLACE(), INSERT() et CONCAT().

1. REPLACE

• Cas d’utilisation : Remplacer une sous‑chaîne « correspondance exacte » dans une chaîne ou une colonne par une autre chaîne.
• Caractéristiques : Sensible à la casse ; la plus simple pour un remplacement basique.
• Exemple : SELECT REPLACE('cat and dog', 'cat', 'fox'); -- → "fox and dog"

2. REGEXP_REPLACE (MySQL 8.0+)

• Cas d’utilisation : Remplacer les parties qui correspondent à un motif d’expression régulière.
• Caractéristiques : Idéal pour la correspondance de motifs complexes, le remplacement de plusieurs motifs et l’extraction/édition partielle.
• Exemple : SELECT REGEXP_REPLACE('a123b456c', '[a-z]', ''); -- → "123456"

3. INSERT

• Cas d’utilisation : « Insert » une chaîne en écrasant une longueur spécifiée à partir d’une position donnée.
• Caractéristiques : Convient pour un remplacement/insertion partiel, mais ressemble davantage à un écrasement qu’à un remplacement typique.
• Exemple : SELECT INSERT('abcdef', 2, 3, 'XYZ'); -- → "aXYZef"

4. CONCAT

• Cas d’utilisation : « Concatenate » plusieurs chaînes ou valeurs de colonnes.
• Caractéristiques : Pas destiné au remplacement/édition ; utilisé pour joindre des chaînes ensemble.
• Exemple : SELECT CONCAT('abc', '123'); -- → "abc123"

5. SUBSTRING / LEFT / RIGHT

• Cas d’utilisation : Extraire une partie d’une chaîne.
• Caractéristiques : Idéal pour découper et extraire des parties de données.
• Exemple : SELECT SUBSTRING('abcdef', 2, 3); -- → "bcd"

Quick Comparison Table

En choisissant la fonction appropriée en fonction de votre cas d’utilisation et de la version de MySQL, vous pouvez manipuler les données de manière plus efficace et sécurisée.

Performance et Précautions

Lors de l’exécution de remplacements massifs de chaînes dans MySQL, en particulier sur de grandes tables ou en production, vous pouvez rencontrer des problèmes inattendus ou une dégradation des performances. Voici les précautions essentielles et des conseils de performance pour réaliser ce travail en toute sécurité et efficacité.

1. Soyez prudent avec les mises à jour massives sur de grands ensembles de données

Les instructions UPDATE utilisant REPLACE() ou REGEXP_REPLACE() parcourent et réécrivent les lignes ciblées. Avec de grands ensembles de données, le temps d’exécution augmente et la charge du serveur peut devenir importante. Sur des tables contenant des dizaines de milliers à des millions de lignes, d’autres requêtes peuvent ralentir, et dans les pires cas, des verrous ou des dépassements de délai peuvent survenir.

2. Impact sur les index

Si un UPDATE modifie les valeurs dans des colonnes indexées (par ex., email, code), les index peuvent devoir être reconstruits. Cela peut affecter les performances. Pour éviter des réécritures inutiles, il est important de restreindre les données ciblées avec une clause WHERE.

3. Utilisez les transactions et les retours en arrière

Pour de grandes réécritures, l’utilisation d’une transaction vous permet d’annuler les modifications si une erreur survient ou si les résultats du remplacement ne sont pas conformes aux attentes.

START TRANSACTION;
UPDATE users SET comment = REPLACE(comment, 'A', 'B') WHERE ...;
-- If everything looks good
COMMIT;
-- If something goes wrong
ROLLBACK;

Cette approche vous aide à travailler avec plus de confiance.

4. Sauvegardez toujours en production

Avant d’effectuer des mises à jour batch de grande ampleur, effectuez toujours d’abord une sauvegarde. Si une corruption ou une perte de données inattendue survient, vous pouvez restaurer à partir de la sauvegarde.

5. Le traitement par lots ou l’exécution fractionnée peut aider

Si le nombre de lignes est extrêmement élevé, au lieu de tout mettre à jour en une fois, envisagez de diviser le travail en morceaux (par ex., par plages d’ID), ou de l’exécuter pendant les heures creuses afin de réduire la charge du serveur.

UPDATE logs
SET message = REPLACE(message, 'error', 'info')
WHERE id BETWEEN 1 AND 10000;

Une exécution pas à pas aide à répartir la charge du serveur.

En tenant compte des performances et de la sécurité, vous pouvez atteindre à la fois l’efficacité opérationnelle et la prévention des problèmes lors du remplacement de chaînes.

Étude de cas : exemples d’utilisation réels

Voici deux exemples pratiques de « remplacement de chaînes » utiles dans des opérations réelles. Les deux surviennent couramment lors de la maintenance de routine et de la gestion des données. Nous expliquerons le flux de travail et les précautions accompagnés des requêtes réelles.

Cas 1 : mise à jour massive des URL dans les descriptions de produits

Ce cas concerne un site e‑commerce où, après une refonte du site, vous souhaitez remplacer l’ancienne URL (old-shop.com) par la nouvelle URL (new-shop.jp) dans toutes les descriptions de produits.

Étapes d’exemple :

1. Sauvegardez la table products avant de commencer
2. Restreignez les lignes ciblées à l’aide d’une clause WHERE (exécutions de test recommandées)
3. Exécutez l’instruction UPDATE pour la correction massive

Exemple SQL réel :

UPDATE products
SET description = REPLACE(description, 'old-shop.com', 'new-shop.jp');

Précautions :

• Sauvegardez toujours et validez soigneusement dans un environnement de test avant d’appliquer en production
• Si des espaces ou des sauts de ligne peuvent entourer l’URL, envisagez également le remplacement par expression régulière (REGEXP_REPLACE)

Cas 2 : Normalisation des formats de données client

Le remplacement de chaînes est également utile pour normaliser les formats tels que les numéros de téléphone et les codes postaux dans les bases de données clients. Par exemple, pour supprimer les tirets de tous les numéros de téléphone et les rendre continus :

Exemple SQL réel :

UPDATE customers
SET tel = REPLACE(tel, '-', '');

Si vous utilisez MySQL 8.0 ou une version ultérieure, vous pouvez également utiliser les expressions régulières pour des corrections de format plus flexibles.

Exemple d’expression régulière (Normaliser les codes postaux au format « 123-4567 ») :

UPDATE customers
SET zipcode = REGEXP_REPLACE(zipcode, '([0-9]{3})-?([0-9]{4})', '\1-\2');

Précautions :

• Validez que les résultats sont conformes aux attentes avant d’appliquer les modifications aux données de production
• Si l’impact est important, limitez progressivement la portée de la mise à jour en utilisant une clause WHERE

Comme le montrent ces études de cas, le remplacement de chaînes MySQL est très utile pour la maintenance à grande échelle et le nettoyage de données récurrent. En sauvegardant et validant systématiquement au préalable, vous pouvez éviter les erreurs et les problèmes.

Résumé et liste de contrôle du travail

Jusqu’à présent, nous avons couvert comment remplacer des chaînes dans MySQL — des bases aux techniques avancées et aux exemples concrets. Enfin, résumons les points clés et fournissons une liste de contrôle utile lors de l’exécution d’opérations réelles.

Points clés à retenir

• REPLACE() est le meilleur choix pour un remplacement simple de chaînes en masse. Il est sensible à la casse et ne fonctionne que sur des correspondances exactes.
• REGEXP_REPLACE() (MySQL 8.0+) permet un remplacement avancé de motifs à l’aide d’expressions régulières.
• Le remplacement en masse avec UPDATE est pratique, mais les sauvegardes et la validation dans un environnement de test sont essentielles.
• L’imbrication de REPLACE() est efficace pour remplacer plusieurs motifs simultanément.
• Soyez attentif aux performances, à l’impact sur les index et à la charge du serveur lors du traitement de grands ensembles de données.

Liste de contrôle du travail

• □ Avez-vous identifié avec précision le motif à remplacer et la ou les colonnes cibles ?
• □ Avez-vous limité les mises à jour aux seules lignes nécessaires en utilisant une clause WHERE ?
• □ Avez-vous effectué une sauvegarde avant d’appliquer les modifications en production ?
• □ Avez-vous validé le comportement de la requête dans un environnement de test ou avec des données factices ?
• □ Disposez‑vous d’un plan pour utiliser le traitement par lots ou les plages LIMIT/ID en fonction du volume de données et de la charge du serveur ?
• □ Après l’opération, avez‑vous vérifié minutieusement que les résultats du remplacement correspondent aux attentes ?
• □ Avez‑vous choisi la fonction appropriée en fonction de votre version de MySQL ?

En suivant cette liste de contrôle, vous pouvez effectuer le remplacement de chaînes MySQL en toute sécurité et fiabilité.
Parce qu’une petite erreur peut entraîner de graves problèmes, assurez‑vous de vérifier chaque étape attentivement avant d’appliquer les modifications en production.

FAQ (Foire aux questions)

Nous résumons ici les questions courantes et les préoccupations concrètes concernant le « remplacement de chaînes MySQL ». Utilisez cette section pour réduire les incertitudes et travailler plus sereinement.

Q1. REPLACE() est‑il sensible à la casse ?

A1 : Oui. REPLACE() est sensible à la casse. Par exemple, « mysql » et « MySQL » sont traités comme des chaînes différentes. Si vous devez remplacer les deux, vous pouvez imbriquer REPLACE() deux fois ou utiliser une autre méthode.

Q2. Que se passe‑t‑il si je l’utilise sur une colonne contenant NULL ?

A2 : Si la colonne cible est NULL, le résultat de REPLACE() est également NULL, et rien ne change. Si vous souhaitez remplacer même lorsque des valeurs NULL existent, combinez‑le avec IFNULL().

Q3. Puis‑je remplacer plusieurs motifs en même temps ?

A3 : Oui. Vous pouvez imbriquer des appels à REPLACE() pour remplacer plusieurs motifs dans une même instruction. Cependant, les résultats peuvent varier selon l’ordre de remplacement, il faut donc valider au préalable.

Q4. Comment puis‑je remplacer en utilisant des expressions régulières ?

A4 : Utilisez REGEXP_REPLACE(), qui est disponible dans MySQL 8.0 et versions ultérieures. Si vous utilisez une version plus ancienne, envisagez un remplacement côté application ou une autre méthode.

Q5. Quelle est la différence avec REPLACE INTO?

A5 : REPLACE() est une fonction de « remplacement de chaîne », tandis que REPLACE INTO est une instruction SQL de type INSERT qui « supprime une ligne existante et insère une nouvelle ligne ». Elles servent à des objectifs complètement différents.

Q6. Puis-je restaurer les données originales après le remplacement ? (Récupération)

A6 : Dans la plupart des cas, il est difficile de revenir en arrière après un remplacement. Effectuez toujours une sauvegarde avant d’exécuter l’opération. Si quelque chose tourne mal, restaurez à partir de votre sauvegarde.

Q7. Comment puis‑je vérifier ma version de MySQL ?

A7 : Exécutez la requête suivante pour vérifier votre version de MySQL :

SELECT VERSION();

Vérifiez votre version à l’avance pour confirmer si des fonctions comme REGEXP_REPLACE() sont disponibles.

Utilisez ces FAQ pour effectuer les opérations de remplacement de chaînes en toute confiance.

Liens et références associés

Si vous souhaitez approfondir le remplacement de chaînes ou l’apprentissage du SQL, l’utilisation de la documentation officielle et de ressources techniques fiables est efficace. Nous présentons également des articles connexes et des outils utiles — veuillez les utiliser comme références.

1. Documentation officielle MySQL

• MySQL :: MySQL 8.0 Reference Manual :: 12.8.2 String Functions Une liste complète des fonctions de chaîne MySQL. Vous pouvez y confirmer les spécifications détaillées et les arguments de REPLACE() et REGEXP_REPLACE().

2. Exemples pratiques de REGEXP_REPLACE()

• Studying the regex functions added in MySQL 8 Comprend de nombreux exemples pratiques de remplacement basé sur les expressions régulières introduits dans MySQL 8.0.

4. Si vous voulez apprendre les bases du SQL

• MySQL Basics (Beginner) – Dotinstall Si vous souhaitez apprendre le SQL depuis le début, les ressources d’apprentissage vidéo peuvent également être utiles.