Suchen nach Vorkommen von Zeichenketten in der MySQL-Datenbank

Als Datenbankadministrator müssen Sie manchmal eine gesamte MySQL-Datenbank durchsuchen, um jedes Vorkommen einer Zeichenfolge oder eines Musters zu finden — etwa um zu sehen, wie viele Mitarbeiter eine Gmail-Adresse registriert haben, oder um einen Wert zu finden, wenn Sie nicht wissen, in welcher Tabelle er gespeichert ist.

Dieses Tutorial behandelt zwei Methoden mit grafischer Benutzeroberfläche (GUI): die Suche mit phpMyAdmin und die Suche mit MySQL Workbench. Sie können auch SQL-Befehle und Stored Procedures schreiben, die dieselbe Aufgabe erfüllen, doch der Schwerpunkt liegt hier auf den GUI-Optionen.

Beispieldatenbank für die Suche vorbereiten

Beide folgenden Methoden verwenden dieselben Beispieldaten: eine Datenbank namens person mit zwei Tabellen, student und teacher. Jede Tabelle hat einen ID-Primärschlüssel sowie die Spalten FIRST_NAME, LAST_NAME, GENDER und EMAIL.

Führen Sie die folgenden Anweisungen auf Ihrem eigenen MySQL-Server aus — zum Beispiel im SQL-Tab von phpMyAdmin oder in einem Abfrage-Tab von MySQL Workbench — bevor Sie einer der beiden GUI-Methoden unten folgen.

CREATE DATABASE person;

USE person;

CREATE TABLE student (ID INT PRIMARY KEY, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), GENDER VARCHAR(10), EMAIL VARCHAR(100));

CREATE TABLE teacher (ID INT PRIMARY KEY, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), GENDER VARCHAR(10), EMAIL VARCHAR(100));

Füllen Sie beide Tabellen mit mehrzeiligen INSERT-Anweisungen. Achten Sie auf das Komma zwischen den einzelnen Zeilengruppen: Ein fehlendes Komma zwischen den VALUES-Zeilen ist eine häufige Ursache für Syntaxfehler.

INSERT INTO student (ID, FIRST_NAME, LAST_NAME, GENDER, EMAIL) VALUES (1, 'Shaajeel', 'Daniel', 'Male', 'shajeeld@gmail.com'), (2, 'Nayya', 'Preston', 'Female', 'npreston@yahoo.com'), (3, 'James', 'Robert', 'Male', 'james@yahoo.com'), (4, 'Jennifer', 'John', 'Female', 'jennifer09@hotmail.com'), (5, 'Sarah', 'Paul', 'Female', 'sarahpaul011@yahoo.com'), (6, 'Karen', 'Donald', 'Female', 'dkaren@gmail.com');

INSERT INTO teacher (ID, FIRST_NAME, LAST_NAME, GENDER, EMAIL) VALUES (1, 'Thomas', 'Christopher', 'Male', 'thomasC098@hotmail.com'), (2, 'Lisa', 'Mark', 'Female', 'lisamark@gmail.com'), (3, 'Anthony', 'Richard', 'Male', 'anthony044@yahoo.com'), (4, 'Matthew', 'Charles', 'Male', 'matthewcharles@gmail.com'), (5, 'Kiren', 'Donald', 'Female', 'dkiren@gmail.com');

In phpMyAdmin erscheinen die neue person-Datenbank und ihre beiden Tabellen im linken Navigationsbereich:

Prüfen Sie die eingefügten Zeilen mit SELECT * FROM teacher; und SELECT * FROM student;:

Alle Tabellen mit phpMyAdmin durchsuchen

phpMyAdmin kann jede Tabelle einer Datenbank in einem Durchlauf durchsuchen:

1. Wählen Sie die Datenbank im linken Bereich aus, damit die Suche für sie gilt (Feld 1 im Screenshot unten).
2. Öffnen Sie den Tab Search und geben Sie die Zeichenfolge, das Wort oder den Ausdruck ein, den Sie finden möchten (Feld 2).
3. Wählen Sie eine Abgleichoption — zum Beispiel findet the exact phrase as substring Zeilen, in denen der Text an beliebiger Stelle innerhalb einer Spalte vorkommt (Feld 3).
4. Wählen Sie die einzubeziehenden Tabellen aus — wählen Sie für eine datenbankweite Suche alle aus (Feld 4) — und klicken Sie auf Go.

phpMyAdmin meldet anschließend die Anzahl der in jeder Tabelle gefundenen Treffer. Klicken Sie neben der Anzahl einer Tabelle auf Browse, um die passenden Zeilen direkt zu öffnen.

Ausgewählte Tabellen mit MySQL Workbench durchsuchen

MySQL Workbench bietet eine ähnliche Funktion über Database → Search Table Data, durchsucht jedoch nur die Tabellen, die Sie vorher auswählen:

1. Wählen Sie im Schemas-Bereich die zu durchsuchenden Tabellen aus (Feld 1). MySQL Workbench überspringt jede nicht ausgewählte Tabelle; wählen Sie daher alle aus, um die gesamte Datenbank abzudecken.
2. Öffnen Sie das Menü Database und wählen Sie Search Table Data.
3. Wählen Sie eine Abgleichoption — CONTAINS findet Zeilen, in denen der Suchtext an beliebiger Stelle in einem Wert vorkommt (Feld 3).
4. Geben Sie die Zeichenfolge oder den Ausdruck ein, nach dem gesucht werden soll (Feld 4).
5. Legen Sie optional die maximale Anzahl von Treffern pro Tabelle und für die gesamte Datenbank fest und wählen Sie, ob alle Spaltentypen durchsucht werden sollen.
6. Klicken Sie auf Start Search.

Das Ergebnisraster zeigt Schema, Tabellenname, Primärschlüssel, den Spaltennamen, in dem der Treffer gefunden wurde, und den übereinstimmenden Wert an (Feld 6), sodass Sie direkt zu der Zeile springen können, die die Zeichenfolge enthält.

Ergebnisse interpretieren und eine Suchmethode wählen

Die beiden Werkzeuge melden Treffer unterschiedlich. phpMyAdmin beantwortet die Frage „Welche Tabellen enthalten diese Zeichenfolge?“ mit einer Anzahl pro Tabelle und einem Browse-Link zu den passenden Zeilen — ideal für einen schnellen Überblick darüber, wo ein Wert vorkommt. MySQL Workbench beantwortet die Frage „Welche exakten Zeilen und Spalten enthalten ihn?“, indem es für jeden Treffer Schema, Tabelle, Primärschlüssel, Spalte und Wert auflistet — ideal, wenn Sie den genauen Fundort benötigen.

Beide Oberflächen führen letztlich SQL auf dem Server aus. Das folgende lauffähige Programm verwendet Pythons Standardmodul sqlite3 auf denselben Beispieldaten, um zu zeigen, was eine solche Suche auf Abfrageebene tut: Es prüft die Zeichenspalten jeder Tabelle auf eine Teilzeichenfolge und meldet Tabelle, Spalte und Primärschlüssel jedes Treffers. Führen Sie dieses Beispiel aus:

Der Code und die angezeigten Bezeichnungen oder Ausgaben in diesem Abschnitt bleiben auf Englisch.

import sqlite3

connection = sqlite3.connect(":memory:")
cursor = connection.cursor()

cursor.execute("CREATE TABLE student (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, gender TEXT, email TEXT)")
cursor.execute("CREATE TABLE teacher (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, gender TEXT, email TEXT)")

students = [
    (1, "Shaajeel", "Daniel", "Male", "shajeeld@gmail.com"),
    (2, "Nayya", "Preston", "Female", "npreston@yahoo.com"),
    (3, "James", "Robert", "Male", "james@yahoo.com"),
    (4, "Jennifer", "John", "Female", "jennifer09@hotmail.com"),
    (5, "Sarah", "Paul", "Female", "sarahpaul011@yahoo.com"),
    (6, "Karen", "Donald", "Female", "dkaren@gmail.com"),
]
teachers = [
    (1, "Thomas", "Christopher", "Male", "thomasC098@hotmail.com"),
    (2, "Lisa", "Mark", "Female", "lisamark@gmail.com"),
    (3, "Anthony", "Richard", "Male", "anthony044@yahoo.com"),
    (4, "Matthew", "Charles", "Male", "matthewcharles@gmail.com"),
    (5, "Kiren", "Donald", "Female", "dkiren@gmail.com"),
]

cursor.executemany("INSERT INTO student VALUES (?, ?, ?, ?, ?)", students)
cursor.executemany("INSERT INTO teacher VALUES (?, ?, ?, ?, ?)", teachers)

term = "%gmail%"
columns = ["first_name", "last_name", "gender", "email"]

for table in ("student", "teacher"):
    for column in columns:
        rows = cursor.execute(
            f"SELECT id, {column} FROM {table} WHERE {column} LIKE ?", (term,)
        ).fetchall()
        for row in rows:
            print(f"{table}.{column} (id={row[0]}): {row[1]}")

connection.close()

student.email (id=1): shajeeld@gmail.com
student.email (id=6): dkaren@gmail.com
teacher.email (id=2): lisamark@gmail.com
teacher.email (id=4): matthewcharles@gmail.com
teacher.email (id=5): dkiren@gmail.com

Die Ausgabe des Beispiels bleibt unverändert auf Englisch, da die angezeigten Bezeichnungen direkt aus dem Programmcode stammen.

Für einmalige Abfragen genügt eine der beiden GUIs. Für wiederholbare Prüfungen, die Sie planen oder skripten möchten, ist reines SQL — ein LIKE-Muster pro Spalte oder eine Stored Procedure, die über INFORMATION_SCHEMA gesteuert wird — die natürlichere Wahl.