MySQL: Saltos de Línea en MySQL

En algunos lenguajes de programación como PHP o Java, podemos utilizar los caracteres respectivamente las secuencias de escape \r y \n para un salto de línea. En JavaScript, sin embargo, la secuencia de escape \n por sí sola representa un salto de línea completo, mientras que otros lenguajes de programación usan códigos de caracteres o proporcionan funciones o constantes correspondientes para insertar los saltos de línea.

¿Pero qué pasa con MySQL? ¿Cómo podemos trabajar explícitamente con saltos de línea usando comandos SQL, por ejemplo, para buscar específicamente texto con un salto de línea en una base de datos, para reemplazar texto en varias líneas, para escribir texto con saltos de línea en una tabla o para eliminar o reemplazar saltos de línea con otro carácter?

En este artículo nos gustaría responder a esta pregunta con las siguientes secciones:

• Conceptos Básicos: Secuencias de Escape y la Función CHAR()
• Saltos de Línea Windows
• Saltos de Línea Unix (Linux y macOS)
• Saltos de Línea dentro de una Cadena
• Uso de la Función CONCAT()
• Notación Alternativa para los Códigos de Caracteres
• Limitaciones de la Función CHAR()
• Saltos de Línea Unicode (NEL, LS, PS, VT y FF)
• Saltos de Línea en PHP y phpMyAdmin

Conceptos Básicos: Secuencias de Escape y la Función CHAR()

MySQL básicamente nos ofrece dos caminos de especificar los saltos de línea: por un lado, podemos usar las secuencias de escape \r y \n conocidas de PHP, y por otro lado, MySQL nos proporciona la función CHAR(), que puede ser se utiliza con uno o más códigos ASCII y luego devuelve los caracteres correspondientes. El código respectivamente el punto de código del rango ASCII para un cambio de línea (Line Feed) es 10 (LF), el de un retorno de carro (Carriage Return) es 13 (CR). Las secuencias de escape correspondientes son \r para CR y \n para LF.

Saltos de Línea Windows

En consecuencia, podemos, por ejemplo, formular la siguiente consulta MySQL para los saltos de línea de Windows (CR LF):

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

Con esto reemplazamos todos los saltos de línea del tipo CR LF por nada, es decir, eliminamos por completo todos los saltos de línea del texto de la columna "col" en la tabla "tab".

En lugar de usar la función CHAR(), también podemos trabajar con las secuencias de escape e insertar la cadena '\r\n' en nuestra consulta en lugar de CHAR(13, 10), lo que significa lo mismo y conduce al mismo resultado:

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

Por cierto, en este punto, no importa si usamos comillas simples o dobles. PHP hace una distinción entre '\r\n' y "\r\n" y sólo reemplaza la segunda variante entre comillas dobles con saltos de línea reales mientras que la primera variante permanece como tal, pero MySQL no diferencia entre el tipo de comillas y reemplaza ambas variantes con saltos de línea reales (más sobre este tema en la última sección sobre saltos de línea en MySQL y PHP).

Saltos de Línea Unix (Linux y macOS)

De manera análoga, también se pueden procesar los otros tipos de saltos de línea de la misma manera utilizando los otros códigos de caracteres correspondientes o secuencias de escape en lugar de 13 y 10 respectivamente \r y \n. Por ejemplo, los saltos de línea del tipo Unix, que son comunes en los sistemas Linux o macOS, que constan de un solo carácter LF con el código decimal 10:

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

Con esta consulta UPDATE reemplazamos todas las apariciones de este tipo de salto de línea en la columna "col" de la tabla "tab" con un espacio.

Nuevamente, podemos trabajar alternativamente con secuencias de escape y usar el carácter de escape para el salto de línea Unix \n para LF:

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

El resultado de ambas consultas es idéntico.

Saltos de Línea dentro de una Cadena

Hasta aquí, hemos utilizado los saltos de línea solos y no junto con otros caracteres dentro de cadenas más largas. Por tanto, en el siguiente ejemplo nos gustaría ver cómo podemos buscar un texto formado por varios caracteres incluyendo un salto de línea, por ejemplo en una columna del tipo VARCHAR o TEXT con MySQL.

La forma más sencilla de hacerlo es con la ayuda de secuencias de escape:

SELECT id FROM tab WHERE col = 'Línea 1\r\nLínea 2';

Esta consulta busca todas las entradas de la tabla "tab" que contiene el texto de dos líneas con la primera línea "Línea 1" y la segunda línea "Línea 2" en la columna "col". Para el ejemplo, utilizamos un salto de línea de Windows. Para un salto de línea Unix, tendríamos que omitir "\r" y simplemente escribir "Línea 1\nLínea 2".

Por supuesto, también podemos utilizar de nuevo la función CHAR() para implementar esta consulta. Sin embargo, en este caso tenemos que pensar en cómo podemos conectar el resultado de la función CHAR() con nuestras otras partes de cadena, porque existen algunas diferencias entre los distintos sistemas de administración de bases de datos y sus configuraciones.

En muchos sistemas de gestión de bases de datos como SQLite, Firebird, PostgreSQL u Oracle, el símbolo de pipe (que se utiliza como OR en MySQL) se puede utilizar para conectar cadenas de la siguiente manera:

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

Aparte de eso, Microsoft SQL Server admite el signo más para conectar varias cadenas:

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

Sin embargo, en MySQL y también en MariaDB, que se utiliza a menudo como alternativa, ninguna de las dos variantes funciona en la configuración predeterminada. De forma predeterminada, ni MySQL ni MariaDB admiten + ni || como operadores para concatenaciones de cadenas y devolvería el valor numérico 0 para el último ejemplo porque el signo más se considera un operador aritmético (aunque el operador || también se puede activar en MySQL y MariaDB mientras se usa el modo SQL PIPES_AS_CONCAT, que está desactivado de forma predeterminada, por ejemplo, usando la declaración "SET sql_mode = (SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));").

Sin cambiar la configuración predeterminada, MySQL y MariaDB usan la función CONCAT() para concatenaciones de cadenas, que veremos en la siguiente sección. Además de MySQL y MariaDB, también Microsoft SQL Server, Oracle y PostgreSQL admiten esta función para concatenar cadenas.

Uso de la Función CONCAT()

Como alternativa al uso del carácter de barra vertical doble / pipe || o el signo más + para la composición de la cadena / string, como vimos en los ejemplos del apartado anterior, también podemos usar la función CONCAT() para el mismo propósito en MySQL, MariaDB, Microsoft SQL Server, Oracle y PostgreSQL, a la que podemos pasar cualquier número de parámetros en forma de cadenas individuales. Entre otras cosas, estas cadenas individuales pueden consistir tanto en texto entre comillas como en caracteres individuales generados por CHAR().

El ejemplo de la última sección se puede implementar de la siguiente manera usando CONCAT():

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

Como podemos ver, podemos simplemente separar las partes individuales con una coma, lo que por supuesto también se aplica a nuestras secuencias de escape, como muestra el siguiente ejemplo:

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

A diferencia de PHP, nuevamente es irrelevante si ponemos nuestras secuencias de escape entre comillas simples o dobles. SQL acepta ambas variantes, mientras que en PHP solo funcionan las comillas dobles, que discutiremos con más detalle en la última sección.

Notación Alternativa para los Códigos de Caracteres

En todos los ejemplos anteriores hasta ahora, siempre hemos utilizado la función CHAR() con códigos de caracteres decimales para nuestros caracteres de salto de línea. Es decir, 13 para CR (Carriage Return) y 10 para LF (Line Feed). Sin embargo, MySQL también nos permite especificar estos puntos de código en notación hexadecimal. La notación hexadecimal del 13 es 0D, mientras que la notación hexadecimal del 10 es 0A. Para indicarle a MySQL que nuestra especificación es un número hexadecimal, debemos anteponerle el prefijo 0x. Veamos un ejemplo para esto:

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

Con este comando SQL establecemos el contenido de la columna "col" en un texto de dos líneas compuesto por las líneas "a" y "b". Para el salto de línea de Windows entre las dos líneas utilizamos la notación hexadecimal CHAR(0x0D) y CHAR(0x0A) en lugar de CHAR(13) y CHAR(10).

La especificación del salto de línea de Windows se puede simplificar aún más contrayendo los caracteres individuales 0D y 0A a 0x0D0A de la siguiente manera y así solo teniendo que llamar a la función CHAR() una vez:

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

Cuando usamos la notación hexadecimal, podemos incluso omitir CHAR() por completo, como muestra el siguiente ejemplo:

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

Y dependiendo del sistema de base de datos (SQLite/Oracle/PostgreSQL/Firebird) o incluso en el modo PIPES_AS_CONCAT de MySQL y MariaDB, también se puede hacerlo sin CONCAT():

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

Cada una de estas cuatro llamadas de ejemplo diferentes nos da exactamente el mismo resultado.

Al usar la notación decimal, las notaciones que se muestran aquí no serían posibles. Aparte del hecho de que no podemos escribir "1310" porque, a diferencia de la notación hexadecimal, no estaría claro que el número decimal "1310" se refiere a dos números individuales, una llamada a 'a' + 10 + 13 + 'b' podría llevar a que los dos números se interpreten como una cadena o una fila de números y el resultado sería (dependiendo del sistema de gestión de la base de datos) el texto "a1013b" o un valor numérico en lugar de un salto de línea.

Limitaciones de la Función CHAR()

Importante: No podemos simplemente llamar a la función CHAR() utilizada aquí con puntos de código arbitrarios para convertir puntos de código arbitrarios en sus equivalentes de caracteres, sino que debemos tener en cuenta la codificación utilizada y la posibilidad de caracteres multibyte resultantes.

Es relativamente poco problemático utilizar puntos de código del rango ASCII con sus números enteros de 0 a 127, que también incluye nuestros códigos para el salto de línea de Windows (13 y 10), así como el salto de línea de Unix (10). Por ejemplo, puede utilizar CHAR(65) para crear la letra "A" (punto de código U+0041, decimal 65):

SELECT CHAR(65);                 -- A

Sin embargo, se vuelve más difícil con caracteres que están representados por múltiples bytes en la codificación que usamos. Por ejemplo, si nuestra base de datos utiliza la standard collation UTF-8, no podemos simplemente escribir CHAR(196) para crear la letra "Ä". La letra "Ä" tiene el punto de código Unicode U+00C4, que corresponde al número decimal 196, pero CHAR() produce una secuencia de bytes que es C3 84 para "Ä" en lugar de C4 en la codificación UTF-8. Por esta razón, tenemos que pasar exactamente esta secuencia de bytes para la letra "Ä" cuando usamos una standard collation UTF-8 de nuestra base de datos (ya sea directamente en forma hexadecimal a través de 0xC384 o en forma decimal como 50052):

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

Si queremos generar caracteres usando puntos de código o secuencias de bytes que se desvían de la clasificación estándar de nuestra base de datos, también podemos definir nuestro conjunto de caracteres preferido usando la extensión USING:

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

Como muestra la primera línea, con esto es posible crear la letra "Ä" usando el número decimal 196 usando el juego de caracteres Latin-1, ya que la letra "Ä" en Latin-1 solo se codifica usando el un byte C4 (196). De manera similar, podemos pasar la secuencia de bytes C3 84 para "Ä" en la codificación UTF-8 o la secuencia de bytes 00 C4 para "Ä" en la codificación UTF-16, así como cualquier otra secuencia de bytes para generar los caracteres correspondientes.

Esta base es importante para generar saltos de línea cuyos puntos de código están fuera del rango ASCII, como los tipos de salto de línea Unicode que veremos en la siguiente sección usando el mismo enfoque.

Saltos de Línea Unicode (NEL, LS, PS, VT y FF)

El estándar Unicode requiere que los caracteres NEL (Next Line, Siguiente Línea, U+0085), LS (Line Separator, Separador de Línea, U+2028), PS (Paragraph Separator, Separador de Párrafo, U+2029), VT (Vertical Tab, Tab Vertical, U+000B), así como FF (Avance de Formulario, Form Feed, U+000C) también deben interpretarse como saltos de línea. Por esta razón, nos gustaría ver cómo podemos usar estos tipos de saltos de línea en nuestras declaraciones MySQL. Como ya se explicó en el último apartado, para ello podemos volver a utilizar la función CHAR().

La siguiente tabla muestra para todos los caracteres con qué secuencias de bytes UTF-8 y UTF-16 podemos llamar a CHAR() para crear el carácter en cuestión:

Carácter		Punto de Código	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 la práctica, podemos simplemente usar estos caracteres en nuestras sentencias SQL, como ya lo hemos mostrado para los otros tipos de salto de línea. Por ejemplo, para reemplazar todos los saltos de línea de Windows (especificados aquí mediante secuencias de escape) con el carácter NEL:

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

O para buscar todos los registros que contienen el carácter Line Separator LS:

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

Como muestran los ejemplos, podemos usar los caracteres de salto de línea Unicode generados con CHAR() individualmente como de costumbre o combinados usando la función CONCAT(). Las secuencias de escape para puntos de código Unicode conocidos de muchos otros lenguajes de programación, como \x2028, \u2028 o \U{2028} para el Line Separator no son compatibles con MySQL.

Saltos de Línea en PHP y phpMyAdmin

Generalmente, podemos usar los comandos SQL que se muestran aquí de la misma manera también dentro de nuestro código PHP o en phpMyAdmin. Sin embargo, debemos considerar que \r y \n no siempre se convierten a los caracteres U+000A respectivamente U+000D, sino que también pueden permanecer como tal bajo ciertas condiciones.

Los factores decisivos son, por un lado, el tipo de comillas utilizadas y, por otro, el uso de las funciones de escape típicas de MySQL, mysqli_real_escape_string o mysqli::real_escape_string. A continuación, nos gustaría analizar algunos ejemplos que demuestran el diferente comportamiento de las distintas combinaciones de comillas y escapes.

Primero, nos gustaría echar un vistazo a este sencillo ejemplo con el que queremos establecer el contenido de una columna al texto de la variable $txt. $txt consta de dos líneas que hemos separado con el salto de línea de Windows \r\n.

$txt = "Línea 1\r\nLínea 2";
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";

Definimos el texto para la variable $txt usando comillas dobles. Esto es crucial en este punto, ya que PHP hace una distinción entre comillas simples y dobles. Las secuencias de escape como nuestro salto de línea \r\n solo se resuelven en saltos de línea reales si se colocan entre comillas dobles. Sin embargo, si se ponen entre comillas simples, los caracteres permanecen como tales.

Por esta razón, en este ejemplo, PHP convierte \r\n en un salto de línea real incluso antes de que nuestra cadena llegue a la base de datos. El resultado es un salto de línea en la base de datos e incluso si generamos $txt con echo o vía vardump() se crea un salto de línea en el código fuente de nuestra página HTML (sólo en el código fuente, no en la visualización del navegador porque allí se ignoran los saltos de línea y otros espacios en blanco).

Ahora nos gustaría cambiar nuestro ejemplo con solo un detalle: en lugar de las comillas dobles, usamos comillas simples en nuestro siguiente ejemplo:

$txt = 'Línea 1\r\nLínea 2';
$sql = "UPDATE tab SET col = '$txt' WHERE id = 1";  

Esto lleva al mismo resultado en la base de datos pero a un resultado diferente en el código fuente. Si bien este código nuevamente crea un salto de línea real en nuestra base de datos, la cadena \r\n permanece en el texto fuente como tal cuando se genera $txt. ¿Como puede ser? Las comillas simples garantizan que PHP no interprete ni convierta las secuencias de escape. Esto significa que \r y \n permanecen como tales en la variable $txt hasta el final y la salida se realiza en consecuencia. Sin embargo, esta vez la conversión no se realiza a nivel de PHP sino a nivel de MySQL. MySQL solo convierte \r\n en un salto de línea después de recibir la consulta y la escribe en la base de datos.

Por razones de seguridad y para evitar interferencias con algunos caracteres como comillas en un texto, es necesario escapar un texto antes de publicarlo en nuestra base de datos. Esto generalmente se hace en estilo procedimental con la función mysqli_real_escape_string() respectivamente con mysqli::real_escape_string si usamos el estilo orientado a objetos.

Veamos cómo cambia el resultado de nuestro código cuando escapamos nuestra cadena con mysqli_real_escape_string() antes de insertarla en la base de datos:

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

Este código también da como resultado un salto de línea real tanto en la base de datos como en el código fuente. La razón de esto es que usamos comillas dobles en la primera línea cuando configuramos la variable $txt. Entonces, nuestros caracteres \r y \n ya se han convertido en saltos de línea reales antes de escapar, lo que nuestra función de escape ya no puede cambiarlos.

Sin embargo, las cosas se comportan de manera diferente si llamamos al mismo código con comillas simples para la definición de la variable:

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

Esta vez, PHP no convierte \r y \n en saltos de línea debido a las comillas simples. La salida en el código fuente conserva \r\n como vimos en nuestro segundo ejemplo. Sin embargo, esta vez MySQL tampoco lo convierte, por lo que nuestra cadena original se escribe en la base de datos con \r\n. Esto está garantizado por la función de escape en este código, que ha convertido (escapado) nuestro \r\n a \\r\\n antes del comando UPDATE, lo que significa que MySQL no lo convierte en saltos de línea y escribe el \r\n reescapado en la base de datos.

Hasta ahora, hemos escrito todas nuestras consultas entre comillas dobles, lo que provocó que PHP insertara nuestra variable $txt en la consulta en la forma de su contenido. Para MySQL, dicha consulta se veía así después de insertar $txt (aquí con las líneas a y b):

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

Sin embargo, si escribimos toda la consulta entre comillas simples, tenemos que escapar nosotros mismos de las comillas que encierran nuestro texto para que siga funcionando:

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

Esta consulta también crea un salto de línea en la base de datos, pero no puede verlo en el código fuente (eso sería 'a\r\nb'). Obtenemos el mismo resultado en ambos niveles con un doble escape:

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

Sólo con un triple escape, como vemos en la siguiente consulta, obtenemos el texto 'a\r\nb' en la base de datos sin un salto de línea real, mientras que en el código fuente el texto 'a\\r\\nb' (también sin salto de línea) se genera:

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

Además de estos casos especiales, cabe mencionar finalmente que también podemos utilizar saltos de línea como tales en nuestro código PHP así como en el prompt SQL de phpMyAdmin:

$sql = "UPDATE tab SET col = 'Línea 1
Línea 2' WHERE id = 1";

Esta consulta crea un salto de línea tanto en el código fuente como en la base de datos, independientemente de un escape o la función CHAR().