paso - Buscar en todos los campos de cada tabla de una base de datos MySQL

crear base de datos mysql phpmyadmin

Question

Quiero buscar en todos los campos de todas las tablas de una base de datos MySQL una cadena dada, posiblemente usando la sintaxis como:

SELECT * FROM * WHERE * LIKE ''%stuff%''

¿Es posible hacer algo como esto?

Answer 1

Aquí está mi solución para esto

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;

Answer 2

Aunque esta pregunta es antigua, aquí se explica cómo puede hacerlo si está utilizando mysql workbench 6.3. (Lo más probable es que también funcione para otras versiones).

Haga clic derecho en su esquema y en "Buscar datos de la tabla", ingrese su valor y presione "Iniciar búsqueda". Eso es.

Answer 3

Construí una respuesta anterior y tengo esto, un poco de relleno adicional para poder unir cómodamente a todos los resultados:

SELECT 
CONCAT(''SELECT '''''',A.TABLE_NAME, ''-'' ,A.COLUMN_NAME,'''''' FROM '', A.TABLE_SCHEMA, ''.'', A.TABLE_NAME, 
       '' WHERE '', A.COLUMN_NAME, '' LIKE /'%Value%/' UNION'')
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''
UNION SELECT ''SELECT ''''''

-- for exact match use: A.COLUMN_NAME, '' LIKE /'Value/' instead

Primero ejecute esto, luego pegue y ejecute el resultado (sin edición) y mostrará todos los nombres y columnas de la tabla donde se usa el valor.

Answer 4

Dumping el archivo SQL fue probablemente el más rápido y más rápido para mí. También descubrí otro problema de todos modos ..

Answer 5

Esta es la consulta más sencilla para recuperar todas las columnas y tablas.

SELECT * FROM information_schema.`COLUMNS` C WHERE TABLE_SCHEMA = ''YOUR_DATABASE''

Todas las tablas o aquellas con una cadena específica en el nombre se pueden buscar a través de la pestaña Buscar en phpMyAdmin.

Tener buena consulta ... / ^. ^ /

Answer 6

Esta es la forma simple en que lo sé. Seleccione su base de datos en PHPMyAdmin, vaya a la pestaña "Buscar" y escriba lo que quiere encontrar y dónde buscará. Seleccione todas las tablas si va a buscar las palabras de todas las tablas. Luego "GO" y mira el resultado.

Answer 7

Esta solución
a) es solo MySQL, no se necesita otro idioma, y
b) devuelve resultados de SQL, listos para procesar!

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

Answer 8

Exportar toda la base de datos y buscar en el archivo .sql.

Answer 9

Hay una bonita biblioteca para leer todas las mesas, ridona

$database = new ridona/Database(''mysql:dbname=database_name;host=127.0.0.1'', ''db_user'',''db_pass'');

foreach ($database->tables()->by_entire() as $row) {

....do

}

Answer 10

He usado HeidiSQL es una herramienta útil y confiable diseñada para desarrolladores web que utilizan el popular servidor MySQL.

En HeidiSQL puede presionar shift + ctrl + f y puede encontrar texto en el servidor en todas las tablas. Esta opción es muy útil.

Answer 11

Modifiqué un poco la respuesta PHP de Olivier para:

• imprimir los resultados en los que se encontró la cadena
• omitir tablas sin resultados
• también muestra salida si los nombres de columna coinciden con la entrada de búsqueda

• muestra el número total de resultados

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

Answer 12

No sé si esto es solo en las versiones recientes, pero al hacer clic con el botón derecho en la opción Tables en el panel del Navigator aparece una opción llamada Search Table Data . Esto abre un cuadro de búsqueda donde se completa la cadena de búsqueda y se pulsa buscar.

Debe seleccionar la tabla en la que desea buscar en el panel izquierdo. Pero si mantiene presionada la tecla shift y selecciona como 10 tablas a la vez, MySql puede manejar eso y devolver los resultados en segundos.

Para quien busca mejores opciones! :)

Answer 13

Podría hacer un SQLDump de la base de datos (y sus datos) y luego buscar ese archivo.

Answer 14

Puede usar este proyecto: http://code.google.com/p/anywhereindb

Esto buscará todos los datos en toda la tabla.

Answer 15

Puedes echar un vistazo en el esquema de esquema de information_schema . Tiene una lista de todas las tablas y todos los campos que están en una tabla. A continuación, puede ejecutar consultas utilizando la información que ha obtenido de esta tabla.

Las tablas involucradas son SCHEMATA, TABLES y COLUMNAS. Hay claves externas que pueden construir exactamente cómo se crean las tablas en un esquema.

Answer 16

Si está evitando stored procedures como la plaga, o no puede hacer un mysql_dump debido a los permisos, o si se encuentra con otras razones.

Sugeriría un enfoque de tres pasos como este:

1) Cuando esta consulta genera un conjunto de consultas como un conjunto de resultados.

# =================
# 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%''
;

Los resultados deben verse así:

2) Puede hacer Right Click el Copy Row (tab separated) Right Click y usar la Copy Row (tab separated)

3) Pegue los resultados en una nueva ventana de consulta y ejecute el contenido de su corazón.

Detalle: excluyo los esquemas del sistema que normalmente no verá en su entorno de trabajo a menos que tenga activada la opción Show Metadata and Internal Schemas .

Hice esto para proporcionar una forma rápida de ANALYZE un HOST o DB completo, si es necesario, o para ejecutar sentencias OPTIMIZE para admitir mejoras de rendimiento.

Estoy seguro de que hay diferentes maneras en que puedes hacer esto, pero esto es lo que funciona para mí:

-- ========================================== 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'';

Probado en la versión de MySQL: 5.6.23

ADVERTENCIA: NO CORRE ESTO SI

1. Está preocupado por causar bloqueos de tablas (vigile las conexiones de sus clientes)

2. No estás seguro de lo que estás haciendo.

3. Estás tratando de enojarte, DBA. (Puede que haya gente en su escritorio con la rapidez .)

Saludos, Jay; -]

Answer 17

Si está usando phpMyAdmin siga esta respuesta , esa página hace referencia .

Answer 18

Si tiene instalado phpMyAdmin, use la función ''Buscar''.

• Seleccione su DB
• Asegúrese de tener una base de datos seleccionada (es decir, no una tabla, de lo contrario obtendrá un cuadro de diálogo de búsqueda completamente diferente)
• Haga clic en la pestaña ''Buscar''
• Elija el término de búsqueda que desee
• Elija las tablas para buscar

He usado esto en hasta 250 bases de datos de mesa / 10 GB (en un servidor rápido) y el tiempo de respuesta es sorprendente.

Answer 19

También hice mi propio rastreador mysql para buscar alguna configuración de wordpress, no pude encontrarla ni en la interfaz ni en la base de datos, y los volcados de base de datos eran demasiado pesados ​​e ilegibles. Debo decir que no puedo prescindir de eso ahora.

Funciona como la de @Olivier, pero administra nombres de tablas / bases de datos exóticos y es seguro como 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++;
    }
}

Ejecutar este script podría generar algo como:

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

Answer 20

Tengo esto para trabajar. solo necesitas cambiar las variables

$query ="SELECT `column_name` FROM `information_schema`.`columns` WHERE `table_schema`=''" . $_SESSION[''db''] . "'' AND `table_name`=''" . $table . "'' ";
$stmt = $dbh->prepare($query);
$stmt->execute(); 
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);       

$query="SELECT name FROM `" . $database . "`.`" . $table . "` WHERE ( ";
foreach ( $columns as $column ) {
    $query .=" CONVERT( `" . $column[''column_name''] . "` USING utf8 ) LIKE ''%" . $search . "%'' OR ";
}
$query = substr($query, 0, -3);
$query .= ")";

echo $query . "<br>";
$stmt=$dbh->prepare($query);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<pre>";
print_r ($results );
echo "</pre>";

Answer 21

Usando MySQL Workbench es fácil seleccionar varias tablas y ejecutar una búsqueda de texto en todas esas tablas de la base de datos ;-)

Answer 22

Usted podría usar

SHOW TABLES;

Luego obtenga las columnas en esas tablas (en un bucle) con

SHOW COLUMNS FROM table;

y luego con esa información, crea muchas consultas que también puedes UNION si necesitas.

Pero esto es extremadamente pesado en la base de datos. Especialmente si estás haciendo una búsqueda de LIKE.

Answer 23

Utilicé Union para encadenar consultas. No sé si es la forma más eficiente, pero funciona.

SELECT * FROM table1 WHERE name LIKE ''%Bob%'' Union
SELCET * FROM table2 WHERE name LIKE ''%Bob%'';

Answer 24

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