Guia de Expressões Regulares MySQL (REGEXP): Sintaxe, Funções do MySQL 8, Exemplos e Melhores Práticas

1. Introdução

Usando Expressões Regulares no MySQL

MySQL é um poderoso sistema de gerenciamento de banco de dados para busca e manipulação de dados, e também oferece suporte a expressões regulares (REGEXP) para permitir buscas mais flexíveis. Com expressões regulares, você pode extrair eficientemente dados que correspondam a padrões específicos, bem como formatar ou validar dados.

Benefícios de Usar Expressões Regulares no MySQL

Usar expressões regulares no MySQL traz benefícios como:

• Busca avançada : Extraia facilmente dados que contenham strings ou padrões específicos.
• Verificação de integridade dos dados : Útil para validação de entrada (por exemplo, validar formatos de endereços de e‑mail).
• Mais poderoso que o operador LIKE : Ao contrário do LIKE, que usa curingas (% e _), o REGEXP permite definir condições de busca complexas de forma mais flexível.

Propósito e Estrutura deste Artigo

Este artigo explica tudo, desde os conceitos básicos de expressões regulares no MySQL até o uso avançado, otimização de desempenho e as novas funções de expressão regular introduzidas no MySQL 8.0 e versões posteriores. Com exemplos práticos, o conteúdo foi projetado para ser fácil de entender, mesmo para iniciantes.

Estrutura do artigo:

1. Conceitos básicos e sintaxe de expressões regulares no MySQL
2. Funções de expressão regular adicionadas no MySQL 8.0 e posteriores
3. Exemplos práticos (validação, extração de dados, conversão de formato)
4. Considerações importantes ao usar expressões regulares (desempenho, segurança)
5. FAQ (Perguntas Frequentes)
6. Resumo

2. Conceitos Básicos e Sintaxe de Expressões Regulares no MySQL

Como Usar Expressões Regulares no MySQL

No MySQL, você usa o operador REGEXP (ou RLIKE) para trabalhar com expressões regulares. Semelhante ao operador LIKE, ele serve para determinar se o valor de uma coluna corresponde a um padrão regex especificado.

Sintaxe básica

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Ou

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE é um alias para REGEXP, e ambos se comportam da mesma forma.

Exemplo
Por exemplo, para buscar registros onde o nome contém o caractere “山”:

SELECT * FROM users WHERE name REGEXP '山';

Padrões Básicos de Expressões Regulares

Nas expressões regulares do MySQL, você pode usar padrões como os seguintes.

Diferenças em relação ao Operador LIKE

O MySQL também fornece o operador LIKE, que é comumente usado para correspondência de padrões simples. Contudo, o REGEXP é mais poderoso que o LIKE e suporta buscas com condições mais complexas.

Exemplo 1: Usando o operador LIKE

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

• Com LIKE, você pode buscar dados que contenham 山, mas só pode usar curingas simples (% e _).

Exemplo 2: Usando o operador REGEXP

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

• Com REGEXP, você pode extrair apenas os dados onde o nome começa com 山.

Como Especificar Múltiplos Padrões

Você pode usar | para especificar vários padrões com uma condição OR.

Exemplo: Obter usuários cujo sobrenome seja “佐藤” ou “田中”

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

Correspondência Negativa

Se você usar ^ dentro de colchetes, pode recuperar dados que “não contêm os caracteres especificados”.

Exemplo: Dados que começam com um caractere diferente de “山”

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

Neste caso, ele retorna registros onde o nome não começa com 山.

Resumo

• No MySQL, REGEXP permite buscas de padrões mais poderosas do que LIKE.
• Compreender a sintaxe básica e padrões ( . , ^ , $ , [] , * , + , etc.) permite buscas flexíveis.
• É importante entender as diferenças em relação ao LIKE e usar cada um de forma apropriada.
• Usar | permite buscar com múltiplos padrões.
• Usar classes de caracteres negativos [^ ] ajuda a buscar dados que não começam com caracteres específicos.

3. Funções de Expressão Regular Adicionadas no MySQL 8.0 e Posteriores

No MySQL 8.0, além do operador tradicional REGEXP, quatro novas funções foram adicionadas para permitir buscas de expressões regulares mais flexíveis. Elas tornam possível recuperar posições de correspondência, extrair subcadeias e realizar substituições, expandindo grandemente o que você pode fazer com regex no MySQL.

Nesta seção, explicamos cada nova função de expressão regular em detalhes e mostramos exemplos concretos.

3.1 REGEXP_LIKE()

Visão Geral

REGEXP_LIKE(), assim como o operador REGEXP, verifica se um valor de coluna corresponde a um padrão de regex especificado.

Sintaxe

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

• column_name : a coluna a ser pesquisada
• 'regex_pattern' : o padrão de regex usado como a condição
• flags (optional) : sensibilidade a maiúsculas/minúsculas (use i para correspondência sem distinção de maiúsculas/minúsculas)

Exemplos

“Obter usuários cujo email termina com gmail.com ou yahoo.co.jp”

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

“Buscar nomes de usuário contendo ‘admin’ (sem distinção de maiúsculas/minúsculas)”

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

✅ REGEXP_LIKE() serve o mesmo propósito que o operador REGEXP, mas permite um estilo mais amigável ao SQL padrão.

3.2 REGEXP_INSTR()

Visão Geral

REGEXP_INSTR() retorna a posição (índice inicial) onde ocorre uma correspondência de regex dentro de uma string.

Sintaxe

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

• start_position (optional): onde começar a busca (padrão: 1)
• occurrence (optional): qual ocorrência de correspondência retornar (padrão: 1)
• return_option (optional): 0 (retornar posição inicial) ou 1 (retornar posição final da correspondência)
• flags (optional): use i para correspondência sem distinção de maiúsculas/minúsculas

Exemplos

“Se o número de telefone começa com 090 ou 080, obter a posição inicial”

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

“Obter a posição inicial da parte do domínio em um endereço de email”

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

• Ele retorna a posição onde @ é encontrado (ex.: user@example.com retorna 5 ).

✅ Com REGEXP_INSTR(), você pode recuperar informações posicionais, expandindo suas opções para processamento de strings.

3.3 REGEXP_SUBSTR()

Visão Geral

REGEXP_SUBSTR() extrai a subcadeia que corresponde a um padrão de regex de dentro de uma string.

Sintaxe

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

• occurrence (optional): obter a enésima correspondência (padrão: 1)
• flags (optional): sensibilidade a maiúsculas/minúsculas ( i para ignorar maiúsculas/minúsculas)

Exemplos

“Extrair apenas a parte do domínio de um endereço de email”

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

“Obter a primeira sequência de números em uma mensagem”

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

✅ Com REGEXP_SUBSTR(), você pode extrair padrões específicos e usá-los para organizar e transformar dados.

3.4 REGEXP_REPLACE()

Visão Geral

REGEXP_REPLACE() substitui subcadeias que correspondem a um padrão de regex por outra string.

Sintaxe

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

• replacement : a string para substituir a parte correspondida
• occurrence (opcional): substituir apenas a n‑ésima correspondência (se omitido, substitui todas as correspondências)
• flags (opcional): use i para correspondência sem distinção entre maiúsculas e minúsculas

Exemplos

“Remover hífens (-) de números de telefone”

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

“Remover tags HTML”

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

✅ Com REGEXP_REPLACE(), a conversão de formato e a limpeza de dados se tornam fáceis.

3.5 Resumo

4. Exemplos Práticos de Expressões Regulares MySQL

Ao aproveitar as expressões regulares do MySQL, você pode simplificar tarefas reais de processamento de dados, como validação de dados, extração de dados específicos e conversão de formato. Nesta seção, apresentamos casos de uso práticos com código SQL e explicações.

4.1 Validação de Endereço de Email

Visão geral

Use expressões regulares para verificar se os endereços de email armazenados no banco de dados seguem um 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,}$';

Explicação

• ^[a-zA-Z0-9._%+-]+ → um ou mais caracteres alfanuméricos, pontos, sublinhados ou sinais de mais antes de @
• @[a-zA-Z0-9.-]+ → um nome de domínio após @
• \.[a-zA-Z]{2,}$ → termina com um TLD (Domínio de Nível Superior) de pelo menos 2 letras (ex.: .com, .jp, .net)

Com esta consulta, você pode excluir endereços de email inválidos (ex.: user@@example.com, user@.com).

4.2 Verificando Formatos de Números de Telefone Japoneses

Visão geral

Determine se números de telefone japoneses comuns (ex.: 090-1234-5678, 03-1234-5678) seguem o formato correto.

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

Explicação

• 0[789]0-[0-9]{4}-[0-9]{4} → números móveis ( 090-xxxx-xxxx, 080-xxxx-xxxx, etc.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → linhas fixas ( 03-xxxx-xxxx, 06-xxxx-xxxx, etc.)

Dessa forma, expressões regulares são úteis quando a consistência de formato é necessária.

4.3 Validação de Formato de Número de Cartão de Crédito

Visão geral

Validar os formatos de números de cartões 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})$';

Explicação

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

Com esta consulta, você pode filtrar números de cartão inválidos no banco de dados (ex.: comprimento incorreto ou dígitos iniciais inválidos).

4.4 Removendo Tags HTML

Visão geral

Se os dados enviados pelo usuário contêm tags HTML, você pode removê-las e converter o conteúdo para texto simples.

SQL a Usar

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

Explicação

• '<[^>]+>' → remove todas as tags HTML entre < e >

Exemplo

Isso é útil para remover tags HTML de comentários de usuários e postagens de blog.

4.5 Validação de Formato de CEP (Japão)

Visão geral

Validar se os códigos postais japoneses (ex.: 123-4567) seguem o formato correto.

SQL a Usar

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

Explicação

• ^[0-9]{3}-[0-9]{4}$ → um código postal no formato “3 dígitos-4 dígitos”

Usar esta expressão regular ajuda a manter a consistência dos dados e evitar formatos incorretos.

4.6 Filtragem de Nome de Usuário (Detectando Palavras Não Permitidas)

Visão geral

Verifique se os nomes de usuário contêm palavras não permitidas reservadas para uso do sistema (por exemplo, admin, root, system) durante o registro.

SQL para Usar

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

Explicação

• admin|root|system → detecta nomes de usuário contendo esses termos

Esta consulta ajuda a impedir que usuários regulares usem nomes destinados a administradores do sistema.

Resumo

• Expressões regulares podem ser usadas para validar endereços de email, números de telefone e números de cartão de crédito .
• Elas também podem ser aplicadas para remover tags HTML e converter formatos de dados .
• Elas são eficazes para melhorar a segurança e a consistência do banco de dados por meio de tarefas como filtrar palavras não permitidas e validar códigos postais .

5. Considerações Importantes ao Usar Expressões Regulares (Desempenho e Segurança)

Usar expressões regulares no MySQL permite buscas poderosas e limpeza de dados. No entanto, o uso inadequado pode causar degradação de desempenho e introduzir riscos de segurança. Esta seção explica considerações chave para usar expressões regulares do MySQL de forma segura e eficiente.

5.1 Otimização de Desempenho para Expressões Regulares

A busca por regex no MySQL é conveniente, mas tem uma desvantagem: os índices geralmente não se aplicam, o que pode tornar as consultas mais lentas.

Medidas para Melhorar o Desempenho

1. Combine LIKE ou índices FULLTEXT

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

2. Use expressões regulares mais simples

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

3. Filtre os dados de destino primeiro

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

5.2 Riscos de Segurança e Contramedidas

1. DoS por Expressão Regular (ReDoS)

• Evite correspondências ilimitadas como .* .
• Use ^ e $ para limitar o escopo da busca.
• Não passe entrada do usuário diretamente para REGEXP .

2. Injeção de SQL e Expressões Regulares

❌ Consulta insegura

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

✅ Consulta segura (use placeholders)

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

5.3 Compatibilidade de Versão

Resumo

• Como REGEXP não usa índices, medidas de desempenho são importantes.
• Para prevenir ReDoS (DoS por Expressão Regular), evite o uso excessivo de curingas.
• Para prevenir injeção de SQL, não aplique entrada do usuário diretamente a REGEXP.

6. FAQ (Perguntas Frequentes)

Aqui estão perguntas comuns sobre o uso de expressões regulares (REGEXP) no MySQL. Resolver essas questões ajudará você a usar regex de forma mais eficaz.

6.1 Quais padrões de regex posso usar no MySQL?

6.2 Qual é a diferença entre LIKE e REGEXP?

✅ Como LIKE é mais rápido, prefira LIKE para buscas simples e use REGEXP quando a correspondência complexa for necessária.

6.3 Quais são as diferenças nas funcionalidades de regex entre MySQL 5.x e MySQL 8.x?

6.4 O que devo fazer se uma regex não funcionar como esperado?

✅ Lista de Verificação

1. Verifique se o escaping está correto

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

2. Tente a flag i com REGEXP_LIKE()

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

3. Verifique a codificação dos dados

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Como posso melhorar o desempenho de consultas REGEXP?

1. Pré-filtre com LIKE

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

2. Use índices onde apropriado

   ALTER TABLE users ADD FULLTEXT(email);
   

Resumo

• REGEXP é mais poderoso que LIKE, mas você deve estar atento ao desempenho.
• No MySQL 8.0, novas funções regex (como REGEXP_LIKE()) foram adicionadas, permitindo um processamento mais flexível.
• Para melhorar o desempenho: combine com LIKE, use índices e crie padrões regex simples.

7. Resumo

Expressões regulares MySQL (REGEXP) são altamente úteis para buscar dados, validação e conversão de formatos. Este artigo abordou os fundamentos de regex no MySQL, novos recursos adicionados no MySQL 8.0 e posteriores, exemplos práticos, considerações importantes e FAQs.

7.1 Principais Pontos

Aqui está um rápido resumo dos pontos mais importantes.

1. Conceitos Básicos de Expressões Regulares MySQL

• Usar o operador REGEXP permite combinações de padrões mais flexíveis que LIKE.
• Padrões regex comuns
• ^ (início), $ (fim), . (qualquer caractere único), [] (classe de caracteres), + (uma ou mais repetições), etc.

2. Novas Funções de Expressão Regular no MySQL 8.0+

No MySQL 8.0, essas quatro funções foram adicionadas, permitindo um processamento mais flexível:

• REGEXP_LIKE() : uma alternativa ao operador REGEXP
• REGEXP_INSTR() : obtém a posição inicial da substring correspondente
• REGEXP_SUBSTR() : extrai a substring correspondente
• REGEXP_REPLACE() : substitui substrings usando regex

3. Exemplos Práticos

• Validação de endereço de e‑mail
• Verificação de número de telefone e número de cartão de crédito
• Remoção de tags HTML
• Validação de formatos de código postal
• Detecção de palavras proibidas
• Limpeza de dados (remover espaços em branco, remover vírgulas, etc.)

4. Considerações Importantes ao Usar Expressões Regulares

• Otimização de desempenho
• Como REGEXP não usa índices, pode resultar em varreduras completas de tabela.
• Você pode melhorar o desempenho combinando LIKE ou índices FULLTEXT.
• Medidas de segurança
• Para prevenir ReDoS (Denial of Service por Expressão Regular), evite o uso excessivo de .*.
• Para prevenir injeção de SQL, use placeholders.

5. FAQ (Perguntas Frequentes)

• Padrões regex disponíveis no MySQL
• Diferenças entre LIKE e REGEXP
• Diferenças de recursos entre MySQL 5.x e 8.x
• Solução de problemas quando regex não se comporta como esperado
• Maneiras de melhorar o desempenho

7.2 Melhores Práticas para Usar Expressões Regulares

1. Mantenha os padrões regex simples

• Exemplo: Evite o uso excessivo de .* e utilize ^ (início) e $ (fim).

2. Combine com LIKE ou FULLTEXT quando apropriado

• Pré-filtre com LIKE, então aplique REGEXP para reduzir o custo da consulta.

3. Use REGEXP_REPLACE() para limpeza de dados

• Exemplo: Remover tags HTML e normalizar espaços desnecessários.

4. Aproveite o MySQL 8.0 e posteriores

• Usar novas funções como REGEXP_LIKE() pode tornar o SQL mais legível.

5. Aplique medidas de segurança de forma completa

• Não aplique a entrada do usuário diretamente ao REGEXP (prevenção de injeção de SQL).
• Use placeholders para consultas dinâmicas.

7.3 Recursos para Aprendizado Adicional

Aqui estão referências para ajudá-lo a aprender expressões regulares MySQL mais profundamente.

Documentação Oficial

• MySQL 8.0 Regular Expression Reference
• MySQL 5.x Regular Expression Basics

7.4 Notas Finais

Expressões regulares MySQL podem ser usadas em muitas situações, incluindo busca de dados, limpeza e validação. Contudo, é importante usá‑las adequadamente, compreendendo as considerações de desempenho e segurança.

Esperamos que este artigo ajude você ao trabalhar com expressões regulares MySQL. Tente aplicar essas técnicas em seus projetos!