我想从一个MySQL数据库的所有表的所有字段搜索一个给定的字符串，可能使用语法为:
SELECT * FROM * WHERE * LIKE '%stuff%'
有可能做这样的事情吗?
当前回答
如果您像躲避瘟疫一样避免存储过程，或者由于权限而无法执行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。(你的办公桌前可能已经有人了。)
干杯，杰伊;-]
其他回答
如果你安装了phpMyAdmin，使用它的“搜索”功能。
选择您的数据库 确保你有一个DB选择(即不是一个表，否则你会得到一个完全不同的搜索对话框) 点击“搜索”标签 选择您想要的搜索词 选择要搜索的表
我在250个表/10GB的数据库上使用过这个功能(在一个快速的服务器上)，响应时间非常惊人。
12年过去了，还没有人发帖回答下面这个问题:
我想从MySQL数据库的所有表中搜索给定字符串的所有字段
答案包括gui、模糊的概念、语法错误、需要表名或前缀的过程以及各种扭曲。下面是一个实际的、有效的、经过测试的、简单易用的答案，它建立在多个先前的答案的基础上，同时还将主键添加到结果中。
DROP PROCEDURE IF EXISTS findAll;
DELIMITER $$
CREATE PROCEDURE findAll( IN `search` TEXT )
BEGIN
SET SESSION group_concat_max_len := @@max_allowed_packet;
SELECT GROUP_CONCAT(
"SELECT '", c1.TABLE_NAME, "' AS `table`, '", c1.COLUMN_NAME, "' AS `column`, ",
"CONCAT_WS(',', ", (SELECT GROUP_CONCAT(c2.column_name) FROM `information_schema`.`columns` c2 WHERE c1.TABLE_SCHEMA=c2.TABLE_SCHEMA AND c1.TABLE_NAME=c2.TABLE_NAME AND c2.COLUMN_KEY='PRI') ,") AS pri,",
c1.COLUMN_NAME, " AS value FROM ", c1.TABLE_NAME,
" WHERE `",c1.COLUMN_NAME,"` LIKE '%", search, "%'" SEPARATOR "\nUNION\n") AS col
INTO @sql
FROM information_schema.columns c1
WHERE c1.TABLE_SCHEMA = DATABASE();
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
就是这样。你现在可以调用findAll('foobar');
不除外。你会遇到两个问题:
MySQL错误1436:线程堆栈溢出 准备好的语句需要重新准备。
将以下两行添加到/etc/mysql/mysql.conf.d/mysqld.cnf或任何你的cnf所在的地方，或者将它们保存在一个单独的文件中并复制到conf.d目录。
thread_stack = 2M
table_definition_cache = 5000
是的，显然这不应该在生产环境中运行，因为它不安全，而且会降低您的性能。
您可以对数据库(及其数据)执行SQLDump，然后搜索该文件。
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搜索文本。
我也做了我自己的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
