mySQL 查询在数据库中的所有表中搜索字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7523039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mySQL query to search all tables within a database for a string?
提问by user784637
Is there a mySQL query to search all tables within a database?
是否有 mySQL 查询来搜索数据库中的所有表？
If not can you search all tables within a database from the mySQL workbench GUI?
如果不能，您可以从 mySQL 工作台 GUI 搜索数据库中的所有表吗？
From phpmyadmin there's a search panel you can use to select all tablesto search through. I find this super effective since magento, the ecommerce package I'm working with has hundreds of tables and different product details are in different tables.
从 phpmyadmin 有一个搜索面板，您可以使用它来选择要搜索的所有表。自 magento 以来，我发现这非常有效，我正在使用的电子商务包有数百个表，不同的产品详细信息位于不同的表中。
采纳答案by entropid
If you want to do it purely in MySQL, without the help of any programming language, you could use this:
如果你想纯粹在 MySQL 中完成，没有任何编程语言的帮助，你可以使用这个：
## Table for storing resultant output
CREATE TABLE `temp_details` (
`t_schema` varchar(45) NOT NULL,
`t_table` varchar(45) NOT NULL,
`t_field` varchar(45) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
## Procedure for search in all fields of all databases
DELIMITER $$
#Script to loop through all tables using Information_Schema
DROP PROCEDURE IF EXISTS get_table $$
CREATE PROCEDURE get_table(in_search varchar(50))
READS SQL DATA
BEGIN
DECLARE trunc_cmd VARCHAR(50);
DECLARE search_string VARCHAR(250);
DECLARE db,tbl,clmn CHAR(50);
DECLARE done INT DEFAULT 0;
DECLARE COUNTER INT;
DECLARE table_cur CURSOR FOR
SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP ''',in_search,''';')
,table_schema,table_name,column_name
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
#Truncating table for refill the data for new search.
PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;";
EXECUTE trunc_cmd ;
OPEN table_cur;
table_loop:LOOP
FETCH table_cur INTO search_string,db,tbl,clmn;
#Executing the search
SET @search_string = search_string;
SELECT search_string;
PREPARE search_string FROM @search_string;
EXECUTE search_string;
SET COUNTER = @CNT_VALUE;
SELECT COUNTER;
IF COUNTER>0 THEN
# Inserting required results from search to table
INSERT INTO temp_details VALUES(db,tbl,clmn);
END IF;
IF done=1 THEN
LEAVE table_loop;
END IF;
END LOOP;
CLOSE table_cur;
#Finally Show Results
SELECT * FROM temp_details;
END $$
DELIMITER ;
回答by evaldeslacasa
If you are using MySQL Workbench, you can do this by doing right click on the DB Schema you want to search into, and then "Search Table Data...".
如果您使用的是MySQL Workbench，您可以通过右键单击要搜索的数据库架构，然后“搜索表数据...”来完成此操作。
In there you can select the "Search using REXEXP" option, and then type your text of search as usual. It will provide the DB rows matching your specific text.
在那里您可以选择“使用 REEXXP 搜索”选项，然后像往常一样输入搜索文本。它将提供与您的特定文本匹配的数据库行。
You will need to check the "Search columns of all types"box as well.
您还需要选中“所有类型的搜索列”框。
回答by ?kio
Alternatively, if your database is not that huge, you can make a dump and make your search in the .sql generated file.
或者，如果您的数据库不是那么大，您可以进行转储并在 .sql 生成的文件中进行搜索。
回答by Philip Olson
In MySQL Workbench you can use the Table Data Searchfeature. It can search across multiple tables and/or multiple databases.
在 MySQL Workbench 中，您可以使用表数据搜索功能。它可以搜索多个表和/或多个数据库。
回答by albfan
Search string in all tables on a database is a complex task. Normally you don't need to use exactly all tables and results are complex to read without a specific layout (tree of tables with matches or the like)
在数据库的所有表中搜索字符串是一项复杂的任务。通常，您不需要完全使用所有表格，并且如果没有特定布局（具有匹配项的表格树等），结果会很复杂。
SQL Workbench/Joffers a GUI and a command-line version to do such task:
SQL Workbench/J提供了一个 GUI 和一个命令行版本来完成这样的任务：
More info:
更多信息：
- http://www.sql-workbench.net/manual/wb-commands.html#command-search-data
- http://www.sql-workbench.net/manual/dbexplorer.html#search-table-data
- http://www.sql-workbench.net/manual/wb-commands.html#command-search-data
- http://www.sql-workbench.net/manual/dbexplorer.html#search-table-data
NOTE:Search with JDBC driver uses a lot of memory if it is not configured properly. SQL Workbench/J warns about that and although online documentation is a bit outdated, the sources of documentation (doc/xml/db-problems.xml) explain how to fix it for different BBDD:
注意：如果配置不正确，使用 JDBC 驱动程序搜索会占用大量内存。SQL Workbench/J 对此发出警告，虽然在线文档有点过时，但文档来源 (doc/xml/db-problems.xml) 解释了如何针对不同的 BBDD 修复它：
Here an extract for Postgres:
这是 Postgres 的摘录：
The PostgreSQL JDBC driver defaults to buffer the results obtained from the database in memory before returning them to the application. This means that when retrieving data, &wb-productname; uses (for a short amount of time) twice as much memory as really needed. This also means that WbExport or WbCopy will effectively read the entire result into memory before writing it into the output file. For large exports, this is usually not wanted. This behavior of the driver can be changed so that the driver uses cursor based retrieval. To do this, the connection profile must disable the "Autocommit" option and must define a default fetch size that is greater than zero. A recommended value is e.g. 10, it might be that higher numbers give a better performance. The number defined for the fetch size, defines the number of rows the driver keeps in its internal buffer before requesting more rows from the backend.
PostgreSQL JDBC 驱动程序默认将从数据库获得的结果缓存在内存中，然后再将它们返回给应用程序。这意味着在检索数据时，&wb-productname; 使用（在短时间内）两倍于实际需要的内存。这也意味着 WbExport 或 WbCopy 将在将整个结果写入输出文件之前有效地将其读取到内存中。对于大型出口，这通常是不需要的。可以更改驱动程序的这种行为，以便驱动程序使用基于游标的检索。为此，连接配置文件必须禁用“自动提交”选项，并且必须定义一个大于零的默认提取大小。推荐值是例如 10，它可能是更高的数字提供更好的性能。为获取大小定义的数字，
