[mysql] MySQL 데이터베이스의 모든 테이블에서 모든 필드에서 검색

stored procedures전염병과 같은 것을 피 하거나 mysql_dump권한으로 인해 또는 다른 다양한 이유로 부딪 칠 수없는 경우 .

다음과 같은 3 단계 접근 방식을 제안합니다.

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) 그러면 바로 Right Click사용할 수 있습니다 .Copy Row (tab-separated)

3) 결과를 새 쿼리 창에 붙여넣고 마음의 내용으로 실행하십시오.

세부 사항 : 옵션을 Show Metadata and Internal Schemas선택 하지 않으면 일반적으로 워크 벤치에서 볼 수없는 시스템 스키마는 제외합니다 .

ANALYZE필요한 경우 전체 HOST 또는 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

경고 :이 경우 다음을 실행하지 마십시오.

1. 테이블 잠금을 유발하는 데 관심이 있습니다 (클라이언트 연결을 주시하십시오)

2. 현재하고있는 일이 확실하지 않습니다.

3. DBA를 화나게하려고합니다. ( 빨리 책상에 사람들이있을 수 있습니다 .)

건배, 제이;-]

답변

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