Guía de expresiones regulares MySQL (REGEXP): sintaxis, funciones de MySQL 8, ejemplos y buenas prácticas

1. Introducción

Uso de expresiones regulares en MySQL

MySQL es un sistema de gestión de bases de datos potente para buscar y manipular datos, y también admite expresiones regulares (REGEXP) para permitir búsquedas más flexibles. Con las expresiones regulares, puedes extraer de forma eficiente los datos que coinciden con patrones específicos, así como formatear o validar datos.

Beneficios de usar expresiones regulares en MySQL

El uso de expresiones regulares en MySQL ofrece ventajas como:

• Búsqueda avanzada : Extrae fácilmente datos que contienen cadenas o patrones específicos.
• Comprobación de integridad de datos : Útil para la validación de entradas (p. ej., validar formatos de direcciones de correo electrónico).
• Más potente que el operador LIKE : En comparación con LIKE, que usa comodines (% y _), REGEXP permite definir condiciones de búsqueda complejas de forma más flexible.

Propósito y estructura de este artículo

Este artículo explica todo, desde los conceptos básicos de expresiones regulares en MySQL hasta su uso avanzado, optimización de rendimiento y las nuevas funciones de expresiones regulares introducidas en MySQL 8.0 y versiones posteriores. Con ejemplos prácticos, el contenido está diseñado para ser fácil de entender incluso para principiantes.

Estructura del artículo:

1. Conceptos básicos y sintaxis de expresiones regulares en MySQL
2. Funciones de expresiones regulares añadidas en MySQL 8.0 y posteriores
3. Ejemplos prácticos (validación, extracción de datos, conversión de formatos)
4. Consideraciones importantes al usar expresiones regulares (rendimiento, seguridad)
5. Preguntas frecuentes (FAQ)
6. Resumen

2. Conceptos básicos 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. De forma similar al operador LIKE, se emplea para determinar si el valor de una columna coincide con un patrón regex especificado.

Sintaxis básica

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

O

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

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

Ejemplo
Por ejemplo, para buscar registros donde el nombre contiene el carácter “山”:

SELECT * FROM users WHERE name REGEXP '山';

Patrones básicos de expresiones regulares

En las expresiones regulares de MySQL, puedes usar patrones como los siguientes.

Diferencias con el operador LIKE

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

Ejemplo 1: Uso del operador LIKE

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

• Con LIKE, puedes buscar datos que contengan 山, pero solo puedes usar comodines simples (% y _).

Ejemplo 2: Uso del operador REGEXP

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

• Con REGEXP, puedes extraer únicamente los datos donde el nombre comienza con 山.

Cómo especificar múltiples patrones

Puedes usar | para especificar varios patrones con una condición OR.

Ejemplo: Obtener usuarios cuyo apellido sea “佐藤” o “田中”

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

Coincidencia negativa

Si utilizas ^ dentro de corchetes, puedes obtener datos que “no contengan los caracteres especificados”.

Ejemplo: Datos que comienzan con un carácter distinto de “山”

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

En este caso, devuelve los registros donde el nombre no comienza con 山.

Resumen

• En MySQL, REGEXP permite búsquedas de patrones más potentes que LIKE.
• Comprender la sintaxis básica y los patrones ( . , ^ , $ , [] , * , + , etc.) permite búsquedas flexibles.
• Es importante entender las diferencias con LIKE y usar cada uno de manera adecuada.
• Usar | permite buscar con múltiples patrones.
• Usar clases de caracteres negativas [^ ] ayuda a buscar datos que no comienzan con caracteres específicos.

3. Funciones de Expresiones Regulares añadidas en MySQL 8.0 y posteriores

En MySQL 8.0, además del operador tradicional REGEXP, se añadieron cuatro nuevas funciones para permitir búsquedas de expresiones regulares más flexibles. Estas permiten obtener posiciones de coincidencia, extraer subcadenas y realizar reemplazos, ampliando enormemente lo que puedes hacer con regex en MySQL.

En esta sección, explicamos cada nueva función de expresiones regulares en detalle y mostramos ejemplos concretos.

3.1 REGEXP_LIKE()

Visión general

REGEXP_LIKE(), al igual que el operador REGEXP, verifica si el valor de una columna coincide con un patrón regex especificado.

Sintaxis

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

• column_name : la columna a buscar
• 'regex_pattern' : el patrón regex usado como condición
• flags (optional) : sensibilidad a mayúsculas/minúsculas (use i para coincidencia sin distinción de mayúsculas)

Ejemplos

“Obtener usuarios cuyo correo electrónico termina en 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 distinción de mayúsculas)”

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

✅ REGEXP_LIKE() cumple la misma función que el operador REGEXP, pero permite un estilo más estándar y amigable con SQL.

3.2 REGEXP_INSTR()

Visión general

REGEXP_INSTR() devuelve la posición (índice de inicio) donde ocurre una coincidencia regex dentro de una cadena.

Sintaxis

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

• start_position (opcional): dónde iniciar la búsqueda (por defecto: 1)
• occurrence (opcional): cuál ocurrencia de coincidencia devolver (por defecto: 1)
• return_option (opcional): 0 (devolver posición de inicio) o 1 (devolver posición final de la coincidencia)
• flags (opcional): use i para coincidencia sin distinción de mayúsculas

Ejemplos

“Si el número de teléfono comienza con 090 o 080, obtener la posición de inicio”

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

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

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

• Devuelve la posición donde se encuentra @ (por ejemplo, user@example.com devuelve 5 ).

✅ Con REGEXP_INSTR(), puedes obtener información posicional, ampliando tus opciones para el procesamiento de cadenas.

3.3 REGEXP_SUBSTR()

Visión general

REGEXP_SUBSTR() extrae la subcadena que coincide con un patrón regex dentro de una cadena.

Sintaxis

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

• occurrence (opcional): obtener la n‑ésima coincidencia (por defecto: 1)
• flags (opcional): sensibilidad a mayúsculas/minúsculas ( i para ignorar mayúsculas)

Ejemplos

“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 un mensaje”

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

✅ Con REGEXP_SUBSTR(), puedes extraer patrones específicos y utilizarlos para organizar y transformar datos.

3.4 REGEXP_REPLACE()

Visión general

REGEXP_REPLACE() reemplaza subcadenas que coinciden con un patrón regex por otra cadena.

Sintaxis

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

• replacement : la cadena con la que reemplazar la parte coincidente
• occurrence (opcional): reemplaza solo la n‑ésima coincidencia (si se omite, reemplaza todas las coincidencias)
• flags (opcional): use i para coincidencia sin distinción de mayúsculas/minúsculas

Ejemplos

“Eliminar guiones (-) de números de teléfono”

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

“Eliminar etiquetas HTML”

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 fáciles.

3.5 Resumen

4. Ejemplos prácticos de expresiones regulares en MySQL

Al aprovechar las expresiones regulares de MySQL, puedes optimizar tareas reales de procesamiento de datos como validación de datos, extracción de datos específicos y conversión de formatos. En esta sección, presentamos casos de uso prácticos con código SQL y explicaciones.

4.1 Validación de direcciones de correo electrónico

Visión general

Utiliza expresiones regulares para comprobar si las direcciones de correo electrónico almacenadas en la base de datos siguen un formato válido.

SQL a usar

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 de @
• @[a-zA-Z0-9.-]+ → un nombre de dominio después de @
• \.[a-zA-Z]{2,}$ → termina con un TLD (Dominio de nivel superior) de al menos 2 letras (p. ej., .com, .jp, .net)

Con esta consulta, puedes excluir direcciones de correo electrónico no válidas (p. ej., user@@example.com, user@.com).

4.2 Verificación de formatos de números de teléfono japoneses

Visión general

Determina si los números de teléfono japoneses comunes (p. ej., 090-1234-5678, 03-1234-5678) siguen el formato correcto.

SQL a usar

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 ( 090-xxxx-xxxx, 080-xxxx-xxxx, etc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → líneas fijas ( 03-xxxx-xxxx, 06-xxxx-xxxx, etc.)

De esta manera, las expresiones regulares son útiles cuando se requiere consistencia de formato.

4.3 Validación del formato de números de tarjetas de crédito

Visión general

Valida los formatos de los números de tarjetas de crédito (Visa, Mastercard, American Express, etc.).

SQL a usar

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 dígitos 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 filtrar números de tarjeta no válidos en la base de datos (p. ej., longitud incorrecta o dígitos iniciales no válidos).

4.4 Eliminación de etiquetas HTML

Visión general

Si los datos enviados por el usuario contienen etiquetas HTML, puedes eliminarlas y convertir el contenido a texto plano.

SQL a usar

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

Explicación

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

Ejemplo

Esto es útil para eliminar etiquetas HTML de comentarios de usuarios y publicaciones de blog.

4.5 Validación del formato de códigos postales (Japón)

Visión general

Valida si los códigos postales japoneses (p. ej., 123-4567) siguen el formato correcto.

SQL a usar

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

Explicación

• ^[0-9]{3}-[0-9]{4}$ → un código postal en el formato «3 dígitos-4 dígitos»

Usar esta expresión regular ayuda a mantener la consistencia de los datos y prevenir formatos incorrectos.

4.6 Filtrado de nombres de usuario (detección de palabras no permitidas)

Visión general

Check whether usernames contain disallowed words reserved for system use (e.g., admin, root, system) during registration.

SQL a usar

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

Explicación

• admin|root|system → detecta nombres de usuario que contienen estos términos

Esta consulta ayuda a evitar que los usuarios normales usen nombres destinados a administradores del sistema.

Resumen

• Las expresiones regulares pueden usarse para validar direcciones de correo electrónico, números de teléfono y números de tarjetas de crédito.
• También pueden aplicarse para eliminar etiquetas HTML y convertir formatos de datos.
• Son efectivas para mejorar la seguridad y consistencia de la base de datos mediante tareas como filtrar palabras no permitidas y validar códigos postales.

5. Consideraciones importantes al usar expresiones regulares (rendimiento y seguridad)

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

5.1 Optimización del rendimiento para expresiones regulares

La búsqueda con regex en MySQL es conveniente, pero tiene una desventaja: los índices generalmente no se aplican, lo que puede ralentizar las consultas.

Medidas para mejorar el rendimiento

1. Combinar índices LIKE o FULLTEXT

   SELECT * FROM users WHERE email LIKE '%gmail.com';
   

2. Usar expresiones regulares más simples

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

3. Filtrar primero los datos objetivo

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

5.2 Riesgos de seguridad y contramedidas

1. Denegación de servicio por expresiones regulares (ReDoS)

• Evite coincidencias sin límite como .*.
• Use ^ y $ para limitar el alcance de la búsqueda.
• No pase la entrada del usuario directamente a REGEXP.

2. Inyección SQL y expresiones regulares

❌ Consulta insegura

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

✅ Consulta segura (use marcadores de posición)

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

5.3 Compatibilidad de versiones

Resumen

• Debido a que REGEXP no utiliza índices, las medidas de rendimiento son importantes.
• Para prevenir ReDoS (Denegación de servicio por 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)

A continuación se presentan preguntas comunes sobre el uso de expresiones regulares de MySQL (REGEXP). Resolver estos problemas le ayudará a usar regex de manera más eficaz.

6.1 ¿Qué patrones regex puedo usar en MySQL?

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

✅ Como LIKE es más rápido, prefiera LIKE para búsquedas simples y use REGEXP cuando se requiera una coincidencia compleja.

6.3 ¿Cuáles son las diferencias en las características de regex entre MySQL 5.x y MySQL 8.x?

6.4 ¿Qué debo hacer si una expresión regular no funciona como se espera?

✅ Lista de verificación

1. Verifique que el escape sea correcto

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

2. Pruebe 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 puedo mejorar el rendimiento de las consultas REGEXP?

1. Pre-filtrar con LIKE

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

2. Utilizar índices cuando sea apropiado

   ALTER TABLE users ADD FULLTEXT(email);
   

Resumen

• REGEXP es más potente que LIKE, pero debes tener en cuenta el rendimiento.
• En MySQL 8.0, se agregaron nuevas funciones de regex (como REGEXP_LIKE()), lo que permite un procesamiento más flexible.
• Para mejorar el rendimiento: combina con LIKE, usa índices y diseña patrones de regex simples.

7. Resumen

Las expresiones regulares de MySQL (REGEXP) son muy útiles para buscar datos, validación y conversión de formatos. Este artículo cubrió los conceptos básicos de regex en MySQL, las nuevas funciones agregadas en MySQL 8.0 y posteriores, ejemplos prácticos, consideraciones importantes y preguntas frecuentes.

7.1 Puntos clave

Aquí hay un resumen rápido de los puntos más importantes.

1. Conceptos básicos de expresiones regulares en MySQL

• El uso del operador REGEXP permite una coincidencia de patrones más flexible que LIKE.
• Patrones de regex comunes
• ^ (inicio), $ (fin), . (cualquier carácter individual), [] (clase de caracteres), + (una o más repeticiones), etc.

2. Nuevas funciones de expresiones regulares en MySQL 8.0+

En MySQL 8.0, se agregaron estas cuatro funciones, lo que permite un procesamiento más flexible:

• REGEXP_LIKE(): una alternativa al operador REGEXP
• REGEXP_INSTR(): obtener la posición inicial de la subcadena coincidente
• REGEXP_SUBSTR(): extraer la subcadena coincidente
• REGEXP_REPLACE(): reemplazar subcadenas usando regex

3. Ejemplos prácticos

• Validación de direcciones de correo electrónico
• Comprobaciones de números de teléfono y tarjetas de crédito
• Eliminación de etiquetas HTML
• Validación de formatos de códigos postales
• Detección de palabras no permitidas
• Limpieza de datos (recortar espacios en blanco, eliminar comas, etc.)

4. Consideraciones importantes al usar expresiones regulares

• Optimización de rendimiento
• Debido a que REGEXP no usa índices, puede resultar en escaneos completos de tabla.
• Puedes mejorar el rendimiento combinando LIKE o índices FULLTEXT.
• Medidas de seguridad
• Para prevenir ReDoS (Regular Expression DoS), evita el uso excesivo de .*.
• Para prevenir inyección SQL, usa placeholders.

5. Preguntas frecuentes (FAQ)

• Patrones de regex disponibles en MySQL
• Diferencias entre LIKE y REGEXP
• Diferencias de funciones entre MySQL 5.x y 8.x
• Resolución de problemas cuando regex no se comporta como se espera
• Formas de mejorar el rendimiento

7.2 Mejores prácticas para usar expresiones regulares

1. Mantén los patrones de regex simples

• Ejemplo: Evita el uso excesivo de .* y haz uso de ^ (inicio) y $ (fin).

1. Combina con LIKE o FULLTEXT donde sea apropiado

• Filtra previamente con LIKE, luego aplica REGEXP para reducir el costo de la consulta.

1. Usa REGEXP_REPLACE() para la limpieza de datos

• Ejemplo: Elimina etiquetas HTML y normaliza espacios innecesarios.

1. Aprovecha MySQL 8.0 y posteriores

• Usar nuevas funciones como REGEXP_LIKE() puede hacer que SQL sea más legible.

1. Aplica medidas de seguridad exhaustivamente

• No apliques entrada de usuario directamente a REGEXP (prevención de inyección SQL).
• Usa placeholders para consultas dinámicas.

7.3 Recursos para aprendizaje adicional

Aquí hay referencias para ayudarte a aprender más profundamente sobre expresiones regulares en MySQL.

Documentación oficial

• Referencia de expresiones regulares de MySQL 8.0
• Conceptos básicos de expresiones regulares en MySQL 5.x

7.4 Notas finales

Las expresiones regulares de MySQL se pueden usar en muchas situaciones, incluyendo búsqueda de datos, limpieza y validación. Sin embargo, es importante usarlas de manera apropiada mientras se entienden las consideraciones de rendimiento y seguridad.

Esperamos que este artículo te ayude cuando trabajes con expresiones regulares en MySQL. ¡Intenta aplicar estas técnicas en tus proyectos!