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_NAMEFROM information_schema.COLUMNS cJOIN information_schema.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAMEWHERE 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()