我想从一个MySQL数据库的所有表的所有字段搜索一个给定的字符串，可能使用语法为:
SELECT * FROM * WHERE * LIKE '%stuff%'
有可能做这样的事情吗?
当前回答
我修改了一点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; }
其他回答
如果您像躲避瘟疫一样避免存储过程，或者由于权限而无法执行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。(你的办公桌前可能已经有人了。)
干杯，杰伊;-]
如果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;
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搜索文本。
这是我的解决方案
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;
你可以用
SHOW TABLES;
然后获取这些表中的列(在循环中)
SHOW COLUMNS FROM table;
然后用这些信息创建很多很多的查询，如果你需要的话，你也可以UNION这些查询。
但这对数据库的影响非常大。特别是如果你在做LIKE搜索。
