MySQL REGEXP Explicado: Guia Completo com Exemplos (Funções do MySQL 8.0 Incluídas)

1. Introdução

O que é Expressão Regular MySQL (REGEXP)?

As expressões regulares do MySQL são ferramentas poderosas para pesquisar e manipular strings de forma flexível dentro de um banco de dados. Elas permitem correspondência avançada de padrões que é difícil de alcançar com buscas de strings padrão, permitindo extrair dados que correspondam a formatos ou condições específicas.

Por exemplo, você pode extrair facilmente dados como “nomes que começam com um caractere específico” ou “códigos que contêm apenas valores numéricos”. Essa funcionalidade é particularmente útil para limpeza de dados e tratamento de condições de busca complexas.

Benefícios de Usar Expressões Regulares no MySQL

1. Suporte a Condições de Busca Complexas

• Você pode especificar padrões de strings complexos que não podem ser tratados pelo operador LIKE padrão.

1. Extração e Substituição em Lote de Dados

• Por exemplo, você pode extrair apenas os dados que correspondem a um formato específico ou substituir parte de uma string.

1. Recursos Aprimorados no MySQL 8.0 e Posteriores

• Novas funções como REGEXP_LIKE e REGEXP_SUBSTR foram adicionadas, permitindo operações mais flexíveis.

Propósito deste Artigo

Este artigo explica tudo, desde o uso básico de expressões regulares MySQL (REGEXP) até exemplos avançados e considerações importantes. Seja você um iniciante ou um semi‑profissional, encontrará insights práticos úteis para aplicações do mundo real.

Na próxima seção, explicaremos os fundamentos das expressões regulares no MySQL.

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

O que é o Operador REGEXP?

No MySQL, o operador REGEXP é usado para trabalhar com expressões regulares. Esse operador determina se um valor corresponde a um padrão especificado. Além disso, RLIKE funciona como um alias para REGEXP.

O exemplo a seguir verifica se uma string corresponde ao padrão “abc”.

SELECT * FROM users WHERE name REGEXP 'abc';

Sintaxe Básica do Operador REGEXP

A sintaxe básica para buscas com expressões regulares é a seguinte:

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

Lista de Padrões REGEXP Comuns

Diferença entre REGEXP e LIKE

Exemplos Práticos: Busca com REGEXP

Exemplo 1: Busca por Formato de E‑mail Válido

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

Exemplo 2: Busca por Campos que Contêm Apenas Números

SELECT * FROM orders WHERE order_id REGEXP '^[0-9]+$';

Resumo

Nesta seção, abordamos o uso básico e os padrões do operador REGEXP no MySQL. Isso permite desde buscas simples até correspondência avançada de padrões para operações de dados flexíveis.

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

REGEXP_LIKE() – Verificação de Correspondência Usando uma Expressão Regular

REGEXP_LIKE(string, pattern [, flags])

Exemplo:

SELECT REGEXP_LIKE('abcdef', 'abc');

Resultado: 1 (corresponde)

REGEXP_INSTR() – Encontrar a Posição da Correspondência

REGEXP_INSTR(string, pattern [, start_position, occurrence, flags, return_type])

Exemplo:

SELECT REGEXP_INSTR('abcdef', 'cd');

Resultado: 3

REGEXP_SUBSTR() – Extrair a Substring Correspondente

REGEXP_SUBSTR(string, pattern [, start_position, occurrence, flags])

Exemplo:

SELECT REGEXP_SUBSTR('abc123def', '[0-9]+');

Resultado: 123

REGEXP_REPLACE() – Substituir Usando uma Expressão Regular

REGEXP_REPLACE(string, pattern, replacement [, start_position, occurrence, flags])

Exemplo:

SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');

Resultado: Item###Price###

Resumo

As funções de expressão regular adicionadas no MySQL 8.0 permitem operações de strings detalhadas e flexíveis. Ao utilizá‑las de forma eficaz, você pode extrair e transformar dados de maneira mais eficiente.

4. Casos de Uso Práticos para Expressões Regulares

Search for Data That Matches Specific Patterns

Example 1: Detect Email Address Format

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

Example 2: Detect Phone Number Format

SELECT * FROM contacts WHERE phone REGEXP '^[0-9]{3}-[0-9]{4}-[0-9]{4}$';

Extract Substrings

Example 1: Extract the Numeric Part

SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+');

Result: 123

Replace Data

Example 1: Replace Numbers with “#”

SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');

Result: Item###Price###

Data Validation and Cleaning

Example 1: Detect Invalid Email Addresses

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

Summary

With these examples, you can efficiently handle tasks such as searching, extracting, replacing, and validating data.

5. Important Considerations and Best Practices

Handling Multibyte Characters (Full-Width Characters)

Since MySQL regular expressions are evaluated on a byte basis by default, special care is required when handling multibyte characters such as Japanese characters.

Solution:

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Impact on Performance

Issue: Because regular expressions involve complex processing, performance may degrade when searching large datasets.
Solution:

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

Protection Against ReDoS (Regular Expression Denial of Service)

Issue: Malicious patterns may cause excessive system load.
Solution:

1. Use simple patterns whenever possible.
2. Strengthen input validation.
3. Monitor query execution time.

Check Version Compatibility

The newer regular expression functions are not available in MySQL versions earlier than 8.0. Always verify your environment version before implementation.

Test in a Staging Environment

Test query behavior and performance in advance, including how edge cases and invalid values are handled.

Summary

Keep these best practices in mind to use regular expressions safely and efficiently while considering both performance and security.

6. Conclusion

Key Takeaways

1. By learning basic operations and regular expression patterns, you can handle everything from simple searches to complex data extraction.
2. The regular expression functions introduced in MySQL 8.0 allow for even more flexible operations.
3. Using practical examples improves efficiency in real-world data manipulation.
4. Applying best practices ensures safe and high-performance queries.

Benefits of Using MySQL Regular Expressions

• Advanced Search Capabilities: Easily define conditions that are difficult with simple string searches.
• Improved Data Processing Efficiency: Extraction, replacement, and validation can be completed directly within SQL.
• Wide Range of Applications: Useful for everything from data cleansing to log analysis.

How to Continue Learning and Applying

1. Practice queries using real datasets to deepen your understanding.
2. Actively leverage the latest version features to optimize performance.
3. Regularly review your queries to maintain security and speed.

Final Thoughts

By mastering MySQL regular expressions, you can improve operational efficiency and enhance your data analysis capabilities.

7. Frequently Asked Questions (FAQ)

Q1. What is the difference between REGEXP and LIKE in MySQL?

A. REGEXP supports advanced pattern matching, while LIKE is mainly used for partial string matching.

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

Q2. Como posso melhorar o desempenho?

A.

1. Aplique as condições de filtro com antecedência.
2. Use índices de forma eficaz.
3. Mantenha as consultas simples e otimizadas.

Q3. Como lidar com caracteres multibyte?

A. Configure o suporte a UTF-8.

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Q4. Você pode mostrar um exemplo de substituição usando expressões regulares?

A. Substitua números por “#”.

SELECT REGEXP_REPLACE('Item123Price456', '[0-9]', '#');

Q5. Qual é a consulta para converter um formato de data?

A. Altere “YYYY/MM/DD” para “YYYY-MM-DD”.

SELECT REGEXP_REPLACE('2023/12/20', '/', '-');

Q6. Como especificar múltiplas condições usando REGEXP?

A. Use o símbolo pipe (|).

SELECT * FROM products WHERE name REGEXP 'phone|tablet';

Resumo

A seção de FAQ abordou perguntas comuns e forneceu exemplos práticos de consultas.