在MySQL数据库的每个表中搜索所有字段

本文翻译自：Search in all fields from every table of a MySQL database

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? 有可能做这样的事情吗？

---

#1楼

参考：https://stackoom.com/question/2Gn1/在MySQL数据库的每个表中搜索所有字段

---

#2楼

This solution 这个解决方案
a) is only MySQL, no other language needed, and a）仅MySQL，不需要其他语言，并且
b) returns SQL results, ready for processing! b）返回SQL结果，准备处理！

#Search multiple database tables and/or columns
#Version 0.1 - JK 2014-01
#USAGE: 1. set the search term @search, 2. set the scope by adapting the WHERE clause of the `information_schema`.`columns` query
#NOTE: This is a usage example and might be advanced by setting the scope through a variable, putting it all in a function, and so on...

#define the search term here (using rules for the LIKE command, e.g % as a wildcard)
SET @search = '%needle%';

#settings
SET SESSION group_concat_max_len := @@max_allowed_packet;

#ini variable
SET @sql = NULL;

#query for prepared statement
SELECT
    GROUP_CONCAT("SELECT '",`TABLE_NAME`,"' AS `table`, '",`COLUMN_NAME`,"' AS `column`, `",`COLUMN_NAME`,"` AS `value` FROM `",TABLE_NAME,"` WHERE `",COLUMN_NAME,"` LIKE '",@search,"'" SEPARATOR "\nUNION\n") AS col
INTO @sql
FROM `information_schema`.`columns`
WHERE TABLE_NAME IN
(
    SELECT TABLE_NAME FROM `information_schema`.`columns`
    WHERE
        TABLE_SCHEMA IN ("my_database")
        && TABLE_NAME IN ("my_table1", "my_table2") || TABLE_NAME LIKE "my_prefix_%"
);

#prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

---

#3楼

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. 我还用自己的mysql搜寻器搜索了一些wordpress配置，无法在界面和数据库中都找到它，并且数据库转储太重且不可读。 I must say I can't do without it now. 我必须说，现在我不能没有它。

It works like the one from @Olivier, but it manages exotic database / table names and is LIKE-joker safe. 它的工作方式与@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++;
    }
}

Running this script could output something like: 运行此脚本可能会输出以下内容：

---------------------------------------------------
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

---

#4楼

使用MySQL Workbench可以很容易地选择几个表并在数据库的所有这些表中搜索文本;-)

---

#5楼

I modified the PHP answer of Olivier a bit to: 我将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; } 

---

#6楼

I am use HeidiSQL is a useful and reliable tool designed for web developers using the popular MySQL server. 我正在使用HeidiSQL是有用且可靠的工具，专为使用流行的MySQL服务器的Web开发人员设计。

In HeidiSQL you can push shift + ctrl + f and you can find text on the server in all tables. 在HeidiSQL中，您可以按shift + ctrl + f，并且可以在服务器上的所有表中找到文本。 This option is very usefully. 此选项非常有用。