Průvodce regulárními výrazy MySQL (REGEXP): syntaxe, funkce MySQL 8, příklady a osvědčené postupy

1. Úvod

Používání regulárních výrazů v MySQL

MySQL je výkonný systém pro správu databází určený k vyhledávání a manipulaci s daty a také podporuje regulární výrazy (REGEXP), které umožňují flexibilnější vyhledávání. S regulárními výrazy můžete efektivně extrahovat data odpovídající konkrétním vzorům, stejně jako formátovat nebo ověřovat data.

Výhody používání regulárních výrazů v MySQL

Používání regulárních výrazů v MySQL přináší výhody, jako jsou:

• Pokročilé vyhledávání : Snadno extrahujete data, která obsahují konkrétní řetězce nebo vzory.
• Kontrola integrity dat : Užitečné pro validaci vstupů (např. ověřování formátu e‑mailových adres).
• Výkonnější než operátor LIKE : Na rozdíl od LIKE, který používá zástupné znaky ( % a _ ), REGEXP vám umožňuje definovat složitější podmínky vyhledávání flexibilněji.

Účel a struktura tohoto článku

Tento článek vysvětluje vše od základů regulárních výrazů v MySQL po pokročilé použití, optimalizaci výkonu a nové funkce regulárních výrazů zavedené v MySQL 8.0 a novějších. S praktickými příklady je obsah navržen tak, aby byl snadno pochopitelný i pro začátečníky.

Struktura článku:

1. Základy a syntaxe regulárních výrazů v MySQL
2. Funkce regulárních výrazů přidané v MySQL 8.0 a novějších
3. Praktické příklady (validace, extrakce dat, konverze formátu)
4. Důležité úvahy při používání regulárních výrazů (výkon, bezpečnost)
5. Často kladené otázky (FAQ)
6. Shrnutí

2. Základy a syntaxe regulárních výrazů v MySQL

Jak používat regulární výrazy v MySQL

V MySQL používáte operátor REGEXP (nebo RLIKE) k práci s regulárními výrazy. Podobně jako operátor LIKE se používá k určení, zda hodnota sloupce odpovídá zadanému regex vzoru.

Základní syntaxe

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Nebo

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE je alias pro REGEXP a oba se chovají stejně.

Příklad
Například pro vyhledání záznamů, kde jméno obsahuje znak “山”:

SELECT * FROM users WHERE name REGEXP '山';

Základní vzory regulárních výrazů

V regulárních výrazech MySQL můžete použít následující vzory.

Rozdíly oproti operátoru LIKE

MySQL také poskytuje operátor LIKE, který se běžně používá pro jednoduché porovnávání vzorů. Nicméně REGEXP je výkonnější než LIKE a podporuje vyhledávání s komplexnějšími podmínkami.

Příklad 1: Použití operátoru LIKE

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

• S LIKE můžete vyhledávat data, která obsahují 山, ale můžete použít jen jednoduché zástupné znaky ( % a _ ).

Příklad 2: Použití operátoru REGEXP

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

• S REGEXP můžete extrahovat pouze data, kde jméno začíná na 山.

Jak specifikovat více vzorů

Můžete použít | k určení více vzorů s podmínkou OR.

Příklad: Získat uživatele, jejichž příjmení je “佐藤” nebo “田中”

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

Negativní shoda

Pokud použijete ^ uvnitř hranatých závorek, můžete získat data, která „neobsahují specifikované znaky“.

Příklad: Data, která začínají znakem jiným než “山”

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

V tomto případě vrací záznamy, kde jméno nezačíná na 山.

Shrnutí

• V MySQL REGEXP umožňuje výkonnější vyhledávání vzorů než LIKE.
• Porozumění základní syntaxi a vzorům ( . , ^ , $ , [] , * , + , atd.) umožňuje flexibilní vyhledávání.
• Je důležité pochopit rozdíly oproti LIKE a používat je vhodně.
• Použití | umožňuje vyhledávat s více vzory.
• Použití záporných znakových tříd [^ ] vám pomůže hledat data, která nezačínají konkrétními znaky.

3. Funkce regulárních výrazů přidané v MySQL 8.0 a novějších

V MySQL 8.0, kromě tradičního operátoru REGEXP, byly přidány čtyři nové funkce, které umožňují flexibilnější vyhledávání regulárních výrazů. Tyto funkce umožňují získat pozice shod, extrahovat podřetězce a provádět nahrazení, čímž výrazně rozšiřují možnosti práce s regexem v MySQL.

V této sekci podrobně vysvětlujeme každou novou funkci regulárních výrazů a ukazujeme konkrétní příklady.

3.1 REGEXP_LIKE()

Přehled

REGEXP_LIKE(), podobně jako operátor REGEXP, kontroluje, zda hodnota sloupce odpovídá zadanému regex vzoru.

Syntaxe

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

• column_name : sloupec, který se má prohledávat
• 'regex_pattern' : regex vzor použitý jako podmínka
• flags (optional) : rozlišení velkých/malých písmen (použijte i pro nerozlišování velikosti)

Příklady

“Získat uživatele, jejichž e‑mail končí na gmail.com nebo yahoo.co.jp”

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

“Vyhledat uživatelská jména obsahující ‚admin‘ (nerozlišující velikost písmen)”

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

✅ REGEXP_LIKE() slouží stejnému účelu jako operátor REGEXP, ale umožňuje více standardní styl přátelský k SQL.

3.2 REGEXP_INSTR()

Přehled

REGEXP_INSTR() vrací pozici (počáteční index), kde se v řetězci vyskytne shoda regexu.

Syntaxe

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

• start_position (volitelný): kde začít hledat (výchozí: 1)
• occurrence (volitelný): kterou výskyt shody vrátit (výchozí: 1)
• return_option (volitelný): 0 (vrátit počáteční pozici) nebo 1 (vrátit koncovou pozici shody)
• flags (volitelný): použijte i pro nerozlišování velikosti písmen

Příklady

“Pokud telefonní číslo začíná na 090 nebo 080, získat počáteční pozici”

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

“Získat počáteční pozici doménové části v e‑mailové adrese”

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

• Vrací pozici, kde se nachází @ (např. user@example.com vrací 5 ).

✅ S REGEXP_INSTR() můžete získat pozicní informace, čímž rozšiřujete možnosti zpracování řetězců.

3.3 REGEXP_SUBSTR()

Přehled

REGEXP_SUBSTR() extrahuje podřetězec, který odpovídá regex vzoru v řetězci.

Syntaxe

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

• occurrence (volitelný): získat n‑tý výskyt (výchozí: 1)
• flags (volitelný): rozlišení velikosti písmen (i pro ignorování velikosti)

Příklady

“Extrahovat pouze doménovou část z e‑mailové adresy”

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

“Získat první číselnou sekvenci ve zprávě”

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

✅ S REGEXP_SUBSTR() můžete extrahovat konkrétní vzory a použít je pro organizaci a transformaci dat.

3.4 REGEXP_REPLACE()

Přehled

REGEXP_REPLACE() nahrazuje podřetězce, které odpovídají regex vzoru, jiným řetězcem.

Syntaxe

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

• replacement : řetězec, kterým nahradíte odpovídající část
• occurrence (volitelný): nahradí pouze n‑tý výskyt (pokud je vynecháno, nahradí všechny výskyty)
• flags (volitelný): použijte i pro rozlišování velkých a malých písmen

Příklady

„Odstranit pomlčky (-) z telefonních čísel“

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

„Odstranit HTML značky“

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

✅ S REGEXP_REPLACE() je konverze formátů a čištění dat snadné.

3.5 Shrnutí

4. Praktické příklady regulárních výrazů v MySQL

Využitím regulárních výrazů v MySQL můžete zefektivnit úlohy zpracování reálných dat, jako je validace dat, extrakce konkrétních dat a konverze formátů. V této sekci představíme praktické případy použití s SQL kódem a vysvětleními.

4.1 Validace e‑mailových adres

Přehled

Použijte regulární výrazy k ověření, zda e‑mailové adresy uložené v databázi mají platný formát.

SQL k použití

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

Vysvětlení

• ^[a-zA-Z0-9._%+-]+ → jeden nebo více alfanumerických znaků, teček, podtržítek nebo znaků plus před @
• @[a-zA-Z0-9.-]+ → doménové jméno po @
• \.[a-zA-Z]{2,}$ → končí TLD (Top-Level Domain) o délce alespoň 2 písmena (např. .com, .jp, .net)

S tímto dotazem můžete vyloučit neplatné e‑mailové adresy (např. user@@example.com, user@.com).

4.2 Kontrola formátů japonských telefonních čísel

Přehled

Určete, zda běžná japonská telefonní čísla (např. 090-1234-5678, 03-1234-5678) odpovídají správnému formátu.

SQL k použití

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

Vysvětlení

• 0[789]0-[0-9]{4}-[0-9]{4} → mobilní čísla ( 090-xxxx-xxxx, 080-xxxx-xxxx atd.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → pevné linky ( 03-xxxx-xxxx, 06-xxxx-xxxx atd.)

Tímto způsobem jsou regulární výrazy užitečné, když je vyžadována konzistence formátu.

4.3 Validace formátu čísla kreditní karty

Přehled

Ověřte formáty čísel kreditních karet (Visa, Mastercard, American Express atd.).

SQL k použití

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

Vysvětlení

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

S tímto dotazem můžete v databázi odfiltrovat neplatná čísla karet (např. nesprávná délka nebo neplatné úvodní číslice).

4.4 Odstranění HTML značek

Přehled

Pokud uživatelská data obsahují HTML značky, můžete je odstranit a převést obsah na prostý text.

SQL k použití

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

Vysvětlení

• '<[^>]+>' → odstraňuje všechny HTML značky mezi < a >

Příklad

Je to užitečné pro odstranění HTML značek z uživatelských komentářů a blogových příspěvků.

4.5 Validace formátu poštovního kódu (Japonsko)

Přehled

Ověřte, zda japonské poštovní kódy (např. 123-4567) odpovídají správnému formátu.

SQL k použití

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

Vysvětlení

• ^[0-9]{3}-[0-9]{4}$ → poštovní kód ve formátu „3 číslice‑4 číslice“

Použití tohoto regulárního výrazu pomáhá udržovat konzistenci dat a předcházet nesprávným formátům.

4.6 Filtrování uživatelských jmen (detekce zakázaných slov)

Přehled

Zkontrolujte, zda uživatelská jména neobsahují zakázaná slova vyhrazená pro systémové použití (např. admin, root, system) během registrace.

SQL k použití

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

Vysvětlení

• admin|root|system → detekuje uživatelská jména obsahující tyto výrazy

Tento dotaz pomáhá zabránit běžným uživatelům v používání jmen určených pro systémové administrátory.

Shrnutí

• Regulární výrazy lze použít k validaci e‑mailových adres, telefonních čísel a čísel kreditních karet.
• Lze je také použít k odstraňování HTML tagů a konverzi datových formátů.
• Jsou účinné pro zlepšení bezpečnosti a konzistence databáze prostřednictvím úkolů, jako je filtrace zakázaných slov a validace poštovních kódů.

5. Důležité úvahy při používání regulárních výrazů (výkon a bezpečnost)

Používání regulárních výrazů v MySQL umožňuje výkonné vyhledávání a čištění dat. Nicméně, nesprávné použití může způsobit degradaci výkonu a zavést bezpečnostní rizika. Tato sekce vysvětluje klíčové úvahy pro bezpečné a efektivní používání MySQL regulárních výrazů.

5.1 Optimalizace výkonu pro regulární výrazy

Vyhledávání regulárních výrazů v MySQL je pohodlné, ale má nevýhodu: indexy se obecně nepoužívají, což může dotazy zpomalit.

Opatření ke zlepšení výkonu

1. Kombinujte indexy LIKE nebo FULLTEXT

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

2. Používejte jednodušší regulární výrazy

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

3. Nejprve filtrujte cílová data

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

5.2 Bezpečnostní rizika a protiopatření

1. DoS útok pomocí regulárních výrazů (ReDoS)

• Vyhněte se neomezeným shodám jako .*.
• Používejte ^ a $ k omezení rozsahu vyhledávání.
• Nepředávejte uživatelský vstup přímo do REGEXP.

2. SQL injection a regulární výrazy

❌ Nebezpečný dotaz

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

✅ Bezpečný dotaz (použijte zástupné symboly)

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

5.3 Kompatibilita verzí

Shrnutí

• Protože REGEXP nepoužívá indexy, jsou důležitá opatření pro výkon.
• Aby se zabránilo ReDoS (DoS útok pomocí regulárních výrazů), vyhněte se nadměrnému používání zástupných znaků.
• Aby se zabránilo SQL injection, nepoužívejte uživatelský vstup přímo v REGEXP.

6. FAQ (Často kladené otázky)

Zde jsou běžné otázky ohledně používání MySQL regulárních výrazů (REGEXP). Vyřešení těchto problémů vám pomůže používat regex efektivněji.

6.1 Jaké regex vzory mohu v MySQL použít?

6.2 Jaký je rozdíl mezi LIKE a REGEXP?

✅ Protože LIKE je rychlejší, upřednostněte LIKE pro jednoduchá vyhledávání a použijte REGEXP, když je potřeba složitější shoda.

6.3 Jaké jsou rozdíly ve funkcích regex mezi MySQL 5.x a MySQL 8.x?

6.4 Co mám dělat, pokud regex nefunguje podle očekávání?

✅ Kontrolní seznam

1. Zkontrolujte, zda je escapování správné

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

2. Vyzkoušejte příznak i s REGEXP_LIKE()

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

3. Zkontrolujte kódování dat

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Jak mohu zlepšit výkon REGEXP dotazů?

1. Předfiltrujte pomocí LIKE

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

2. Používejte indexy tam, kde je to vhodné

   ALTER TABLE users ADD FULLTEXT(email);
   

Shrnutí

• REGEXP je výkonnější než LIKE, ale musíte mít na paměti výkon.
• V MySQL 8.0 byly přidány nové regex funkce (např. REGEXP_LIKE()), které umožňují flexibilnější zpracování.
• Pro zlepšení výkonu: kombinujte s LIKE, používejte indexy a navrhujte jednoduché regex vzory.

7. Shrnutí

MySQL regular expressions (REGEXP) jsou velmi užitečné pro vyhledávání dat, validaci a konverzi formátů. Tento článek pokrýval základy regex v MySQL, nové funkce přidané v MySQL 8.0 a novějších verzích, praktické příklady, důležité úvahy a časté dotazy.

7.1 Klíčové poznatky

Zde je rychlý přehled nejdůležitějších bodů.

1. Základy regulárních výrazů v MySQL

• Použití operátoru REGEXP umožňuje flexibilnější porovnávání vzorů než LIKE.
• Běžné regex vzory
• ^ (začátek), $ (konec), . (libovolný jeden znak), [] (třída znaků), + (jedna nebo více opakování), atd.

2. Nové funkce regulárních výrazů v MySQL 8.0+

V MySQL 8.0 byly přidány tyto čtyři funkce, které umožňují flexibilnější zpracování:

• REGEXP_LIKE() : alternativa k operátoru REGEXP
• REGEXP_INSTR() : získá počáteční pozici odpovídajícího podřetězce
• REGEXP_SUBSTR() : extrahuje odpovídající podřetězec
• REGEXP_REPLACE() : nahrazuje podřetězce pomocí regexu

3. Praktické příklady

• Validace e‑mailových adres
• Kontrola telefonních čísel a čísel kreditních karet
• Odstraňování HTML tagů
• Validace formátů poštovních směrovacích čísel
• Detekce zakázaných slov
• Čištění dat (odstraňování mezer, odstraňování čárek atd.)

4. Důležité úvahy při používání regulárních výrazů

• Optimalizace výkonu
• Protože REGEXP nepoužívá indexy, může vést k plnému prohledávání tabulek.
• Výkon můžete zlepšit kombinací s indexy LIKE nebo FULLTEXT.
• Bezpečnostní opatření
• Aby se zabránilo ReDoS (Denial of Service pomocí regulárních výrazů), vyhněte se nadměrnému používání .*.
• Aby se zabránilo SQL injection, používejte placeholders.

5. FAQ (Často kladené otázky)

• Regex vzory dostupné v MySQL
• Rozdíly mezi LIKE a REGEXP
• Rozdíly ve funkcích mezi MySQL 5.x a 8.x
• Řešení problémů, když regex nefunguje podle očekávání
• Způsoby, jak zlepšit výkon

7.2 Nejlepší postupy pro používání regulárních výrazů

1. Udržujte regex vzory jednoduché

• Příklad: Vyhněte se nadměrnému používání .* a využívejte ^ (začátek) a $ (konec).

2. Kombinujte s LIKE nebo FULLTEXT tam, kde je to vhodné

• Předfiltrujte pomocí LIKE, pak použijte REGEXP ke snížení nákladů na dotaz.

3. Používejte REGEXP_REPLACE() pro čištění dat

• Příklad: Odstraňte HTML tagy a normalizujte zbytečné mezery.

4. Využijte MySQL 8.0 a novější

• Použití nových funkcí, jako je REGEXP_LIKE(), může učinit SQL čitelnější.

5. Důkladně aplikujte bezpečnostní opatření

• Nepoužívejte vstup uživatele přímo v REGEXP (prevence SQL injection).
• Používejte placeholders pro dynamické dotazy.

7.3 Zdroje pro další učení

Zde jsou odkazy, které vám pomohou hlouběji se naučit regulární výrazy v MySQL.

Oficiální dokumentace

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

7.4 Závěrečné poznámky

Regulární výrazy MySQL lze použít v mnoha situacích, včetně vyhledávání dat, čištění a validace. Je však důležité je používat vhodně a rozumět souvisejícím výkonovým a bezpečnostním úvahám.

Doufáme, že vám tento článek pomůže při práci s regulárními výrazy v MySQL. Vyzkoušejte tyto techniky ve svých projektech!