MySQL查询以搜索完整数据库中的记录

问题描述

我正在搜索要通过PhpMyAdmin运行的SQL查询，以查找包含给定字符串的数据库所有表中的所有记录.到目前为止，我发现的只是一个查询:

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

返回架构中的所有表，但是我在SQL中效率不高，因此无法进行进一步的移动.

我只需要一个查询，该查询将列出通过上述代码列出的所有表中的所有记录(在任何列中包含特定字符串).所有表中的列都相同.

解决方案

这是一件有些复杂的事情.而且您真的无法一步一步完成.我会给您一些开始，而您必须从那里开始:

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

现在这将产生如下输出:

+--------------------------------------------------------------------+
| CONCAT("SELECT * FROM ", TABLE_NAME, " WHERE user_id=1;")          |
+--------------------------------------------------------------------+
| SELECT * FROM table0 WHERE user_id=1;                              |
| SELECT * FROM table1 WHERE user_id=1;                              |

现在，您想转过头并执行所有这些命令...如此操作:

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";

这将为您提供一个文本文件，其中包含您要查找的所有命令.

更新---

我错过了对于任何列位."

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

在这里，我们使用的事实是，您说您正在寻找一个字符串，并且所有字符串类型字段都具有collat​​ion_name.将"WHATEVER"替换为您要寻找的内容.

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.

这篇关于MySQL查询以搜索完整数据库中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！