dump.php

<?php

$queries = [];
while (true) {
    $query = fgets(STDIN);
    if (empty($query)) {
        break;
    }
    $queries []= $query;
}

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=database1';
$user = 'user';
$password = 'password';

$dbh = new PDO($dsn, $user, $password);

foreach($queries as $query) {
    if (empty($query)) {
        continue;
    }
    $stmt = $dbh->prepare($query);
    $stmt->setFetchMode(PDO::FETCH_NUM);
    $stmt->execute();
    
    $cols = $stmt->columnCount();
    
    $tables = [];
    $columns = [];
    for ($i = 0;$i < $cols;$i++) {
        $col = $stmt->getColumnMeta($i);
        $table = $col["table"];
        if (!in_array($table , $tables)) {
            $tables []= $table;
        }
        $columns []= $col["name"];
    }
    $result = $stmt->fetchAll();
    
    echo "\xEF\xBB\xBF";
    echo join("|", $tables )."\r\n";
    echo ",".join(",", $columns )."\r\n";
    
    foreach($result as $row) {
        echo ",\"".join("\",\"", $row)."\"\r\n";
    }
    echo "\r\n";
}

example.sh

cat << EOL | php dump.php > dump.csv
SELECT * FROM tb1 limit 1;
SELECT id,name FROM tb1 limit 1;
EOL

to_query.php

<?php

$queries = [];
while (true) {
    $query = fgets(STDIN);
    if (empty($query)) {
        break;
    }
    $queries []= $query;
}

$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=database1';
$user = 'user';
$password = 'password';

$dbh = new PDO($dsn, $user, $password);

foreach($queries as $query) {
    if (empty($query)) {
        continue;
    }
    $stmt = $dbh->prepare($query);
    $stmt->setFetchMode(PDO::FETCH_NUM);
    $stmt->execute();
    
    $cols = $stmt->columnCount();
    
    $tables = [];
    $columns = [];
    for ($i = 0;$i < $cols;$i++) {
        $col = $stmt->getColumnMeta($i);
        $table = $col["table"];
        if (!in_array($table , $tables)) {
            $tables []= $table;
        }
        $columns []= $col["name"];
    }
    $result = $stmt->fetchAll();
    
    $insert = "INSERT INTO ".$tables[0]." (".join(",", $columns)." ) VALUES ";

    foreach($result as $row) {
        echo $insert."('" .join("','", $row)."');".PHP_EOL;
    }
    echo PHP_EOL;
}