在 MySQL 資料庫中搜尋字串的出現

身為資料庫管理員，有時需要搜尋整個 MySQL 資料庫，找出某個字串或模式的每一次出現；例如查看有多少員工註冊了 Gmail 地址，或在不知道值儲存在哪個資料表時將其找出來。

本教學介紹兩種圖形使用者介面（GUI）方法：使用 phpMyAdmin 搜尋，以及使用 MySQL Workbench 搜尋。你也可以撰寫能完成相同工作的 SQL 命令和預存程序，但本文重點是 GUI 選項。

準備用於搜尋的範例資料庫

以下兩種方法使用相同的範例資料：一個名為 person 的資料庫，其中包含 student 和 teacher 兩個資料表。每個資料表都有 ID 主鍵，以及 FIRST_NAME、LAST_NAME、GENDER 和 EMAIL 欄位。

在按照以下任一 GUI 方法操作之前，請先在你自己的 MySQL 伺服器上執行下列陳述式，例如在 phpMyAdmin 的 SQL 分頁或 MySQL Workbench 查詢分頁中執行。

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

使用多列 INSERT 陳述式填入兩個資料表。請注意每個資料列群組之間的逗號：VALUES 資料列之間缺少逗號是常見的語法錯誤原因。

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

在 phpMyAdmin 中，新的 person 資料庫及其兩個資料表會出現在左側導覽面板：

使用 SELECT * FROM teacher; 和 SELECT * FROM student; 驗證插入的資料列：

使用 phpMyAdmin 搜尋所有資料表

phpMyAdmin 可以一次搜尋資料庫中的每個資料表：

1. 在左側面板選取資料庫，使搜尋套用至該資料庫（下方螢幕截圖中的方框 1）。
2. 開啟 Search 分頁，輸入要尋找的字串、單字或片語（方框 2）。
3. 選擇比對選項；例如，the exact phrase as substring 會找出文字出現在欄位中任何位置的資料列（方框 3）。
4. 選取要納入的資料表；若要搜尋整個資料庫，請全選（方框 4），然後按一下 Go。

接著，phpMyAdmin 會報告每個資料表中找到的相符數。按一下資料表計數旁的 Browse，即可直接開啟相符資料列。

使用 MySQL Workbench 搜尋選取的資料表

MySQL Workbench 透過 Database → Search Table Data 提供類似功能，但它只會搜尋你先選取的資料表：

1. 在 Schemas 面板中，選取要搜尋的資料表（方框 1）。MySQL Workbench 會略過每個未選取的資料表，因此若要完整涵蓋資料庫，請全選。
2. 開啟 Database 功能表，選擇 Search Table Data。
3. 選擇比對選項；CONTAINS 會找出搜尋文字出現在值中任何位置的資料列（方框 3）。
4. 輸入要尋找的字串或運算式（方框 4）。
5. 可選擇設定每個資料表和整個資料庫的最大相符數，並選擇是否搜尋所有欄位類型。
6. 按一下 Start Search。

結果格線會報告綱要、資料表名稱、主鍵、找到相符項的欄位名稱，以及相符的值（方框 6），因此你可以直接跳到包含該字串的資料列。

解讀結果並選擇搜尋方法

這兩種工具回報相符項的方式不同。phpMyAdmin 回答「哪些資料表包含這個字串？」，提供每個資料表的計數和進入相符資料列的 Browse 連結，適合快速了解某個值位於何處。MySQL Workbench 回答「哪些確切的資料列和欄位包含它？」，列出每個相符項的綱要、資料表、主鍵、欄位和值，適合需要精確位置的情況。

這兩個介面最終都會對伺服器執行 SQL。下方可執行程式在相同範例資料上使用 Python 標準庫的 sqlite3 模組，展示這類搜尋在查詢層的運作方式：它會檢查每個資料表的字元欄位是否包含子字串，並報告每個命中項的資料表、欄位和主鍵。範例中的資料表和欄位名稱會保留英文原名。請執行此範例：

本節中的程式碼以及顯示的標籤或輸出仍為英文。

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

對於一次性查詢，任一 GUI 都足夠。對於想要排程或編寫成指令碼的可重複檢查，直接使用 SQL（例如對每個欄位使用 LIKE 模式，或使用由 INFORMATION_SCHEMA 驅動的預存程序）是更自然的選擇。