mysql讯搜获取所有数据_MySQL查询以搜索完整数据库中的记录

i'm searching for a SQL query to run through PhpMyAdmin to find all records in all the tables of the database that contains a given string.

till now all i've found is a query:

SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'final_year_project_demo'

that returns all the tables in the schema , but i'm not efficient in SQL so not able to move any further than that.

all i need is a query that will list all the records(containing a particular string in any column) from all the tables that are listed through above code.

columns in all tables are same.

解决方案

This is a somewhat complicated thing to do. And you really can't do it in one step. I'll give you something to start with, and you'll have to take it from there:

select CONCAT("SELECT * FROM ", TABLE_NAME, " WHERE user_id=1;") FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME="user_id";

Now this will produce output like this:

+--------------------------------------------------------------------+

| CONCAT("SELECT * FROM ", TABLE_NAME, " WHERE user_id=1;") |

+--------------------------------------------------------------------+

| SELECT * FROM table0 WHERE user_id=1; |

| SELECT * FROM table1 WHERE user_id=1; |

Now, you want to turn around and execute all of those commands... so do it like this:

select CONCAT("SELECT * FROM ", TABLE_NAME, " WHERE user_id=1;") FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME="user_id" INTO OUTFILE "some_file_path_and_name";

That will give you a text file full of all of the commands you are looking for.

Update---

I missed the "For any column bit.."

select CONCAT("SELECT * FROM ", TABLE_NAME, " WHERE ", COLUMN_NAME, "='WHATEVER';") FROM INFORMATION_SCHEMA.COLUMNS WHERE COLLATION_NAME IS NOT NULL INTO OUTFILE 'somepath';

Here, we are using the fact that you said you are looking for a string, and all of the string type fields have a collation_name. Replace WHATEVER with what you are looking for.