使用MySQL Workbench可以很容易地选择几个表，并在所有这些数据库表中搜索文本;-)

这个解决方案 a)只有MySQL，不需要其他语言，并且 b)返回SQL结果，准备处理!

#Search multiple database tables and/or columns
#Version 0.1 - JK 2014-01
#USAGE: 1. set the search term @search, 2. set the scope by adapting the WHERE clause of the `information_schema`.`columns` query
#NOTE: This is a usage example and might be advanced by setting the scope through a variable, putting it all in a function, and so on...

#define the search term here (using rules for the LIKE command, e.g % as a wildcard)
SET @search = '%needle%';

#settings
SET SESSION group_concat_max_len := @@max_allowed_packet;

#ini variable
SET @sql = NULL;

#query for prepared statement
SELECT
    GROUP_CONCAT("SELECT '",`TABLE_NAME`,"' AS `table`, '",`COLUMN_NAME`,"' AS `column`, `",`COLUMN_NAME`,"` AS `value` FROM `",TABLE_NAME,"` WHERE `",COLUMN_NAME,"` LIKE '",@search,"'" SEPARATOR "\nUNION\n") AS col
INTO @sql
FROM `information_schema`.`columns`
WHERE TABLE_NAME IN
(
    SELECT TABLE_NAME FROM `information_schema`.`columns`
    WHERE
        TABLE_SCHEMA IN ("my_database")
        && TABLE_NAME IN ("my_table1", "my_table2") || TABLE_NAME LIKE "my_prefix_%"
);

#prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我已经使用HeidiSQL做到了这一点。 它不容易找到，但按Ctrl+Shift+F它会显示“表格工具”对话框。然后选择要搜索的内容(全库到单表)，输入“要查找的文本”值，点击“查找”。我发现它惊人的快(不到一分钟870MiB db)

这是我的解决方案

DROP PROCEDURE IF EXISTS findAll;
CREATE PROCEDURE `findAll`( IN `tableName` VARCHAR( 28 ) , IN `search` TEXT )
BEGIN
       DECLARE finished INT DEFAULT FALSE ;
       DECLARE columnName VARCHAR ( 28 ) ;
       DECLARE stmtFields TEXT ;
       DECLARE columnNames CURSOR FOR
              SELECT DISTINCT `COLUMN_NAME` FROM `information_schema`.`COLUMNS`
              WHERE `TABLE_NAME` = tableName ORDER BY `ORDINAL_POSITION` ;
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
       SET stmtFields = '' ;
       OPEN columnNames ;
       readColumns: LOOP
              FETCH columnNames INTO columnName ;
              IF finished THEN
                     LEAVE readColumns ;
              END IF;
              SET stmtFields = CONCAT(
                     stmtFields , IF ( LENGTH( stmtFields ) > 0 , ' OR' , ''  ) ,
                     ' `', tableName ,'`.`' , columnName , '` REGEXP "' , search , '"'
              ) ;
       END LOOP;
       SET @stmtQuery := CONCAT ( 'SELECT * FROM `' , tableName , '` WHERE ' , stmtFields ) ;
       PREPARE stmt FROM @stmtQuery ;
       EXECUTE stmt ;
       CLOSE columnNames ;
END;

如果您像躲避瘟疫一样避免存储过程，或者由于权限而无法执行mysql_dump，或者遇到其他各种原因。

我建议采用如下三步法:

1)该查询构建了一堆查询作为结果集。

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER 
# ** USE AN ALTERNATE BACKUP **

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' LIKE \'%stuff%\';') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     
        (
            A.DATA_TYPE LIKE '%text%'
        OR  
            A.DATA_TYPE LIKE '%char%'
        )
;

.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' IN (\'%1234567890%\');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;

.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT 
    CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN (\'%someText%\');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE LIKE '%blob%'
;

结果应该是这样的:

2)然后只需右键单击并使用复制行(标签分隔)

3)将结果粘贴到一个新的查询窗口，并运行到您的心脏内容。

详细信息:我排除了通常在工作台中看不到的系统模式，除非选中了显示元数据和内部模式选项。

我这样做是为了提供一种快速的方法来分析整个主机或DB(如果需要的话)，或者运行OPTIMIZE语句来支持性能改进。

我相信你可以有不同的方法来做到这一点，但以下是对我有用的方法:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

MySQL版本:5.6.23 警告:如果出现以下情况，请不要运行此命令: 您担心会导致表锁(密切关注您的客户端连接) 你不确定你在做什么。 你在试图激怒你的DBA。(你的办公桌前可能已经有人了。)

干杯，杰伊;-]

PHP函数:

function searchAllDB($search){
    global $mysqli;
    
    $out = Array();
    
    $sql = "show tables";
    $rs = $mysqli->query($sql);
    if($rs->num_rows > 0){
        while($r = $rs->fetch_array()){
            $table = $r[0];
            $sql_search = "select * from `".$table."` where ";
            $sql_search_fields = Array();
            $sql2 = "SHOW COLUMNS FROM `".$table."`";
            $rs2 = $mysqli->query($sql2);
            if($rs2->num_rows > 0){
                while($r2 = $rs2->fetch_array()){
                    $column = $r2[0];
                    $sql_search_fields[] = "`".$column."` like('%".$mysqli->real_escape_string($search)."%')";
                }
                $rs2->close();
            }
            $sql_search .= implode(" OR ", $sql_search_fields);
            $rs3 = $mysqli->query($sql_search);
            $out[$table] = $rs3->num_rows."\n";
            if($rs3->num_rows > 0){
                $rs3->close();
            }
        }
        $rs->close();
    }
    
    return $out;
}

print_r(searchAllDB("search string"));