Remplacer les caractères \\r\\n par un retour à la ligne dans MySQL

À la suite d’une migration de base de données, il peut arriver que certains retours à la ligne se retrouvent transformés en texte littéral \r\n. La correction reste très simple avec MySQL ou MariaDB, mais il est préférable de commencer par vérifier exactement ce qui est stocké avant d’exécuter un UPDATE.

Le problème : deux caractères visibles au lieu d’un saut de ligne

Il faut distinguer un vrai retour à la ligne des caractères littéraux « barre oblique inverse + r + barre oblique inverse + n ». Si vos données contiennent réellement la chaîne \r\n, vous pouvez la remplacer par un caractère de fin de ligne avec la fonction SQL REPLACE().

Vérifier les lignes concernées

Dans un WordPress classique, le contenu des articles se trouve dans la colonne post_content de la table des publications. Le préfixe n’est pas forcément wp_, adaptez donc le nom de la table à votre installation.

SELECT ID, post_title
FROM wp_posts
WHERE post_content LIKE '%\\\\r\\\\n%'
LIMIT 20;

Cette requête permet de vérifier que l’on cible bien la chaîne littérale \r\n avant de modifier les données.

Remplacer \r\n par un saut de ligne

Pour remplacer cette chaîne par un saut de ligne Unix, on peut utiliser CHAR(10) :

UPDATE wp_posts
SET post_content = REPLACE(
    post_content,
    '\\r\\n',
    CHAR(10)
)
WHERE post_content LIKE '%\\\\r\\\\n%';

REPLACE() recherche toutes les occurrences de la chaîne indiquée et les remplace par la nouvelle valeur. CHAR(10) produit le caractère LF, utilisé comme saut de ligne sur les systèmes Unix et Linux.

Conserver un retour à la ligne Windows

Si vous avez réellement besoin d’un retour Windows CRLF plutôt que d’un simple LF, utilisez les deux caractères :

UPDATE ma_table
SET ma_colonne = REPLACE(
    ma_colonne,
    '\\r\\n',
    CONCAT(CHAR(13), CHAR(10))
)
WHERE ma_colonne LIKE '%\\\\r\\\\n%';

Pour du contenu destiné au web, un simple CHAR(10) est généralement suffisant.

Adapter la requête à une autre application

La technique n’est pas propre à WordPress. Il suffit de remplacer le nom de table et le nom de colonne :

UPDATE ma_table
SET ma_colonne = REPLACE(ma_colonne, '\\r\\n', CHAR(10))
WHERE ma_colonne LIKE '%\\\\r\\\\n%';

Tester avec une transaction

Avec un moteur transactionnel comme InnoDB, vous pouvez tester la modification avant de la valider :

START TRANSACTION;

UPDATE wp_posts
SET post_content = REPLACE(post_content, '\\r\\n', CHAR(10))
WHERE post_content LIKE '%\\\\r\\\\n%';

SELECT ID, post_title, post_content
FROM wp_posts
WHERE post_content LIKE CONCAT('%', CHAR(10), '%')
LIMIT 10;

-- Si le résultat est correct :
COMMIT;

-- Sinon, à la place :
-- ROLLBACK;

Et si le problème concerne de vrais CRLF ?

Si la colonne contient de vrais caractères CRLF et non le texte \r\n, la recherche est différente. Pour normaliser les fins de ligne Windows vers Unix :

UPDATE ma_table
SET ma_colonne = REPLACE(
    ma_colonne,
    CONCAT(CHAR(13), CHAR(10)),
    CHAR(10)
);

Cette seconde requête traite donc un cas différent : des caractères de contrôle réellement enregistrés dans la colonne.

Documentation

MySQL 8.4 — fonctions de chaînes REPLACE() et CHAR()