mySQL查询来搜索数据库中的所有表以查找字符串? [英] mySQL query to search all tables within a database for a string?
本文介绍了mySQL查询来搜索数据库中的所有表以查找字符串?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否存在一个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屋!
查看全文