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

心已赠人 2022-11-26 07:47 192阅读 0赞

本文翻译自：[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数据库的每个表中搜索所有字段][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.* **此选项非常有用。**

[Search in all fields from every table of a MySQL database]: https://oldbug.net/q/2Gn1/Search-in-all-fields-from-every-table-of-a-MySQL-database
[https_stackoom.com_question_2Gn1_MySQL]: https://stackoom.com/question/2Gn1/%E5%9C%A8MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%AF%8F%E4%B8%AA%E8%A1%A8%E4%B8%AD%E6%90%9C%E7%B4%A2%E6%89%80%E6%9C%89%E5%AD%97%E6%AE%B5