mysql遍历查询+C#_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 list = GetTableList();

Query(list, "1111aaaa");

Console.WriteLine("over");

Console.ReadLine();

}

static List GetTableList()

{

using (MySqlConnection conn = GetConnection())

{

Dictionary dic = new Dictionary();

MySqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "select table_name, column_name from information_schema.columns where table_schema = 'mydb';";

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 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 ColumnList { get; set; } = new List();

}

}