mySQL 查询在数据库中的所有表中搜索字符串? [英] mySQL query to search all tables within a database for a string?

问题描述

是否有 mySQL 查询来搜索数据库中的所有表?

如果不能，您能否从 mySQL 工作台 GUI 搜索数据库中的所有表?

If not can you search all tables within a database from the mySQL workbench GUI?

phpmyadmin 中有一个搜索面板，您可以使用它选择所有表格进行搜索.自 magento 以来，我发现这非常有效，我正在使用的电子商务包有数百个表格，不同的产品详细信息位于不同的表格中.

From phpmyadmin there's a search panel you can use to select all tables to 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.

推荐答案

如果你想纯粹在 MySQL 中完成，不需要任何编程语言的帮助，你可以使用这个:

If you want to do it purely in MySQL, without the help of any programming language, you could use this:

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

来源:http://forge.mysql.com/tools/tool.php?id=232

这篇关于mySQL 查询在数据库中的所有表中搜索字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！