Buscar ocurrencias de cadenas en la base de datos MySQL

Como administrador de bases de datos, a veces tiene que buscar en toda una base de datos MySQL para encontrar cada aparición de una cadena o patrón, por ejemplo, para ver cuántos empleados registraron una dirección de Gmail o para localizar un valor cuando no sabe qué tabla lo almacena.

Este tutorial cubre dos métodos con interfaz gráfica de usuario (GUI): buscar con phpMyAdmin y buscar con MySQL Workbench. También puede escribir comandos SQL y procedimientos almacenados que realicen la misma tarea, pero aquí nos centraremos en las opciones de GUI.

Preparar una base de datos de ejemplo para la búsqueda

Los dos métodos siguientes utilizan los mismos datos de ejemplo: una base de datos llamada person con dos tablas, student y teacher. Cada tabla tiene una clave primaria ID y las columnas FIRST_NAME, LAST_NAME, GENDER y EMAIL.

Ejecute las siguientes sentencias en su propio servidor MySQL, por ejemplo, en la pestaña SQL de phpMyAdmin o en una pestaña de consultas de MySQL Workbench, antes de seguir cualquiera de los métodos de GUI que se describen a continuación.

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

Rellene ambas tablas con sentencias INSERT de varias filas. Observe la coma entre cada grupo de filas: una coma omitida entre las filas de VALUES es una causa frecuente de errores de sintaxis.

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

En phpMyAdmin, la nueva base de datos person y sus dos tablas aparecen en el panel de navegación izquierdo:

Compruebe las filas insertadas con SELECT * FROM teacher; y SELECT * FROM student;:

Buscar en todas las tablas con phpMyAdmin

phpMyAdmin puede buscar en todas las tablas de una base de datos en una sola pasada:

1. Seleccione la base de datos en el panel izquierdo para que la búsqueda se aplique a ella (cuadro 1 en la captura de pantalla siguiente).
2. Abra la pestaña Search y escriba la cadena, palabra o frase que desea encontrar (cuadro 2).
3. Elija una opción de coincidencia; por ejemplo, the exact phrase as substring encuentra las filas donde el texto aparece en cualquier parte de una columna (cuadro 3).
4. Seleccione las tablas que desea incluir (selecciónelas todas para una búsqueda en toda la base de datos, cuadro 4) y haga clic en Go.

A continuación, phpMyAdmin informa del número de coincidencias encontradas en cada tabla. Haga clic en Browse junto al recuento de una tabla para abrir las filas coincidentes directamente.

Buscar en tablas seleccionadas con MySQL Workbench

MySQL Workbench ofrece una función similar mediante Database → Search Table Data, pero solo busca en las tablas que usted seleccione primero:

1. En el panel Schemas, seleccione las tablas en las que desea buscar (cuadro 1). MySQL Workbench omite todas las tablas que no seleccione, así que selecciónelas todas para cubrir la base de datos completa.
2. Abra el menú Database y elija Search Table Data.
3. Elija una opción de coincidencia; CONTAINS encuentra las filas donde el texto de búsqueda aparece en cualquier parte de un valor (cuadro 3).
4. Escriba la cadena o expresión que desea buscar (cuadro 4).
5. Si lo desea, establezca el número máximo de coincidencias por tabla y para toda la base de datos, y elija si quiere buscar en todos los tipos de columna.
6. Haga clic en Start Search.

La cuadrícula de resultados muestra el esquema, el nombre de la tabla, la clave primaria, el nombre de la columna donde se encontró la coincidencia y el valor coincidente (cuadro 6), de modo que puede ir directamente a la fila que contiene la cadena.

Interpretar los resultados y elegir un método de búsqueda

Las dos herramientas informan de las coincidencias de forma diferente. phpMyAdmin responde a la pregunta «¿qué tablas contienen esta cadena?» con un recuento por tabla y un enlace Browse a las filas coincidentes, lo que resulta adecuado para una revisión rápida de dónde se encuentra un valor. MySQL Workbench responde a «¿qué filas y columnas exactas lo contienen?» enumerando el esquema, la tabla, la clave primaria, la columna y el valor de cada coincidencia, lo que resulta adecuado cuando necesita la ubicación precisa.

En última instancia, ambas interfaces ejecutan SQL contra el servidor. El programa ejecutable que aparece a continuación utiliza el módulo estándar sqlite3 de Python sobre los mismos datos de ejemplo para mostrar lo que hace una búsqueda de este tipo en la capa de consulta: comprueba las columnas de caracteres de cada tabla en busca de una subcadena e informa de la tabla, la columna y la clave primaria de cada acierto. Ejecute este ejemplo:

El código y las etiquetas o salidas visibles de esta sección se mantienen en inglés.

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

Para consultas puntuales, cualquiera de las dos GUI es suficiente. Para comprobaciones repetibles que quiera programar o convertir en un script, el SQL sin más (un patrón LIKE por columna o un procedimiento almacenado controlado por INFORMATION_SCHEMA) es la opción más natural.