MySQL-Reguläre Ausdrücke (REGEXP) Leitfaden: Syntax, MySQL‑8‑Funktionen, Beispiele und bewährte Verfahren

1. Einführung

Verwendung von regulären Ausdrücken in MySQL

MySQL ist ein leistungsstarkes Datenbankverwaltungssystem zum Suchen und Manipulieren von Daten und unterstützt zudem reguläre Ausdrücke (REGEXP), um flexiblere Suchvorgänge zu ermöglichen. Mit regulären Ausdrücken können Sie effizient Daten extrahieren, die bestimmten Mustern entsprechen, sowie Daten formatieren oder validieren.

Vorteile der Verwendung von regulären Ausdrücken in MySQL

Die Verwendung von MySQL‑regulären Ausdrücken bietet Vorteile wie:

• Erweiterte Suche: Daten, die bestimmte Zeichenketten oder Muster enthalten, lassen sich einfach extrahieren.
• Datenintegrität prüfen: Nützlich für die Eingabevalidierung (z. B. Validierung von E‑Mail‑Adressformaten).
• Leistungsfähiger als der LIKE‑Operator: Im Vergleich zu LIKE, das Platzhalter (% und _) verwendet, ermöglicht REGEXP die flexiblere Definition komplexer Suchbedingungen.

Zweck und Aufbau dieses Artikels

Dieser Artikel erklärt alles von den Grundlagen regulärer Ausdrücke in MySQL bis hin zu fortgeschrittener Nutzung, Leistungsoptimierung und den neuen regulären Ausdrucksfunktionen, die in MySQL 8.0 und später eingeführt wurden. Mit praktischen Beispielen ist der Inhalt so gestaltet, dass er selbst für Anfänger leicht verständlich ist.

Aufbau des Artikels:

1. Grundlagen und Syntax regulärer Ausdrücke in MySQL
2. In MySQL 8.0 und später hinzugefügte Funktionen für reguläre Ausdrücke
3. Praktische Beispiele (Validierung, Datenauszug, Formatkonvertierung)
4. Wichtige Überlegungen bei der Verwendung regulärer Ausdrücke (Leistung, Sicherheit)
5. FAQ (Häufig gestellte Fragen)
6. Zusammenfassung

2. Grundlagen und Syntax regulärer Ausdrücke in MySQL

Verwendung von regulären Ausdrücken in MySQL

In MySQL verwendet man den Operator REGEXP (oder RLIKE), um mit regulären Ausdrücken zu arbeiten. Ähnlich dem LIKE‑Operator wird er genutzt, um zu prüfen, ob ein Spaltenwert einem angegebenen Regex‑Muster entspricht.

Grundsyntax

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Or

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE ist ein Alias für REGEXP, und beide verhalten sich identisch.

Beispiel
Zum Beispiel, um Datensätze zu finden, bei denen der Name das Zeichen „山“ enthält:

SELECT * FROM users WHERE name REGEXP '山';

Grundlegende Muster regulärer Ausdrücke

In MySQL‑regulären Ausdrücken können Sie Muster wie die folgenden verwenden.

Unterschiede zum LIKE‑Operator

MySQL stellt zudem den LIKE‑Operator bereit, der häufig für einfache Musterabgleiche verwendet wird. Allerdings ist REGEXP leistungsfähiger als LIKE und unterstützt die Suche mit komplexeren Bedingungen.

Beispiel 1: Verwendung des LIKE‑Operators

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

• Mit LIKE können Sie nach Daten suchen, die 山 enthalten, jedoch nur einfache Platzhalter (% und _) verwenden.

Beispiel 2: Verwendung des REGEXP‑Operators

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

• Mit REGEXP können Sie ausschließlich die Daten extrahieren, bei denen der Name mit 山 beginnt.

Wie man mehrere Muster angibt

Sie können | verwenden, um mehrere Muster mit einer ODER‑Bedingung anzugeben.

Beispiel: Benutzer, deren Nachname „佐藤“ oder „田中“ ist

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

Negatives Matching

Wenn Sie ^ innerhalb eckiger Klammern verwenden, können Sie Daten abrufen, die die angegebenen Zeichen nicht enthalten.

Beispiel: Daten, die mit einem anderen Zeichen als „山“ beginnen

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

In diesem Fall werden Datensätze zurückgegeben, bei denen der Name nicht mit 山 beginnt.

Zusammenfassung

• In MySQL ermöglicht REGEXP leistungsstärkere Mustersuche als LIKE.
• Verständnis der grundlegenden Syntax und Muster ( . , ^ , $ , [] , * , + , usw.) ermöglicht flexible Suche.
• Es ist wichtig, die Unterschiede zu LIKE zu verstehen und jede angemessen zu verwenden.
• Die Verwendung von | ermöglicht die Suche mit mehreren Mustern.
• Die Verwendung negativer Zeichenklassen [^ ] hilft bei der Suche nach Daten, die nicht mit bestimmten Zeichen beginnen.

3. Reguläre Ausdrucksfunktionen, die in MySQL 8.0 und neuer hinzugefügt wurden

In MySQL 8.0 wurden zusätzlich zum traditionellen REGEXP-Operator vier neue Funktionen hinzugefügt, um flexiblere reguläre Ausdrucks-Suchen zu ermöglichen. Diese machen es möglich, Übereinstimmungspositionen abzurufen, Teilstrings zu extrahieren und Ersetzungen durchzuführen, was die Möglichkeiten mit Regex in MySQL erheblich erweitert.

In diesem Abschnitt erklären wir jede neue reguläre Ausdrucksfunktion im Detail und zeigen konkrete Beispiele.

3.1 REGEXP_LIKE()

Übersicht

REGEXP_LIKE() prüft, ob ein Spaltenwert zu einem angegebenen Regex-Muster passt, ähnlich wie der REGEXP-Operator.

Syntax

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

• column_name : die zu durchsuchende Spalte
• 'regex_pattern' : das als Bedingung verwendete Regex-Muster
• flags (optional) : Groß-/Kleinschreibung ( i für Groß-/Kleinschreibung ignorierende Übereinstimmung verwenden)

Beispiele

„Benutzer abrufen, deren E-Mail mit gmail.com oder yahoo.co.jp endet“

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

„Nach Benutzernamen suchen, die ‚admin‘ enthalten (Groß-/Kleinschreibung ignorierend)“

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

✅ REGEXP_LIKE() dient dem gleichen Zweck wie der REGEXP-Operator, erlaubt aber einen standard-SQL-freundlicheren Stil.

3.2 REGEXP_INSTR()

Übersicht

REGEXP_INSTR() gibt die Position (Startindex), an der eine Regex-Übereinstimmung in einem String auftritt, zurück.

Syntax

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

• start_position (optional): wo die Suche beginnen soll (Standard: 1)
• occurrence (optional): welche Übereinstimmung zurückgeben (Standard: 1)
• return_option (optional): 0 (Startposition zurückgeben) oder 1 (Endposition der Übereinstimmung zurückgeben)
• flags (optional): i für Groß-/Kleinschreibung ignorierende Übereinstimmung verwenden

Beispiele

„Wenn die Telefonnummer mit 090 oder 080 beginnt, die Startposition abrufen“

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

„Die Startposition des Domain-Teils in einer E-Mail-Adresse abrufen“

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

• Es gibt die Position zurück, an der @ gefunden wird (z. B. gibt user@example.com 5 zurück).

✅ Mit REGEXP_INSTR() können Sie positionsbezogene Informationen abrufen und erweitern so Ihre Optionen für die String-Verarbeitung.

3.3 REGEXP_SUBSTR()

Übersicht

REGEXP_SUBSTR() extrahiert den Teilstring, der zu einem Regex-Muster passt, aus einem String.

Syntax

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

• occurrence (optional): die n-te Übereinstimmung abrufen (Standard: 1)
• flags (optional): Groß-/Kleinschreibung ( i um Groß-/Kleinschreibung zu ignorieren)

Beispiele

„Nur den Domain-Teil aus einer E-Mail-Adresse extrahieren“

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

„Die erste Zahlenfolge in einer Nachricht abrufen“

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

✅ Mit REGEXP_SUBSTR() können Sie spezifische Muster extrahieren und sie zur Organisation und Transformation von Daten verwenden.

3.4 REGEXP_REPLACE()

Übersicht

REGEXP_REPLACE() ersetzt Teilstrings, die zu einem Regex-Muster passen, durch einen anderen String.

Syntax

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

• replacement : die Zeichenkette, mit der der gefundene Teil ersetzt wird
• occurrence (optional): ersetze nur das n‑te Vorkommen (wenn weggelassen, werden alle Vorkommen ersetzt)
• flags (optional): verwende i für case‑insensitive (Groß‑/Kleinschreibung ignorierende) Suche

Beispiele

„Bindestriche (-) aus Telefonnummern entfernen“

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

„HTML‑Tags entfernen“

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

✅ Mit REGEXP_REPLACE() wird Formatkonvertierung und Datenbereinigung einfach.

3.5 Zusammenfassung

4. Praktische Beispiele für MySQL‑Reguläre Ausdrücke

Durch den Einsatz von MySQL‑Regulären Ausdrücken können Sie reale Datenverarbeitungsaufgaben wie Datenvalidierung, Extraktion spezifischer Daten und Formatkonvertierung vereinfachen. In diesem Abschnitt stellen wir praktische Anwendungsfälle mit SQL‑Code und Erklärungen vor.

4.1 Validierung von E‑Mail‑Adressen

Überblick

Verwenden Sie reguläre Ausdrücke, um zu prüfen, ob in der Datenbank gespeicherte E‑Mail‑Adressen einem gültigen Format entsprechen.

Zu verwendendes SQL

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

Erklärung

• ^[a-zA-Z0-9._%+-]+ → ein oder mehrere alphanumerische Zeichen, Punkte, Unterstriche oder Plus‑Zeichen vor @
• @[a-zA-Z0-9.-]+ → ein Domain‑Name nach @
• \.[a-zA-Z]{2,}$ → endet mit einer TLD (Top‑Level‑Domain) von mindestens 2 Buchstaben (z. B. .com, .jp, .net)

Mit dieser Abfrage können Sie ungültige E‑Mail‑Adressen ausschließen (z. B. user@@example.com, user@.com).

4.2 Überprüfung japanischer Telefonnummern

Überblick

Bestimmen Sie, ob gängige japanische Telefonnummern (z. B. 090-1234-5678, 03-1234-5678) dem korrekten Format entsprechen.

Zu verwendendes SQL

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

Erklärung

• 0[789]0-[0-9]{4}-[0-9]{4} → Mobilnummern ( 090‑xxxx‑xxxx, 080‑xxxx‑xxxx usw.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → Festnetznummern ( 03‑xxxx‑xxxx, 06‑xxxx‑xxxx usw.)

Auf diese Weise sind reguläre Ausdrücke nützlich, wenn Formatkonsistenz erforderlich ist.

4.3 Validierung des Kreditkartennummern‑Formats

Überblick

Validieren Sie die Formate von Kreditkartennummern (Visa, Mastercard, American Express usw.).

Zu verwendendes SQL

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

Erklärung

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

Mit dieser Abfrage können Sie ungültige Kartennummern in der Datenbank herausfiltern (z. B. falsche Länge oder ungültige Anfangsziffern).

4.4 Entfernen von HTML‑Tags

Überblick

Wenn von Benutzern eingereichte Daten HTML‑Tags enthalten, können Sie diese entfernen und den Inhalt in Klartext umwandeln.

Zu verwendendes SQL

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

Erklärung

• '<[^>]+>' → entfernt alle HTML‑Tags zwischen < und >

Beispiel

Das ist nützlich, um HTML‑Tags aus Benutzerkommentaren und Blog‑Posts zu entfernen.

4.5 Validierung des Postleitzahl‑Formats (Japan)

Überblick

Validieren Sie, ob japanische Postleitzahlen (z. B. 123-4567) dem korrekten Format entsprechen.

Zu verwendendes SQL

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

Erklärung

• ^[0-9]{3}-[0-9]{4}$ → eine Postleitzahl im „3‑Ziffern‑4‑Ziffern“‑Format

Die Verwendung dieses Regex hilft, Datenkonsistenz zu wahren und falsche Formate zu verhindern.

4.6 Benutzername‑Filterung (Erkennung unerlaubter Wörter)

Überblick

Überprüfen Sie während der Registrierung, ob Benutzernamen unzulässige Wörter enthalten, die für die Systemverwendung reserviert sind (z. B. admin, root, system).

SQL zum Verwenden

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

Erklärung

• admin|root|system → erkennt Benutzernamen, die diese Begriffe enthalten

Diese Abfrage hilft, regulären Benutzern den Gebrauch von Namen vorzubeugen, die für Systemadministratoren vorgesehen sind.

Zusammenfassung

• Reguläre Ausdrücke können für die Validierung von E-Mail-Adressen, Telefonnummern und Kreditkartennummern verwendet werden.
• Sie können auch angewendet werden, um HTML-Tags zu entfernen und Datenformate umzuwandeln.
• Sie sind effektiv zur Verbesserung der Datenbanksicherheit und -konsistenz durch Aufgaben wie das Filtern unzulässiger Wörter und die Validierung von Postleitzahlen.

5. Wichtige Überlegungen beim Einsatz von Regulären Ausdrücken (Leistung und Sicherheit)

Die Verwendung von regulären Ausdrücken in MySQL ermöglicht leistungsstarke Suchen und Datenbereinigung. Allerdings kann eine unsachgemäße Nutzung zu Leistungsabfall und Sicherheitsrisiken führen. Dieser Abschnitt erklärt wichtige Überlegungen für den sicheren und effizienten Einsatz von MySQL-Regulären Ausdrücken.

5.1 Leistungsoptimierung für Reguläre Ausdrücke

Die Regex-Suche in MySQL ist bequem, hat aber einen Nachteil: Indizes werden in der Regel nicht angewendet, was Abfragen verlangsamen kann.

Maßnahmen zur Leistungsverbesserung

1. Kombinieren Sie LIKE oder FULLTEXT-Indizes

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

2. Verwenden Sie einfachere Reguläre Ausdrücke

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

3. Filtern Sie die Zieldaten zuerst

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

5.2 Sicherheitsrisiken und Gegenmaßnahmen

1. Regular Expression DoS (ReDoS)

• Vermeiden Sie unbegrenzte Übereinstimmungen wie .*.
• Verwenden Sie ^ und $, um den Suchbereich einzugrenzen.
• Geben Sie Benutzereingaben nicht direkt in REGEXP weiter.

2. SQL-Injection und Reguläre Ausdrücke

❌ Unsichere Abfrage

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

✅ Sichere Abfrage (Platzhalter verwenden)

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

5.3 Versionskompatibilität

Zusammenfassung

• Da REGEXP keine Indizes verwendet, sind Leistungsmaßnahmen wichtig.
• Um ReDoS (Regular Expression DoS) zu verhindern, vermeiden Sie übermäßigen Einsatz von Wildcards.
• Um SQL-Injection zu verhindern, wenden Sie Benutzereingaben nicht direkt auf REGEXP an.

6. FAQ (Häufig Gestellte Fragen)

Hier sind gängige Fragen zum Einsatz von MySQL-Regulären Ausdrücken (REGEXP). Die Behebung dieser Probleme hilft Ihnen, Regex effektiver zu nutzen.

6.1 Welche Regex-Muster kann ich in MySQL verwenden?

6.2 Was ist der Unterschied zwischen LIKE und REGEXP?

✅ Da LIKE schneller ist, bevorzugen Sie LIKE für einfache Suchen und verwenden Sie REGEXP, wenn komplexe Übereinstimmungen erforderlich sind.

6.3 Was sind die Unterschiede in den Regex-Funktionen zwischen MySQL 5.x und MySQL 8.x?

6.4 Was sollte ich tun, wenn ein Regex nicht wie erwartet funktioniert?

✅ Checkliste

1. Überprüfen Sie, ob das Escaping korrekt ist

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

2. Versuchen Sie die i-Flag mit REGEXP_LIKE()

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

3. Überprüfen Sie die Datencodierung

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Wie kann ich die Leistung von REGEXP-Abfragen verbessern?

1. Vorfiltern mit LIKE

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

2. Verwenden Sie Indizes, wo angemessen

   ALTER TABLE users ADD FULLTEXT(email);
   

Zusammenfassung

• REGEXP ist mächtiger als LIKE, aber Sie müssen die Leistung im Auge behalten.
• In MySQL 8.0 wurden neue Regex‑Funktionen (wie REGEXP_LIKE()) hinzugefügt, die eine flexiblere Verarbeitung ermöglichen.
• Um die Leistung zu verbessern: Kombination mit LIKE, Verwendung von Indizes und Gestaltung einfacher Regex‑Muster.

7. Zusammenfassung

MySQL‑Reguläre Ausdrücke (REGEXP) sind äußerst nützlich für die Datensuche, Validierung und Formatumwandlung. Dieser Artikel behandelte die Grundlagen von MySQL‑Regex, neue Funktionen, die in MySQL 8.0 und später hinzugefügt wurden, praktische Beispiele, wichtige Überlegungen und FAQs.

7.1 Wichtigste Erkenntnisse

Hier ist eine kurze Zusammenfassung der wichtigsten Punkte.

1. Grundlagen regulärer Ausdrücke in MySQL

• Die Verwendung des REGEXP‑Operators ermöglicht flexibleres Musterabgleichen als LIKE.
• Häufige Regex‑Muster
• ^ (Anfang), $ (Ende), . (ein beliebiges einzelnes Zeichen), [] (Zeichenklasse), + (eine oder mehrere Wiederholungen) usw.

2. Neue Funktionen für reguläre Ausdrücke in MySQL 8.0+

In MySQL 8.0 wurden diese vier Funktionen hinzugefügt, die eine flexiblere Verarbeitung ermöglichen:

• REGEXP_LIKE() : eine Alternative zum REGEXP‑Operator
• REGEXP_INSTR() : gibt die Startposition der passenden Teilzeichenkette zurück
• REGEXP_SUBSTR() : extrahiert die passende Teilzeichenkette
• REGEXP_REPLACE() : ersetzt Teilzeichenketten mittels Regex

3. Praktische Beispiele

• Validierung von E‑Mail‑Adressen
• Prüfung von Telefonnummern und Kreditkartennummern
• Entfernen von HTML‑Tags
• Validierung von Postleitzahl‑Formaten
• Erkennung unerlaubter Wörter
• Datenbereinigung (Entfernen von Leerzeichen, Kommas usw.)

4. Wichtige Überlegungen bei der Verwendung regulärer Ausdrücke

• Leistungsoptimierung
• Da REGEXP keine Indizes nutzt, kann dies zu Volltabellenscans führen.
• Sie können die Leistung verbessern, indem Sie LIKE‑ oder FULLTEXT‑Indizes kombinieren.
• Sicherheitsmaßnahmen
• Um ReDoS (Regular Expression DoS) zu verhindern, vermeiden Sie übermäßigen Einsatz von .*.
• Um SQL‑Injection zu verhindern, verwenden Sie placeholders.

5. FAQ (Häufig gestellte Fragen)

• Verfügbare Regex‑Muster in MySQL
• Unterschiede zwischen LIKE und REGEXP
• Funktionsunterschiede zwischen MySQL 5.x und 8.x
• Fehlerbehebung, wenn Regex nicht wie erwartet funktioniert
• Möglichkeiten zur Leistungsverbesserung

7.2 Best Practices für die Verwendung regulärer Ausdrücke

1. Halten Sie Regex‑Muster einfach

• Beispiel: Vermeiden Sie übermäßige Verwendung von .* und nutzen Sie ^ (Anfang) und $ (Ende).

1. Kombinieren Sie mit LIKE oder FULLTEXT, wo es sinnvoll ist

• Vorfiltern mit LIKE, dann REGEXP anwenden, um die Abfragekosten zu senken.

1. Verwenden Sie REGEXP_REPLACE() für die Datenbereinigung

• Beispiel: Entfernen von HTML‑Tags und Normalisieren überflüssiger Leerzeichen.

1. Nutzen Sie MySQL 8.0 und später

• Der Einsatz neuer Funktionen wie REGEXP_LIKE() kann SQL lesbarer machen.

1. Sicherheitsmaßnahmen konsequent anwenden

• Wenden Sie Benutzereingaben nicht direkt auf REGEXP an (Verhinderung von SQL‑Injection).
• Verwenden Sie placeholders für dynamische Abfragen.

7.3 Ressourcen für weiterführendes Lernen

Hier sind Referenzen, die Ihnen helfen, MySQL‑Reguläre Ausdrücke tiefer zu verstehen.

Offizielle Dokumentation

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

7.4 Abschließende Hinweise

MySQL‑Reguläre Ausdrücke können in vielen Situationen eingesetzt werden, z. B. bei der Datensuche, -bereinigung und -validierung. Es ist jedoch wichtig, sie angemessen zu verwenden und die Leistungs‑ und Sicherheitsaspekte zu verstehen.

Wir hoffen, dieser Artikel hilft Ihnen bei der Arbeit mit MySQL‑Regulären Ausdrücken. Versuchen Sie, diese Techniken in Ihren Projekten anzuwenden!