MySQL：information_schema查找某个表的主键是否在数据的其他位置出现之二

上一篇： MySQL：information_schema查找某个表的主键是否在数据的其他位置出现之一-CSDN博客

摘要

遍历数据库每一张表的每一个字段，是否存在字符串search_term

正文

源码

import pymysql
from datetime import datetime

# 测试函数
if __name__ == '__main__':
    # 连接参数
    db_config = {
        'host': 'xx.x.x.x',
        'user': 'xx',
        'password': 'xx',
        'database': 'xx'
    }

    # 要搜索的字符串
    search_term = '%attach%'

    # 连接到数据库
    connection = pymysql.connect(**db_config)
    with open('xx.txt', 'a') as file:
        try:
            print("Connected to database!")
            with connection.cursor() as cursor:
                # 获取所有表和字段
                # 获取所有表和字段
                cursor.execute("""
                           SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
                           FROM information_schema.COLUMNS c
                           JOIN information_schema.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
                           WHERE t.TABLE_TYPE = 'BASE TABLE'
                           and c.TABLE_SCHEMA = 'xx'  # 替换为你的数据库名
                       """)
                columns = cursor.fetchall()

                # 遍历所有表和字段
                for schema, table, column in columns:
                    # 构造查询
                    query = f"SELECT `{column}` FROM `{schema}`.`{table}` WHERE `{column}` LIKE {connection.escape(search_term)}"
                    now = datetime.now()
                    formatted_now = now.strftime("%Y-%m-%d %H:%M:%S")
                    # print(f"{formatted_now}Executing: {query}")
                    scan_table = f"{formatted_now}Executing: {query}\n"

                    # 执行查询（这里只打印SQL，实际使用时可以执行并检查结果）
                    cursor.execute(query)
                    results = cursor.fetchall()
                    if results:
                        file.write(scan_table)
                        has_value = f"Found in {schema}.{table}.{column}\n"
                        # print(f"Found in {schema}.{table}.{column}")
                        file.write(has_value)

        finally:
            connection.close()