Guía completa de MySQL Regex, desde lo básico hasta el rendimiento

1. Introducción

Uso de Expresiones Regulares en MySQL

MySQL es un potente sistema de gestión de bases de datos para buscar y manipular datos, y permite el uso de expresiones regulares (REGEXP) para habilitar búsquedas más flexibles. Al aprovechar las expresiones regulares, puedes extraer de manera eficiente datos que coincidan con patrones específicos, así como formatear y validar datos.

Beneficios de usar Expresiones Regulares en MySQL

Usar expresiones regulares en MySQL ofrece los siguientes beneficios:

• Capacidades avanzadas de búsqueda : Extrae fácilmente datos que contengan cadenas o patrones específicos.
• Verificar la integridad de los datos : Útil para validar datos de entrada (por ejemplo, comprobaciones de formato de dirección de correo electrónico).
• Más potente que el operador LIKE : Permite definir de manera flexible condiciones de búsqueda complejas en comparación con LIKE con comodines ( % o _ ).

Propósito y Estructura de Este Artículo

Este artículo cubre todo, desde los conceptos básicos de uso de expresiones regulares en MySQL hasta aplicaciones avanzadas, técnicas de optimización del rendimiento y las nuevas funciones de expresiones regulares introducidas en MySQL 8.0 y posteriores. Se incluyen ejemplos prácticos para que el contenido sea accesible incluso para principiantes.

Estructura de este artículo:

1. Fundamentos y sintaxis de las expresiones regulares en MySQL
2. Funciones de expresiones regulares añadidas en MySQL 8.0 y posteriores
3. Casos de uso prácticos (validación, extracción de datos, conversión de formato)
4. Consideraciones al usar expresiones regulares (rendimiento, seguridad)
5. Preguntas frecuentes (FAQ)
6. Conclusión

2. Fundamentos y Sintaxis de Expresiones Regulares en MySQL

Cómo usar expresiones regulares en MySQL

En MySQL, utilizas el operador REGEXP (o RLIKE) para trabajar con expresiones regulares. Al igual que el operador LIKE, se usa para determinar si el valor de una columna coincide con un patrón de expresión regular dado.

Sintaxis básica

SELECT * FROM table_name WHERE column_name REGEXP 'regex';

o

SELECT * FROM table_name WHERE column_name RLIKE 'regex';

RLIKE es un alias de REGEXP, y ambos se comportan de la misma manera.

Ejemplo

Por ejemplo, para buscar datos donde el nombre contiene el carácter «mountain»:

SELECT * FROM users WHERE name REGEXP 'mountain';

Patrones básicos de expresiones regulares

En las expresiones regulares de MySQL, se pueden usar los siguientes patrones.

Diferencias con el operador LIKE

MySQL también tiene el operador LIKE, que se usa comúnmente para coincidencias de patrones simples, pero REGEXP es más potente que LIKE y permite búsquedas con condiciones más complejas.

Ejemplo 1: Usando el operador LIKE

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

• Con LIKE, puedes buscar datos que contengan «mountain», pero solo se pueden usar comodines simples ( % y _ ).

Ejemplo 2: Usando el operador REGEXP

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

• Usar REGEXP permite extraer solo datos con nombres que comienzan con «mountain» .

Cómo especificar múltiples patrones

Usar | permite especificar múltiples patrones con una condición OR.

Ejemplo: Recuperar datos con el apellido «Sato» o «Tanaka»

SELECT * FROM users WHERE name REGEXP 'Sato|Tanaka';

Búsquedas negativas

Usar ^ dentro de corchetes permite recuperar datos que no contienen los caracteres especificados.

Ejemplo: Datos que contienen caracteres distintos a «mountain»

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

En este caso, busca nombres que no comienzan con «mountain».

Resumen

• En MySQL, usar REGEXP permite búsquedas de patrones más potentes que el regular LIKE.
• Al comprender la sintaxis básica (. , ^ , $ , [] , * , +, etc.), se hacen posibles búsquedas flexibles.
• Comprender las diferencias con LIKE y usarlas adecuadamente es importante.
• Usar | permite buscar múltiples patrones.
• Al aprovechar el patrón negativo [ ], puedes buscar datos que no contengan caracteres específicos.

3. Funciones de Expresiones Regulares Añadidas en MySQL 8.0 y Versiones Posteriores

En MySQL 8.0, además del operador tradicional REGEXP, se añadieron cuatro nuevas funciones que permiten búsquedas de expresiones regulares más flexibles. Esto permite recuperar posiciones de coincidencia, extraer o reemplazar subcadenas, y amplía considerablemente el rango de casos de uso de expresiones regulares.

Esta sección explica cada una de las nuevas funciones de expresiones regulares en detalle y proporciona ejemplos de uso concretos.

3.1 REGEXP_LIKE()

Overview

REGEXP_LIKE() determina si el valor de una columna especificada coincide con un patrón de expresión regular particular, al igual que el operador REGEXP.

Syntax

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

• column_name : la columna a buscar
• 'regex_pattern' : la expresión regular a usar como condición de búsqueda
• flags (optional) : establecer sensibilidad a mayúsculas (especificar i para coincidencia sin distinguir mayúsculas/minúsculas)

Example

“Recuperar usuarios cuyo correo termina con gmail.com o yahoo.co.jp”

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

“Buscar nombres de usuario que contengan ‘admin’ sin importar mayúsculas/minúsculas”

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

✅ REGEXP_LIKE() cumple la misma función que el operador REGEXP pero ofrece una sintaxis que se alinea mejor con el SQL estándar.

3.2 REGEXP_INSTR()

Overview

REGEXP_INSTR() devuelve la posición (índice inicial) de la primera coincidencia de una expresión regular dentro de una cadena dada.

Syntax

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

• start_position (optional) : donde comenzar la búsqueda (el valor por defecto es 1)
• occurrence (optional) : qué coincidencia devolver (el valor por defecto es 1)
• return_option (optional) : 0 (devolver posición inicial) o 1 (devolver posición después de la subcadena coincidente)
• flags (optional) : especificar i para coincidencia sin distinguir mayúsculas/minúsculas

Example

“Obtener la posición inicial de números de teléfono que comienzan con 090 o 080”

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

“Encontrar la posición inicial de la parte del dominio en una dirección de correo electrónico”

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

• El resultado es la posición donde se encuentra @ (por ejemplo, 5 para user@example.com).

✅ Usar REGEXP_INSTR() te permite obtener información posicional para patrones específicos, ampliando tus capacidades de procesamiento de cadenas de texto.

3.3 REGEXP_SUBSTR()

Overview

REGEXP_SUBSTR() extrae la subcadena que coincide con un patrón de expresión regular de una cadena más grande.

Syntax

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

• occurrence (optional) : qué coincidencia devolver (el valor por defecto es 1)
• flags (optional) : sensibilidad a mayúsculas (ignorar con i)

Example

“Extraer solo la parte del dominio de una dirección de correo electrónico”

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

“Obtener la primera secuencia numérica en una cadena de texto”

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

✅ REGEXP_SUBSTR() te permite extraer patrones específicos para limpieza de datos, transformación o análisis.

3.4 REGEXP_REPLACE()

Overview

REGEXP_REPLACE() reemplaza partes de una cadena que coinciden con una expresión regular por otra cadena.

Syntax

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

• replacement : la cadena que sustituye a la porción coincidente
• occurrence (optional) : reemplazar solo la n‑ésima coincidencia (el valor por defecto reemplaza todas las coincidencias)
• flags (optional) : especificar i para coincidencia sin distinguir mayúsculas/minúsculas

Example

“Eliminar guiones de los números de teléfono”

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

“Eliminar etiquetas HTML de un campo de comentarios”

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

✅ Con REGEXP_REPLACE(), la conversión de formatos y la limpieza de datos se vuelven sencillas.

3.5 Resumen

4. Aplicaciones Prácticas de Expresiones Regulares en MySQL

Al aprovechar las expresiones regulares de MySQL, puedes optimizar el procesamiento de datos en operaciones comerciales, como validación de datos, extracción específica de datos y conversión de formatos. Esta sección presenta casos de uso reales, proporcionando el código SQL y las explicaciones para cada ejemplo.

4.1 Validación de Direcciones de Correo Electrónico

Visión General

Utiliza expresiones regulares para verificar si las direcciones de correo electrónico almacenadas en la base de datos están en un formato correcto.

SQL Utilizado

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

Explicación

• ^[a-zA-Z0-9._%+-]+ → Uno o más caracteres alfanuméricos, puntos, guiones bajos o signos más antes del @
• @[a-zA-Z0-9.-]+ → Nombre de dominio después del @
• .[a-zA-Z]{2,}$ → Termina con un dominio de nivel superior (TLD) de al menos dos caracteres (por ejemplo, .com, .jp, .net, etc.)

Con esta consulta, puedes excluir direcciones de correo electrónico inválidas (por ejemplo, user@@example.com, user@.com).

4.2 Verificación del Formato de Números de Teléfono Japoneses

Visión General

Determina si los números de teléfono japoneses típicos (por ejemplo, 090-1234-5678, 03-1234-5678) están en el formato correcto.

SQL Utilizado

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

Explicación

• 0[789]0-[0-9]{4}-[0-9]{4} → Números móviles (por ejemplo, 090-xxxx-xxxx, 080-xxxx-xxxx, etc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → Números fijos (por ejemplo, 03-xxxx-xxxx, 06-xxxx-xxxx, etc.)

Así, las expresiones regulares son útiles en escenarios donde se requiere la estandarización de formatos.

4.3 Verificación del Formato de Números de Tarjeta de Crédito

Visión General

Valida el formato de los números de tarjeta de crédito (Visa, Mastercard, American Express, etc.).

SQL Utilizado

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

Explicación

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

Con esta consulta, puedes eliminar números de tarjeta inválidos (dígitos insuficientes o números iniciales incorrectos) de la base de datos.

4.4 Eliminación de Etiquetas HTML

Visión General

Si los datos enviados por el usuario contienen etiquetas HTML, se eliminan y se convierten a texto plano.

SQL Utilizado

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

Explicación

• '<[^>]+>' → Elimina todas las etiquetas HTML entre < y >

Ejemplo

Efectivo para eliminar etiquetas HTML de comentarios de usuarios y publicaciones de blog.

4.5 Verificación del Formato de Código Postal (Japón)

Visión General

Valida si un código postal japonés (por ejemplo, 123-4567) está en el formato correcto.

SQL Utilizado

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

Explicación

• ^[0-9]{3}-[0-9]{4}$ → Códigos postales con formato “3 dígitos‑4 dígitos”

Al usar esta expresión regular, puedes mantener la consistencia de los datos y prevenir formatos incorrectos.

4.6 Filtrado de Nombres de Usuario (Detección de Palabras Prohibidas)

Visión General

Comprueba durante el registro que el nombre de usuario no contenga palabras prohibidas específicas (por ejemplo, admin, root, system).

SQL Utilizado

Resumen

• La validación de direcciones de correo electrónico, números de teléfono y números de tarjeta de crédito se puede realizar utilizando expresiones regulares.
• La eliminación de etiquetas HTML y la conversión de formatos de datos también se pueden aplicar.
• El filtrado de palabras prohibidas y la verificación de códigos postales, etc., son efectivos para mejorar la seguridad y consistencia de la base de datos.

5. Precauciones al usar expresiones regulares (rendimiento y seguridad)

El uso de expresiones regulares en MySQL permite búsquedas y limpieza de datos potentes. Sin embargo, el uso inadecuado puede provocar degradación del rendimiento y riesgos de seguridad. Esta sección explica las precauciones para usar expresiones regulares de MySQL de forma segura y eficiente.

5.1 Optimización del rendimiento de expresiones regulares

Las búsquedas con expresiones regulares en MySQL son convenientes, pero tienen la desventaja de que no se aplican índices, lo que puede provocar velocidades de procesamiento más lentas.

Medidas para mejorar el rendimiento

1. Combinar índices LIKE y FULLTEXT

«` SELECT * FROM users WHERE email LIKE ‘%gmail.com’;

2. **Usar expresiones regulares simples**

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

3. Filtrar los datos objetivo de antemano

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

### 5.2 Riesgos de seguridad y contramedidas

#### 1. Ataques DoS con expresiones regulares (ReDoS)

* Evitar coincidencias ilimitadas como `.*`.
* Usar `^` y `$` para limitar el rango de búsqueda.
* No pasar la entrada del usuario directamente a `REGEXP`.

#### 2. Inyección SQL y expresiones regulares

❌ **Consulta peligrosa**

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

✅ Consulta segura (usando marcadores de posición)

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

### 5.3 Compatibilidad entre versiones




Version
Características principales




MySQL 5.x
REGEXP


MySQL 8.x
Añadido REGEXP_LIKE(), REGEXP_INSTR(), REGEXP_SUBSTR(), REGEXP_REPLACE()




## Resumen

* **`REGEXP` no utiliza índices, por lo que la optimización del rendimiento es importante.**
* **Para prevenir ReDoS (ataques DoS con expresiones regulares), evite el uso excesivo de comodines.**
* **Para prevenir inyección SQL, no aplique la entrada del usuario directamente a `REGEXP`.**

## 6. Preguntas frecuentes (FAQ)

Hemos compilado preguntas comunes al usar expresiones regulares de MySQL (REGEXP). Resolver estas dudas le ayudará a usar expresiones regulares de manera más efectiva.

---

### 6.1 ¿Qué patrones de expresiones regulares se pueden usar en MySQL?




Symbol
Descripción
Ejemplo
Result




.
Cualquier carácter único
a.c
Coincide con "abc", "aac", "adc"


^
Inicio de la cadena
^abc
Coincide con "abcde" (no coincide con "dabc")


$
Fin de la cadena
xyz$
Coincide con "axyz" (no coincide con "xyzb")


[]
Cualquiera de los caracteres especificados
[abc]
Coincide con "a", "b", "c"


[^]
Cualquier carácter excepto los especificados
[^abc]
Coincide con cualquier carácter excepto "a", "b", "c"


*
Cero o más repeticiones del carácter precedente
a*
Coinciden con "a", "aa", "aaa"


+
Una o más repeticiones del carácter anterior
a+
Coincide con "a", "aa", "aaa" (no coincide con "")




### 6.2 ¿Cuál es la diferencia entre LIKE y REGEXP?




Comparison Item
LIKE
REGEXP




Funcionalidad
Coincidencia de patrones simple
Buscar usando expresiones regulares complejas


Comodines
%_
.*^$[a-z]


Velocidad de búsqueda
Rápido porque se pueden aplicar índices
A menudo resulta en escaneos completos de tabla, lo que puede ser más lento.


Casos de uso
Búsquedas simples (coincidencia parcial, coincidencia de prefijo, etc.)
Búsquedas complejas basadas en patrones específicos




✅ **Porque `LIKE` es más rápido, priorice `LIKE` para búsquedas simples y use `REGEXP` cuando se necesiten búsquedas complejas.**

### 6.3 ¿Cuáles son las diferencias en expresiones regulares entre MySQL 5.x y MySQL 8.x?




Version
Características clave




MySQL 5.x
REGEXP


MySQL 8.x
Añadido REGEXP_LIKE(), REGEXP_INSTR(), REGEXP_SUBSTR(), REGEXP_REPLACE()




### 6.4 ¿Cómo solucionar cuando las expresiones regulares no funcionan como se espera?

✅ **Lista de verificación**

1. **¿Se ha escapado correctamente?**

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

2. Intente la bandera i con REGEXP_LIKE()

«` SELECT * FROM users WHERE REGEXP_LIKE(username, ‘admin’, ‘i’);

3. **Verifique la codificación de los datos**

   ```
   SHOW VARIABLES LIKE 'character_set_database';

6.5 ¿Cómo mejorar el rendimiento de las funciones REGEXP?

1. Pre-filtrar usando LIKE

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

2. **Aprovechar índices**

   ```
   ALTER TABLE users ADD FULLTEXT(email);

Resumen

• REGEXP es más potente que LIKE, pero debe tener cuidado con el rendimiento.
• En MySQL 8.0, se han añadido nuevas funciones de expresiones regulares (como REGEXP_LIKE()) que permiten un procesamiento más flexible.
• Mejorar el rendimiento depende de combinar con LIKE, aprovechar índices y diseñar expresiones regulares simples.

7. Resumen

Las expresiones regulares de MySQL (REGEXP) son una herramienta muy útil para la búsqueda de datos, validación, conversión de formatos y más. Este artículo ofrece una explicación detallada de todo, desde los conceptos básicos de uso de expresiones regulares en MySQL hasta las nuevas funciones añadidas en MySQL 8.0 y posteriores, casos de uso prácticos, precauciones y preguntas frecuentes.

7.1 Revisión de los puntos clave

Un breve repaso de los puntos importantes del artículo.

1. Conceptos básicos de expresiones regulares en MySQL

• El operador REGEXP permite una coincidencia de patrones más flexible que el estándar LIKE.
• Patrones comunes de expresiones regulares
• ^ (inicio), $ (final), . (cualquier carácter único), [] (clase de caracteres), + (una o más repeticiones), etc.

2. Nuevas funciones de expresiones regulares en MySQL 8.0 y posteriores

MySQL 8.0 añade las siguientes cuatro funciones, haciendo el procesamiento de datos más flexible:

• REGEXP_LIKE() : alternativa al operador REGEXP
• REGEXP_INSTR() : devuelve la posición inicial de una cadena coincidente
• REGEXP_SUBSTR() : extrae la cadena coincidente
• REGEXP_REPLACE() : reemplaza cadenas usando expresiones regulares

3. Casos de uso práctico

• Validación de direcciones de correo electrónico
• Verificación de números de teléfono y tarjetas de crédito
• Eliminación de etiquetas HTML
• Verificación del formato de códigos postales
• Detección de palabras prohibidas
• Limpieza de datos (eliminación de espacios en blanco, comas, etc.)

4. Precauciones al usar expresiones regulares

• Optimización del rendimiento
• REGEXP no utiliza índices y provoca un escaneo completo de la tabla, así que tenga cuidado.
• Puede mejorar esto combinando índices LIKE o FULLTEXT.
• Medidas de seguridad
• Para prevenir ReDoS (ataques de denegación de servicio con expresiones regulares), evite el uso excesivo de .*.
• Use placeholder para prevenir inyección SQL.

5. Preguntas frecuentes (FAQ)

• Patrones utilizables con expresiones regulares de MySQL
• Diferencias entre LIKE y REGEXP
• Diferencias de características entre MySQL 5.x y 8.x
• Solución de problemas cuando las expresiones regulares no funcionan como se espera
• Formas de mejorar el rendimiento

7.2 Buenas prácticas para usar expresiones regulares

1. Mantenga las expresiones regulares simples

• Ejemplo: evite sobreusar .* y aproveche ^ (inicio) y $ (final).

2. Combine con LIKE o FULLTEXT

• Pre-filtre con LIKE para reducir el objetivo antes de aplicar REGEXP, disminuyendo la carga de procesamiento.

3. Use REGEXP_REPLACE() para la limpieza de datos

• Ejemplo: elimine etiquetas HTML, limpie espacios innecesarios.

4. Aproveche MySQL 8.0 y posteriores

• Usar nuevas funciones como REGEXP_LIKE() permite escribir SQL más legible.

5. Implemente medidas de seguridad

• No aplique directamente la entrada del usuario a REGEXP (protección contra inyección SQL).
• Use placeholder para consultas dinámicas.

7.3 Recursos para profundizar

Aquí hay algunos materiales de referencia para un aprendizaje más profundo de las expresiones regulares de MySQL.

Documentación oficial

• Referencia de expresiones regulares de MySQL 8.0
• Fundamentos de expresiones regulares de MySQL 5.x

7.4 Conclusión

Las expresiones regulares de MySQL pueden usarse en muchos escenarios, como búsqueda de datos, limpieza y validación. Sin embargo, es importante usarlas correctamente con una comprensión de las consideraciones de rendimiento y seguridad.
Esperamos que este artículo sirva como una referencia útil para aprovechar las expresiones regulares de MySQL. ¡Siéntase libre de aplicarlas en sus proyectos reales!