MySQL: Sauts de Ligne dans MySQL

Dans certains langages de programmation comme PHP ou Java, on peut utiliser les caractères respectivement les séquences d'échappement \r et \n pour un saut de ligne. En JavaScript, cependant, la séquence d'échappement \n représente à elle seule un saut de ligne complet tandis que d'autres langages de programmation utilisent des codes de caractères ou fournissent des fonctions ou des constantes correspondantes pour insérer les sauts de ligne.

Mais qu'en est-il de MySQL? Comment pouvons-nous travailler explicitement avec des sauts de ligne à l'aide de commandes SQL, par exemple pour rechercher spécifiquement du texte avec un saut de ligne dans une base de données, pour remplacer du texte sur plusieurs lignes, pour écrire du texte avec des sauts de ligne dans un tableau ou pour supprimer ou remplacer les sauts de ligne par un autre caractère?

Dans cet article, nous aimerions répondre à cette question avec les sections suivantes:

• Les Bases: Séquences d'Échappement et la Fonction CHAR()
• Sauts de Ligne Windows
• Sauts de Ligne Unix (Linux et macOS)
• Sauts de Ligne dans une Chaîne
• Utilisation de la Fonction CONCAT()
• Notation Alternative pour les Codes de Caractères
• Limitations de la Fonction CHAR()
• Sauts de Ligne Unicode (NEL, LS, PS, VT et FF)
• Sauts de Ligne en PHP et phpMyAdmin

Les Bases: Séquences d'Échappement et la Fonction CHAR()

MySQL nous propose essentiellement deux manières de spécifier des sauts de ligne: d'une part, nous pouvons utiliser les séquences d'échappement \r et \n connues de PHP, et d'autre part, MySQL nous fournit la fonction CHAR(), qui peut être utilisé avec un ou plusieurs codes ASCII puis renvoie les caractères correspondants. Le code ou le point de code de la plage ASCII pour un changement de ligne (Line Feed) est 10 (LF), celui pour un retour chariot (Carriage Return) est 13 (CR). Les séquences d'échappement correspondantes sont \r pour CR ainsi que \n pour LF.

Sauts de Ligne Windows

Ainsi, nous pouvons, par exemple, formuler la requête MySQL suivante pour les sauts de ligne Windows (CR LF):

UPDATE tab SET col = REPLACE(col, CHAR(13, 10), '');

Avec cela, nous remplaçons tous les sauts de ligne de type CR LF par rien - c'est-à-dire que nous supprimons complètement tous les sauts de ligne du texte de la colonne "col" dans le tableau "tab".

Au lieu d'utiliser la fonction CHAR(), nous pouvons également travailler avec les séquences d'échappement et insérer la chaîne '\r\n' dans notre requête au lieu de CHAR(13, 10), ce qui signifie la même chose et conduit au même résultat:

UPDATE tab SET col = REPLACE(col, '\r\n', '');

Soit dit en passant, à ce stade, peu importe que nous utilisions des guillemets simples ou doubles. PHP fait une distinction entre '\r\n' et "\r\n" et remplace uniquement la deuxième variante entre guillemets doubles par de vrais sauts de ligne tandis que la première variante reste telle quelle, mais MySQL ne fait pas de différence entre le type de guillemets et remplace les deux variantes par de vrais sauts de ligne (plus d'informations sur ce sujet dans la dernière section sur les sauts de ligne dans MySQL et PHP).

Sauts de Ligne Unix (Linux et macOS)

De manière analogue, d'autres types de sauts de ligne peuvent également être traités de la même manière en utilisant les autres codes de caractères ou séquences d'échappement correspondants au lieu de 13 et 10 respectivement \r et \n. Par exemple, les sauts de ligne de type Unix, courants sur les systèmes Linux ou macOS, constitués d'un seul caractère LF avec le code décimal 10:

UPDATE tab SET col = REPLACE(col, CHAR(10), ' ');

Avec cette requête UPDATE nous remplaçons toutes les occurrences de ce type de saut de ligne dans la colonne "col" du tableau "tab" par un espace.

Encore une fois, nous pouvons également travailler avec des séquences d'échappement et utiliser le caractère d'échappement pour le saut de ligne Unix \n pour LF:

UPDATE tab SET col = REPLACE(col, '\n', ' ');

Le résultat de ces deux requêtes est identique.

Sauts de Ligne dans une Chaîne

Jusqu'ici, nous avons utilisé les sauts de ligne uniquement seuls et non en conjonction avec d'autres caractères dans des chaînes plus longues. Ainsi, dans l'exemple suivant, nous aimerions voir comment rechercher un texte composé de plusieurs caractères incluant un saut de ligne, par exemple dans une colonne de type VARCHAR ou TEXT avec MySQL.

Le moyen le plus simple de procéder consiste à utiliser des séquences d'échappement:

SELECT id FROM tab WHERE col = 'Ligne 1\r\nLigne 2';

Cette requête recherche toutes les entrées de la table "tab" qui contient le texte sur les deux lignes avec la première ligne "Ligne 1" et la deuxième ligne "Ligne 2" dans la colonne "col". Pour l'exemple, nous avons utilisé un saut de ligne Windows. Pour un saut de ligne Unix, nous devrions omettre le "\r" et simplement écrire "Ligne 1\nLigne 2" à la place.

Bien sûr, nous pouvons également utiliser à nouveau la fonction CHAR() pour implémenter cette requête. Cependant, dans ce cas, nous devons réfléchir à la manière dont nous pouvons connecter le résultat de la fonction CHAR() avec nos autres parties de chaîne, car il existe certaines différences entre les différents systèmes de gestion de base de données et leurs configurations.

Dans de nombreux systèmes de gestion de bases de données tels que SQLite, Firebird, PostgreSQL ou Oracle, le symbole pipe (qui est utilisé comme OR dans MySQL) peut être utilisé pour connecter des chaînes de la manière suivante:

SELECT id FROM tab WHERE col = 'Ligne 1' || CHAR(13) || CHAR(10) || 'Ligne 2';

En dehors de cela, Microsoft SQL Server prend en charge le signe plus pour connecter plusieurs chaînes à la place:

SELECT id FROM tab WHERE col = 'Ligne 1' + CHAR(13) + CHAR(10) + 'Ligne 2';

Cependant, dans MySQL ainsi que dans MariaDB, qui est souvent utilisée comme alternative, aucune des deux variantes ne fonctionne dans le préréglage. Par défaut, ni MySQL ni MariaDB ne prennent en charge + ni || comme opérateurs pour les concaténations de chaînes et renverrait la valeur numérique 0 pour le dernier exemple car le plus est considéré comme un opérateur arithmétique (cependant, l'opérateur || peut également être activé dans MySQL et MariaDB en utilisant le mode SQL PIPES_AS_CONCAT, qui est désactivé par défaut, par exemple en utilisant l'instruction "SET sql_mode = (SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));").

Sans modifier les paramètres par défaut, MySQL et MariaDB utilisent la fonction CONCAT() pour les concaténations de chaînes, que nous examinerons dans la section suivante. Outre MySQL et MariaDB, Microsoft SQL Server, Oracle et PostgreSQL prennent également en charge cette fonction de concaténation de chaînes.

Utilisation de la Fonction CONCAT()

Comme alternative à l'utilisation du caractère double barre / pipe || ou le signe plus + pour la composition de la chaîne, comme nous l'avons vu dans les exemples de la section précédente, nous pouvons également utiliser la fonction CONCAT() dans le même but dans MySQL, MariaDB, Microsoft SQL Server, Oracle et PostgreSQL, auquel nous peut transmettre n'importe quel nombre de paramètres sous la forme de chaînes simples. Entre autres, ces chaînes uniques peuvent être constituées à la fois de texte entre guillemets et de caractères uniques générés par CHAR().

L'exemple de la dernière section peut être implémenté de la manière suivante en utilisant CONCAT():

SELECT id FROM tab WHERE col = CONCAT('Ligne 1', CHAR(13), CHAR(10), 'Ligne 2');

Comme nous pouvons le constater, nous pouvons simplement séparer les différentes parties par une virgule, ce qui bien sûr s'applique également à nos séquences d'échappement, comme le montre notre exemple suivant:

SELECT id FROM tab WHERE col = CONCAT('Ligne 1', '\r', '\n', 'Ligne 2');

Contrairement à PHP, peu importe encore une fois que nous mettions nos séquences d'échappement entre guillemets simples ou doubles. SQL accepte les deux variantes, tandis qu'en PHP, seuls les guillemets doubles fonctionnent, dont nous parlerons plus en détail dans la dernière section.

Notation Alternative pour les Codes de Caractères

Dans tous les exemples précédents jusqu'à présent, nous avons toujours utilisé la fonction CHAR() avec des codes de caractères décimaux pour nos caractères de saut de ligne. Soit 13 pour CR (Carriage Return) ainsi que 10 pour LF (Line Feed). Cependant, MySQL nous permet également de spécifier ces points de code en notation hexadécimale. La notation hexadécimale de 13 est 0D, tandis que la notation hexadécimale de 10 est 0A. Pour signaler à MySQL que notre spécification est un nombre hexadécimal, nous devons le préfixer par 0x. Regardons un exemple pour cela:

UPDATE tab SET col = CONCAT('a', CHAR(0x0D), CHAR(0x0A), 'b');

Avec cette commande SQL, nous définissons le contenu de la colonne "col" sur un texte sur deux lignes composé des lignes "a" et "b". Pour le saut de ligne Windows entre les deux lignes, nous utilisons la notation hexadécimale CHAR(0x0D) et CHAR(0x0A) au lieu de CHAR(13) et CHAR(10).

La spécification du saut de ligne Windows peut être encore simplifiée en contractant les caractères individuels 0D et 0A en 0x0D0A de la manière suivante et en n'appelant ainsi la fonction CHAR() qu'une seule fois:

UPDATE tab SET col = CONCAT('a', CHAR(0x0D0A), 'b');

Lorsque nous utilisons la notation hexadécimale, nous pouvons même omettre complètement CHAR(), comme le montre l'exemple suivant:

UPDATE tab SET col = CONCAT('a', 0x0D0A, 'b');

Et selon le système de base de données (SQLite/Oracle/PostgreSQL/Firebird) ou même en mode PIPES_AS_CONCAT de MySQL et MariaDB, cela peut aussi se faire sans CONCAT():

UPDATE tab SET col = 'a' || 0x0D0A || 'b';

Chacun de ces quatre exemples d’appels différents nous donne exactement le même résultat.

En utilisant la notation décimale, les notations présentées ici ne seraient pas possibles. Outre le fait qu'on ne peut pas écrire "1310" car, contrairement à la notation hexadécimale, il ne serait pas clair que le nombre décimal "1310" fait référence à deux nombres individuels, un appel à 'a' + 10 + 13 + 'b' pourrait conduire à ce que les deux nombres soient interprétés comme une chaîne ou une ligne de nombres et le résultat serait (selon le système de gestion de base de données) le texte "a1013b" ou une valeur numérique au lieu d'un saut de ligne.

Limitations de la Fonction CHAR()

Important: Nous ne pouvons pas simplement appeler la fonction CHAR() utilisée ici avec des points de code arbitraires pour convertir des points de code arbitraires en leurs équivalents de caractères, mais nous devons garder à l'esprit le codage utilisé et la possibilité d'obtenir des caractères multi-octets.

Il est relativement peu problématique d'utiliser des points de code de la plage ASCII avec ses nombres entiers de 0 à 127, qui inclut également nos codes pour le saut de ligne Windows (13 et 10) ainsi que le saut de ligne Unix (10). Par exemple, vous pouvez utiliser CHAR(65) pour créer la lettre "A" (point de code U+0041, décimal 65):

SELECT CHAR(65);                 -- A

Cependant, cela devient plus difficile avec les caractères représentés par plusieurs octets dans le codage que nous utilisons. Par exemple, si notre base de données utilise le standard collation UTF-8, nous ne pouvons pas simplement écrire CHAR(196) pour créer la lettre "Ä". La lettre "Ä" a le point de code Unicode U+00C4, qui correspond au nombre décimal 196, mais CHAR() produit une séquence d'octets qui est C3 84 pour "Ä" au lieu de C4 dans le codage UTF-8. Pour cette raison, nous devons transmettre exactement cette séquence d'octets pour la lettre "Ä" lorsque nous utilisons un standard collation UTF-8 de notre base de données (soit directement sous forme hexadécimale via 0xC384, soit sous forme décimale comme 50052):

SELECT CHAR(196);                -- 0xc4
SELECT CHAR(50052);              -- Ä
SELECT CHAR(0xC384);             -- Ä

Si nous souhaitons générer des caractères à l'aide de points de code ou de séquences d'octets qui s'écartent du classement standard de notre base de données, nous pouvons également définir notre jeu de caractères préféré à l'aide de l'extension USING:

SELECT CHAR(196 USING LATIN1);   -- Ä
SELECT CHAR(0xC384 USING UTF8);  -- Ä
SELECT CHAR(0x00C4 USING UTF16); -- Ä
SELECT CHAR(0xD0A8 USING UTF8);  -- Ш

Comme le montre la première ligne, il est ainsi possible de créer la lettre "Ä" en utilisant le nombre décimal 196 en utilisant le jeu de caractères Latin-1, puisque la lettre "Ä" en Latin-1 n'est codée qu'en utilisant le un octet C4 (196). De même, nous pouvons transmettre la séquence d'octets C3 84 pour "Ä" dans le codage UTF-8 ou la séquence d'octets 00 C4 pour "Ä" dans le codage UTF-16 ainsi que toute autre séquence d'octets pour générer les caractères correspondants.

Cette base est importante pour générer des sauts de ligne dont les points de code sont en dehors de la plage ASCII, comme les types de sauts de ligne Unicode que nous examinerons dans la section suivante en utilisant la même approche.

Sauts de Ligne Unicode (NEL, LS, PS, VT et FF)

La norme Unicode exige que les caractères NEL (Next Line, Ligne Suivante, U+0085), LS (Line Separator, Séparateur de Ligne, U+2028), PS (Paragraph Separator, Séparateur de Paragraphe, U+2029), VT (Vertical Tab, Onglet Vertical, U+000B) ainsi que FF (Form Feed, Saut de Formulaire, U+000C) doivent également être interprétés comme des sauts de ligne. Pour cette raison, nous aimerions voir comment utiliser ces types de sauts de ligne dans nos instructions MySQL. Comme déjà expliqué dans la dernière section, pour cela, nous pouvons à nouveau utiliser la fonction CHAR().

Le tableau suivant montre pour tous les caractères avec quelles séquences d'octets UTF-8 et UTF-16 nous pouvons appeler CHAR() pour créer le caractère en question:

Caractère		Point de Code	UTF-8	UTF-16
VT	Vertical Tab	U+000B	CHAR(0x0B USING UTF8)	CHAR(0x000B USING UTF16)
FF	Form Feed	U+000C	CHAR(0x0C USING UTF8)	CHAR(0x000C USING UTF16)
NEL	Next Line	U+0085	CHAR(0xC285 USING UTF8)	CHAR(0x0085 USING UTF16)
LS	Line Separator	U+2028	CHAR(0xE280A8 USING UTF8)	CHAR(0x2028 USING UTF16)
PS	Paragraph Separator	U+2029	CHAR(0xE280A9 USING UTF8)	CHAR(0x2029 USING UTF16)

En pratique, nous pouvons simplement utiliser ces caractères dans nos instructions SQL, comme nous l'avons déjà montré pour les autres types de saut de ligne. Par exemple, pour remplacer tous les sauts de ligne Windows (spécifiés ici à l'aide de séquences d'échappement) par le caractère NEL:

UPDATE tab SET col = REPLACE(col, '\r\n', CHAR(0xC285 USING UTF8));

Ou pour rechercher tous les enregistrements contenant le caractère Line Separator LS:

SELECT * FROM tab WHERE col LIKE CONCAT('%', CHAR(0x2028 USING UTF16), '%');

Comme le montrent les exemples, nous pouvons utiliser les caractères de saut de ligne Unicode générés avec CHAR() individuellement comme d'habitude ou combinés à l'aide de la fonction CONCAT(). Les séquences d'échappement pour les points de code Unicode connues dans de nombreux autres langages de programmation, tels que \x2028, \u2028 ou \U{2028} pour le Line Separator ne sont pas prises en charge par MySQL.

Sauts de Ligne en PHP et phpMyAdmin

Généralement, nous pouvons utiliser les commandes SQL présentées ici de la même manière également dans notre code PHP ou dans phpMyAdmin. Cependant, il faut considérer que \r et \n ne sont pas toujours convertis en caractères U+000A respectivement U+000D, mais peuvent aussi rester comme tel sous certaines conditions.

Les facteurs décisifs sont d'une part le type de guillemets utilisés et d'autre part l'utilisation des fonctions d'échappement typiques de MySQL mysqli_real_escape_string ou mysqli::real_escape_string. Dans ce qui suit, nous aimerions analyser quelques exemples qui démontrent le comportement différent des différentes combinaisons de guillemets et d’échappements.

Tout d’abord, nous aimerions jeter un œil à cet exemple simple avec lequel nous voulons définir le contenu d’une colonne sur le texte de la variable $txt. $txt se compose de deux lignes que nous avons séparées par le saut de ligne Windows \r\n.

$txt = "Ligne 1\r\nLigne 2";
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";

Nous avons défini le texte de la variable $txt à l'aide de guillemets doubles. Ceci est crucial à ce stade, car PHP fait une distinction entre les guillemets doubles et simples. Les séquences d'échappement telles que notre saut de ligne \r\n ne sont résolues en véritables sauts de ligne que si elles sont placées entre guillemets doubles. Toutefois, s'ils sont placés entre guillemets simples, les caractères restent tels quels.

Pour cette raison, dans cet exemple, PHP convertit \r\n en un véritable saut de ligne avant même que notre chaîne n'atteigne la base de données. Le résultat est un saut de ligne dans la base de données et même si nous produisons $txt avec echo ou via vardump() un saut de ligne est créé dans le code source de notre page HTML (uniquement dans le code source, pas dans l'affichage du navigateur car les sauts de ligne et autres espaces y sont ignorés).

Nous aimerions maintenant modifier notre exemple sur un seul détail: au lieu des guillemets doubles, nous utilisons des guillemets simples dans notre exemple suivant:

$txt = 'Ligne 1\r\nLigne 2';
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";  

Cela conduit au même résultat dans la base de données mais à un résultat différent dans le code source. Bien que ce code crée à nouveau un véritable saut de ligne dans notre base de données, la chaîne \r\n reste telle quelle dans le texte source lorsque $txt est généré. Comment est-ce possible? Les guillemets simples garantissent que PHP n'interprète ni ne convertit les séquences d'échappement. Cela signifie que \r et \n restent tels quels dans la variable $txt jusqu'à la fin et que la sortie est effectuée en conséquence. Cependant, cette fois la conversion ne s'effectue pas au niveau PHP mais au niveau MySQL. MySQL ne convertit \r\n en un saut de ligne qu'après avoir reçu la requête et l'écrit dans la base de données.

Pour des raisons de sécurité et pour éviter toute interférence avec certains caractères comme les guillemets dans un texte, il est nécessaire d'échapper le texte avant de le diffuser dans notre base de données. Cela se fait généralement dans un style procédural avec la fonction mysqli_real_escape_string() respectivement avec mysqli::real_escape_string si nous utilisons le style orienté objet.

Voyons comment le résultat de notre code change lorsque nous échappons notre chaîne avec mysqli_real_escape_string() avant de l'insérer dans la base de données:

$txt = "Ligne 1\r\nLigne 2";
$txt = mysqli_real_escape_string($db, $txt);
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";
$res = mysqli_query($db, $sql);

Ce code entraîne également un véritable saut de ligne à la fois dans la base de données et dans le code source. La raison pour cela est que nous utilisons des guillemets doubles sur la première ligne lors de la définition de la variable $txt. Ainsi, nos caractères \r et \n ont donc déjà été convertis en véritables sauts de ligne avant de s'échapper, ce que notre fonction d'échappement ne peut plus les modifier.

Cependant, les choses se comportent différemment si nous appelons le même code avec des guillemets simples pour la définition de la variable:

$txt = 'Ligne 1\r\nLigne 2';
$txt = mysqli_real_escape_string($db, $txt);
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";
$res = mysqli_query($db, $sql);

Cette fois, PHP ne convertit pas \r et \n en sauts de ligne à cause des guillemets simples. La sortie dans le code source conserve \r\n comme nous l'avons vu dans notre deuxième exemple. Cependant, cette fois, MySQL ne le convertit pas non plus, donc notre chaîne d'origine est écrite dans la base de données avec \r\n. Ceci est assuré par la fonction d'échappement dans ce code, qui a converti (échappé) notre \r\n en \\r\\n avant la commande UPDATE, ce qui signifie que MySQL ne le convertit pas en sauts de ligne et écrit le \r\n ré-échappé dans la base de données.

Jusqu'à présent, nous écrivions toutes nos requêtes entre guillemets doubles, ce qui faisait que PHP insérait notre variable $txt dans la requête sous la forme de son contenu. Pour MySQL, une telle requête ressemblait à ceci après avoir inséré $txt (ici avec les lignes a et b):

$sql = "UPDATE tab SET col = 'a\r\nb' WHERE id = 1";

Cependant, si nous écrivons l’intégralité de la requête entre guillemets simples, nous devons nous-mêmes échapper aux guillemets qui entourent notre texte pour qu’il continue à fonctionner:

$sql = 'UPDATE tab SET col = \'a\r\nb\' WHERE id = 1';

Cette requête crée également un saut de ligne dans la base de données, mais vous ne pouvez pas le voir dans le code source (ce serait 'a\r\nb'). On obtient le même résultat aux deux niveaux avec un double échappement:

$sql = 'UPDATE tab SET col = \'a\\r\\nb\' WHERE id = 1';

Seulement avec un triple échappement, comme nous le voyons dans la requête suivante, nous obtenons le texte 'a\r\nb' dans la base de données sans véritable saut de ligne, alors que dans le code source le texte 'a\\r\\nb' (également sans saut de ligne) est affiché:

$sql = 'UPDATE tab SET col = \'a\\\r\\\nb\' WHERE id = 1';

En plus de ces cas particuliers, il faut enfin mentionner que nous pouvons également utiliser des sauts de ligne en tant que tels dans notre code PHP ainsi que dans le prompt SQL de phpMyAdmin:

$sql = "UPDATE tab SET col = 'Ligne 1
Ligne 2' WHERE id = 1";

Cette requête crée un saut de ligne à la fois dans le code source et dans la base de données, indépendamment d'un échappement ou de la fonction CHAR().