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搜索文本。

我建立在之前的答案，有这个，一些额外的填充，只是为了能够方便地连接所有的输出:

SELECT 
CONCAT('SELECT ''',A.TABLE_NAME, '-' ,A.COLUMN_NAME,''' FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
       ' WHERE ', A.COLUMN_NAME, ' LIKE \'%Value%\' UNION')
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'
UNION SELECT 'SELECT '''

-- for exact match use: A.COLUMN_NAME, ' LIKE \'Value\' instead

首先运行它，然后粘贴并运行结果(无需编辑)，它将显示使用该值的所有表名和列。

我要用这个。你只需要改变变量

$query ="SELECT `column_name` FROM `information_schema`.`columns` WHERE `table_schema`='" . $_SESSION['db'] . "' AND `table_name`='" . $table . "' ";
$stmt = $dbh->prepare($query);
$stmt->execute(); 
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);       

$query="SELECT name FROM `" . $database . "`.`" . $table . "` WHERE ( ";
foreach ( $columns as $column ) {
    $query .=" CONVERT( `" . $column['column_name'] . "` USING utf8 ) LIKE '%" . $search . "%' OR ";
}
$query = substr($query, 0, -3);
$query .= ")";

echo $query . "<br>";
$stmt=$dbh->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r ($results );
echo "</pre>";

如果23个答案还不够，这里还有2个……根据数据库结构和内容，您可能会发现其中一个实际上是快速而简单的解决方案。

对于shell一行程序的爱好者，这里有一个很长的程序(实际上只有2行，使用变量):

cmd='mysql -u Username -pYour_Password -D Your_Database' # <-- Adapt this

$cmd -s -e 'SHOW TABLES' | while read table; do echo "=== $table ==="; $cmd -B -s -e "SELECT * FROM $table" | grep 'Your_Search'; done

或多行，使其更具可读性:

$cmd -s -e 'SHOW TABLES' \
| while read table; do
    echo "=== $table ===";
    $cmd -B -s -e "SELECT * FROM $table" \
    | grep 'Your_Search';
  done

-s(——silent)用于屏蔽列名标头 -B(——batch)转义像换行符这样的特殊字符，所以我们在使用grep时获得整个记录

对于Perl爱好者来说，这将允许您使用正则表达式:

# perl -MDBI -le '($db,$u,$p)=@ARGV; $dbh=DBI->connect("dbi:mysql:dbname=$db",$u,$p); foreach $table ($dbh->tables()) {print "$table\n"; foreach $r ($dbh->selectall_array("SELECT * FROM $table")) {$_=join("\t", @$r); print $_ if (/Your_Regex/);}}' Your_Database Username Your_Password

在“真正的”Perl脚本中可能是这样的:

#!/usr/bin/perl

use strict;
use open qw(:std :utf8);

use DBI;

my $db_host  = 'localhost';
my $db       = 'Your_Database';
my $db_user  = 'Username';
my $db_pass  = 'Your_Password';

my $search    = qr/Your_regex_Search/;


# https://metacpan.org/pod/DBD::mysql
my $dbh = DBI->connect( "dbi:mysql:dbname=$db;host=$db_host", $db_user, $db_pass,
                        { mysql_enable_utf8mb4 => 1 }
) or die "Can't connect: $DBI::errstr\n";


foreach my $table ( $dbh->tables() ) {
    my $sth = $dbh->prepare("SELECT * FROM $table")
        or die "Can't prepare: ", $dbh->errstr;

    $sth->execute
        or die "Can't execute: ", $sth->errstr;

    my @results;

    while (my @row = $sth->fetchrow()) {
        local $_ = join("\t", @row);
        if ( /$search/ ) {
            push @results, $_;
        }
    }

    $sth->finish;

    next unless @results;

    print "*** TABLE $table :\n",
          join("\n---------------\n", @results),
          "\n" . "=" x 20 . "\n";
}

$dbh->disconnect;

我修改了一点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，然后搜索该文件。