c# 遍历 Mysql 所有表所有列，查找目标数据

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace ConsoleApp1
{
class Program
{
static void Main(string[] args)
{
List<MyTable> list = GetTableList();
Query(list, "1111aaaa");
Console.WriteLine("over");
Console.ReadLine();
} static List<MyTable> GetTableList()
{
using (MySqlConnection conn = GetConnection())
{
Dictionary<string, MyTable> dic = new Dictionary<string, MyTable>();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select table_name, column_name from information_schema.columns where table_schema = 'lpet6plusdb';";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string table = reader.GetString("table_name");
string column = reader.GetString("column_name");
if (dic.ContainsKey(table))
{
dic[table].ColumnList.Add(column);
}
else
{
MyTable t = new MyTable();
t.Table = table;
t.ColumnList.Add(column);
dic.Add(t.Table, t);
}
}
}
return dic.Values.ToList();
}
} static void Query(List<MyTable> list, string str)
{
using (MySqlConnection conn = GetConnection())
{
MySqlCommand cmd = conn.CreateCommand();
foreach (MyTable table in list)
{
foreach (string column in table.ColumnList)
{
cmd.CommandText = string.Format("select count(*) from {0} where `{1}` like '%{2}%'", table.Table, column, str);
object obj = cmd.ExecuteScalar();
if (Convert.ToInt32(obj) > 0)
{
Console.WriteLine(string.Format("TableName: {0}, ColumnName: {1}", table.Table, column));
}
}
}
}
} static MySqlConnection GetConnection()
{
MySqlConnection conn = new MySqlConnection("server=localhost;port=3306;user id=userid;password=pass;database=mydb;pooling=true;ConnectionTimeout=1800");
conn.Open();
return conn;
}
} public class MyTable
{
public string Table { get; set; }
public List<string> ColumnList { get; set; } = new List<string>();
}
}