Rechercher des occurrences de chaîne dans la base de données MySQL

En tant qu’administrateur de base de données, vous devez parfois rechercher dans toute une base de données MySQL chaque occurrence d’une chaîne ou d’un motif — par exemple, pour savoir combien d’employés ont enregistré une adresse Gmail, ou pour retrouver une valeur lorsque vous ne savez pas dans quelle table elle est stockée.

Ce tutoriel présente deux méthodes fondées sur une interface graphique (GUI) : la recherche avec phpMyAdmin et la recherche avec MySQL Workbench. Vous pouvez également écrire des commandes SQL et des procédures stockées qui accomplissent la même tâche, mais l’accent est mis ici sur les options graphiques.

Préparer une base de données d’exemple pour la recherche

Les deux méthodes ci-dessous utilisent les mêmes données d’exemple : une base de données nommée person avec deux tables, student et teacher. Chaque table possède une clé primaire ID ainsi que les colonnes FIRST_NAME, LAST_NAME, GENDER et EMAIL.

Exécutez les instructions suivantes sur votre propre serveur MySQL — par exemple, dans l’onglet SQL de phpMyAdmin ou dans un onglet de requête de MySQL Workbench — avant de suivre l’une ou l’autre des méthodes graphiques ci-dessous.

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));

Remplissez les deux tables avec des instructions INSERT multilignes. Notez la virgule entre chaque groupe de lignes : une virgule manquante entre les lignes de VALUES est une cause fréquente d’erreurs de syntaxe.

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

Dans phpMyAdmin, la nouvelle base de données person et ses deux tables apparaissent dans le panneau de navigation de gauche :

Vérifiez les lignes insérées avec SELECT * FROM teacher; et SELECT * FROM student; :

Rechercher dans toutes les tables avec phpMyAdmin

phpMyAdmin peut rechercher dans chaque table d’une base de données en une seule passe :

1. Sélectionnez la base de données dans le panneau de gauche afin que la recherche s’y applique (encadré 1 dans la capture d’écran ci-dessous).
2. Ouvrez l’onglet Search et saisissez la chaîne, le mot ou la phrase à rechercher (encadré 2).
3. Choisissez une option de correspondance — par exemple, the exact phrase as substring trouve les lignes où le texte apparaît n’importe où dans une colonne (encadré 3).
4. Sélectionnez les tables à inclure — sélectionnez-les toutes pour une recherche dans toute la base de données (encadré 4) — puis cliquez sur Go.

phpMyAdmin indique ensuite le nombre de correspondances trouvées dans chaque table. Cliquez sur Browse à côté du compteur d’une table pour ouvrir directement les lignes correspondantes.

Rechercher dans des tables sélectionnées avec MySQL Workbench

MySQL Workbench offre une fonctionnalité similaire via Database → Search Table Data, mais il ne recherche que dans les tables que vous sélectionnez d’abord :

1. Dans le panneau Schemas, sélectionnez les tables à rechercher (encadré 1). MySQL Workbench ignore toutes les tables que vous ne sélectionnez pas ; sélectionnez-les donc toutes pour couvrir l’ensemble de la base de données.
2. Ouvrez le menu Database et choisissez Search Table Data.
3. Choisissez une option de correspondance — CONTAINS trouve les lignes où le texte recherché apparaît n’importe où dans une valeur (encadré 3).
4. Saisissez la chaîne ou l’expression à rechercher (encadré 4).
5. Définissez éventuellement le nombre maximal de correspondances par table et pour l’ensemble de la base de données, et choisissez si la recherche doit porter sur tous les types de colonnes.
6. Cliquez sur Start Search.

La grille de résultats indique le schéma, le nom de la table, la clé primaire, le nom de la colonne où la correspondance a été trouvée et la valeur correspondante (encadré 6), ce qui vous permet d’aller directement à la ligne qui contient la chaîne.

Interpréter les résultats et choisir une méthode de recherche

Les deux outils présentent les correspondances différemment. phpMyAdmin répond à la question « quelles tables contiennent cette chaîne ? » avec un nombre de correspondances par table et un lien Browse vers les lignes correspondantes, ce qui convient pour repérer rapidement où se trouve une valeur. MySQL Workbench répond à la question « quelles lignes et quelles colonnes exactes la contiennent ? » en indiquant le schéma, la table, la clé primaire, la colonne et la valeur de chaque correspondance, ce qui convient lorsque vous avez besoin de l’emplacement précis.

Les deux interfaces finissent par exécuter du SQL sur le serveur. Le programme exécutable ci-dessous utilise le module standard sqlite3 de Python sur les mêmes données d’exemple pour montrer ce qu’une telle recherche fait au niveau de la requête : il vérifie les colonnes de type caractère de chaque table à la recherche d’une sous-chaîne et indique la table, la colonne et la clé primaire de chaque correspondance. Exécutez cet exemple :

Le code et les libellés ou sorties affichés dans cette section restent en anglais.

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

Pour des recherches ponctuelles, l’une ou l’autre interface graphique suffit. Pour des vérifications répétables que vous souhaitez planifier ou scripter, le SQL pur — un motif LIKE par colonne, ou une procédure stockée pilotée par INFORMATION_SCHEMA — est le choix le plus naturel.