如果您像躲避瘟疫一样避免存储过程，或者由于权限而无法执行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。(你的办公桌前可能已经有人了。)

干杯，杰伊;-]

这是检索所有列和表的最简单的查询

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

在phpMyAdmin中，可以通过搜索选项卡搜索所有表或名称中有特定字符串的表。

有良好的查询…\ ^ ^ /

我也做了我自己的mysql爬虫来搜索一些wordpress配置，在界面和数据库中都无法找到它，数据库转储太沉重和不可读。我得说我现在不能没有它。

它的工作原理类似于@Olivier，但它管理外来的数据库/表名，并且是像小丑一样安全。

<?php

$database = 'database';
$criteria = '*iemblo'; // you can use * and ? as jokers

$dbh = new PDO("mysql:host=127.0.0.1;dbname={$database};charset=utf8", 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$tables = $dbh->query("SHOW TABLES");
while (($table = $tables->fetch(PDO::FETCH_NUM)) !== false)
{
    $fields = $dbh->prepare("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?");
    $fields->execute(array ($database, $table[0]));

    $ors = array ();
    while (($field = $fields->fetch(PDO::FETCH_NUM)) !== false)
    {
        $ors[] = str_replace("`", "``", $field[0]) . " LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(:search, '\\\\', '\\\\\\\\'), '%', '\\%'), '_', '\\_'), '*', '%'), '?', '_')";
    }

    $request = 'SELECT * FROM ';
    $request .= str_replace("`", "``", $table[0]);
    $request .= ' WHERE ';
    $request .= implode(' OR ', $ors);
    $rows = $dbh->prepare($request);

    $rows->execute(array ('search' => $criteria));

    $count = $rows->rowCount();
    if ($count == 0)
    {
        continue;
    }

    $str = "Table '{$table[0]}' contains {$count} rows matching '{$criteria}'.";
    echo str_repeat('-', strlen($str)), PHP_EOL;
    echo $str, PHP_EOL;
    echo str_repeat('-', strlen($str)), PHP_EOL;

    $counter = 1;
    while (($row = $rows->fetch(PDO::FETCH_ASSOC)) !== false)
    {
        $col = 0;
        $title = "Row #{$counter}:";
        echo $title;
        foreach ($row as $column => $value)
        {
            echo
            (($col++ > 0) ? str_repeat(' ', strlen($title) + 1) : ' '),
            $column, ': ',
            trim(preg_replace('!\s+!', ' ', str_replace(array ("\r", "\t", "\n"), array ("", "", " "), $value))),
            PHP_EOL;
        }
        echo PHP_EOL;
        $counter++;
    }
}

运行这个脚本可以输出如下内容:

---------------------------------------------------
Table 'customers' contains 1 rows matching '*iemblo'.
---------------------------------------------------
Row #1: email_client: my@email.com
        numero_client_compta: C05135
        nom_client: Tiemblo
        adresse_facturation_1: 151, My Street
        adresse_facturation_2: 
        ville_facturation: Nantes
        code_postal_facturation: 44300
        pays_facturation: FR
        numero_tva_client: 
        zone_geographique: UE
        prenom_client: Alain
        commentaires: 
        nom_societe: 
        email_facturation: my@email.com

MySQL工作台

这里有一些说明。

下载并安装MSQL Workbench。

https://www.mysql.com/products/workbench/

安装时，可能需要安装Visual Studio c++ Redistributable。你可以在这里买到:

https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads

x64: vc_redist.x64.exe(适用于64位Windows)

打开MySQL Workbench时，必须输入主机名、用户名和密码。

在侧菜单栏上有一个Schemas选项卡，单击Schemas选项卡，然后双击数据库以选择要搜索的数据库。

然后转到菜单数据库-搜索数据，并输入你要搜索的文本，单击开始搜索。

海蒂SQLl

下载并安装HeidiSql https://www.heidisql.com/download.php

输入主机名、用户名和密码。

按Ctrl+Shift+F搜索文本。

我修改了一点Olivier的PHP答案:

print out the results in which the string was found omit tables without results also show output if column names match the search input show total number of results function searchAllDB($search){ global $mysqli; $out = ""; $total = 0; $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()){ $colum = $r2[0]; $sql_search_fields[] = $colum." like('%".$search."%')"; if(strpos($colum,$search)) { echo "FIELD NAME: ".$colum."\n"; } } $rs2->close(); } $sql_search .= implode(" OR ", $sql_search_fields); $rs3 = $mysqli->query($sql_search); if($rs3 && $rs3->num_rows > 0) { $out .= $table.": ".$rs3->num_rows."\n"; if($rs3->num_rows > 0){ $total += $rs3->num_rows; $out.= print_r($rs3->fetch_all(),1); $rs3->close(); } } } $out .= "\n\nTotal results:".$total; $rs->close(); } return $out; }

您可以对数据库(及其数据)执行SQLDump，然后搜索该文件。