在 MySQL 数据库的每个表的所有字段中搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/639531/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search in all fields from every table of a MySQL database
提问by RSilva
I want to search in all fields from all tables of a MySQL database a given string, possibly using syntax as:
我想在 MySQL 数据库的所有表的所有字段中搜索给定字符串，可能使用以下语法：
SELECT * FROM * WHERE * LIKE '%stuff%'
Is it possible to do something like this?
有可能做这样的事情吗？
采纳答案by Milhous
You can peek into the information_schemaschema. It has a list of all tables and all fields that are in a table. You can then run queries using the information that you have gotten from this table.
您可以查看information_schema架构。它具有所有表和表中所有字段的列表。然后，您可以使用从该表中获得的信息运行查询。
The tables involved are SCHEMATA, TABLES and COLUMNS. There are foreign keys such that you can build up exactly how the tables are created in a schema.
涉及的表是 SCHEMATA、TABLES 和 COLUMNS。有外键，您可以准确地构建在模式中创建表的方式。
回答by Dean Rather
You could do an SQLDumpof the database (and its data) then search that file.
您可以执行SQLDump数据库（及其数据）的操作，然后搜索该文件。
回答by Greg Lyon
If you have phpMyAdmin installed use its 'Search' feature.
如果您安装了 phpMyAdmin，请使用它的“搜索”功能。
- Select your DB
- Be sure you do have a DB selected (i.e. not a table, otherwise you'll get a completely different search dialog)
- Click 'Search' tab
- Choose the search term you want
- Choose the tables to search
- 选择您的数据库
- 确保你选择了一个数据库（即不是表，否则你会得到一个完全不同的搜索对话框）
- 单击“搜索”选项卡
- 选择您想要的搜索词
- 选择要搜索的表
I have used this on up to 250 table/10GB databases (on a fast server) and the response time is nothing short of amazing.
我已经在多达 250 个表/10GB 的数据库（在一个快速的服务器上）上使用了它，并且响应时间非常惊人。
回答by Olivier
PHP function:
PHP函数：
function searchAllDB($search){
global $mysqli;
$out = "";
$sql = "show tables";
$rs = $mysqli->query($sql);
if($rs->num_rows > 0){
while($r = $rs->fetch_array()){
$table = $r[0];
$out .= $table.";";
$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."%')";
}
$rs2->close();
}
$sql_search .= implode(" OR ", $sql_search_fields);
$rs3 = $mysqli->query($sql_search);
$out .= $rs3->num_rows."\n";
if($rs3->num_rows > 0){
$rs3->close();
}
}
$rs->close();
}
return $out;
}
回答by Olivier
You can use this project: http://code.google.com/p/anywhereindb
你可以使用这个项目：http: //code.google.com/p/anywhereindb
This will search all the data in all table.
这将搜索所有表中的所有数据。
回答by JayRizzo
If you are avoiding stored procedureslike the plague, or are unable to do a mysql_dumpdue to permissions, or running into other various reasons.
如果您stored procedures像瘟疫一样躲避，或mysql_dump由于权限而无法执行，或遇到其他各种原因。
I would suggest a three-step approach like this:
我建议采用这样的三步法：
1) Where this query builds a bunch of queries as a result set.
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%'
;
Results should look like this:
结果应如下所示：
2) You can then just Right Clickand use the Copy Row (tab-separated)
2）然后您可以只Right Click使用Copy Row (tab-separated)
3) Paste results in a new query window and run to your heart's content.
3) 将结果粘贴到一个新的查询窗口中并运行到您心中的内容。
Detail: I exclude system schema's that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemaschecked.
详细信息：我排除了您通常不会在工作台中看到的系统架构，除非您Show Metadata and Internal Schemas选中了该选项。
I did this to provide a quick way to ANALYZEan entire HOST or DB if needed or to run OPTIMIZEstatements to support performance improvements.
我这样做是为了ANALYZE在需要时为整个 HOST 或 DB提供一种快速的方法，或者运行OPTIMIZE语句以支持性能改进。
I'm sure there are differentways you may go about doing this but here's what works for me:
我敢肯定，您可以通过不同的方式来执行此操作，但以下是对我有用的方法：
-- ========================================== 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';
Tested On MySQL Version: 5.6.23
WARNING: DO NOT RUN THIS IF:
- You are concerned with causing Table-locks (keep an eye on your client-connections)
You are unsure about what you are doing.
You are trying to anger you DBA. (you may have people at your desk with the quickness.)
在 MySQL 版本上测试：5.6.23
警告：如果出现以下情况，请勿运行：
- 您担心导致表锁（注意您的客户端连接）
你不确定你在做什么。
您正试图激怒 DBA。（你的办公桌上可能有人很快。）
Cheers, Jay ;-]
干杯，杰伊;-]
回答by Alain Tiemblo
I also did my own mysql crawler to search some wordpress configuration, was unable to find it in both the interface and database, and database dumps were too heavy and unreadable. I must say I can't do without it now.
自己也做了mysql爬虫，搜索了一些wordpress的配置，界面和数据库都找不到，数据库转储太重，不可读。我必须说我现在离不开它。
It works like the one from @Olivier, but it manages exotic database / table names and is LIKE-joker safe.
它的工作原理与@Olivier 的类似，但它管理异国情调的数据库/表名，并且是 LIKE-joker 安全的。
<?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++;
}
}
Running this script could output something like:
运行此脚本可能会输出如下内容：
---------------------------------------------------
Table 'customers' contains 1 rows matching '*iemblo'.
---------------------------------------------------
Row #1: email_client: [email protected]
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: [email protected]
回答by Fred Christophe
Using MySQL Workbench it's easy to select several tables and run a search for text in all those tables of the DB ;-)
使用 MySQL Workbench 可以轻松选择多个表并在数据库的所有这些表中搜索文本；-)
回答by Fred Christophe
Here is my solution for this
这是我的解决方案
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;
回答by aji
This is the simplest query to retrive all Columns and Tables
这是检索所有列和表的最简单查询
SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = 'YOUR_DATABASE'
All the tables or those with specific string in name could be searched via Search tab in phpMyAdmin.
可以通过 phpMyAdmin 中的“搜索”选项卡搜索所有表或名称中带有特定字符串的表。
Have Nice Query... \^.^/
有很好的查询... \^.^/
