php txt转sql, mysql命令行查出来的结果转化为sql语句 类似navicat copy rows as Insert SQL功能

recharge表查出来的结果如下:

+-------+-----+-------+----------------------------+------------------+--------+------+--------+---------------------+---------------------+-------+
| id    | uid | money | type                       | orderid          | status | warn | reason | time                | time2               | jifen |
+-------+-----+-------+----------------------------+------------------+--------+------+--------+---------------------+---------------------+-------+
| 11156 | 141 | 100   | 微信在线扫吗支付（吗支付） | PAY1569230069247 |      1 |    0 | 无     | 2019-09-23 17:14:29 | 2019-09-23 17:15:34 |   100 |
| 11701 | 141 | 134   | 系统充值                   | PAY1569395833289 |      1 |    0 | 无     | 2019-09-25 15:17:13 | 0000-00-00 00:00:00 |     0 |
+-------+-----+-------+----------------------------+------------------+--------+------+--------+---------------------+---------------------+-------+

保存为recharge.txt

保留首行和尾行的 +--- 开头的

* index.php

<?php

function fileForEachRow($path, $handler) {
    $handle = fopen($path, "r");
    if (! $handle ) {
        return;
    }
    $linum = 0;
    while (($buffer = fgets($handle, 1024) ) !== false) {
        $buffer = trim($buffer);
        call_user_func($handler, $buffer, $linum);
        $linum += 1;
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}

function mystrpos($s, $delim) {
    $n = strlen($delim);
    $j = 0;
    for ($i = 0; isset($s[$i]) && $j < $n; $i++) {
        if ($s[$i] === $delim[$j]) {
            $j++;
        } else {
            $j = 0;
        }
    }
    if ($j === $n) {
        return $i - $n;
    }
    return -1;
}

// $table = "user";

if ($argc < 2) {
    printf("Usage: %s [tableName]\n", $argv[0]);
    printf("[tableName].txt\n");
    $example = <<<EOF
+----+-----+--------+------------+-----------+
| id | uid | paypal | time       | dayyuebao |
+----+-----+--------+------------+-----------+
| 15 | 141 | 71     | 1569357774 | NULL      |
+----+-----+--------+------------+-----------+
EOF;
    echo $example.PHP_EOL;
    exit(0);
}

$table = $argv[1];
$columns = [];
fileForEachRow($table.".txt", function($line, $i) use (&$columns, $table) {
    //    echo $i.PHP_EOL;
    if (mystrpos($line, "+--") >= 0) {
        return;
    }
    $a = explode('|', $line);
    $a = array_filter($a, function($s) {
        return isset($s[0]);
    });
    $a = array_map(function($s) {
        return trim($s);
    }, $a);
    // set column
    if (empty($columns)) {
        foreach ($a as $it) {
            array_push($columns, $it);
        }
        return;
    }
    // set rows
    $v = array_map(function($s) {
        if (strcmp($s, "NULL") === 0) {
            return "NULL";
        } else if (is_numeric($s)) {
            return $s;
        } else {
            return "'".addslashes($s)."'";
        }
    }, $a);
    $values = implode(',', $v);
    
    $s = sprintf("INSERT INTO `%s`(`%s`) VALUES(%s);\n",
                 $table, implode('`,`', $columns), $values);
    echo $s;
});

用法:

$ php index.php recharge

INSERT INTO `recharge`(`id`,`uid`,`money`,`type`,`orderid`,`status`,`warn`,`reason`,`time`,`time2`,`jifen`) VALUES(11156,141,100,'微信在线扫吗支付（吗支付）','PAY1569230069247',1,0,'无','2019-09-23 17:14:29','2019-09-23 17:15:34',100);

INSERT INTO `recharge`(`id`,`uid`,`money`,`type`,`orderid`,`status`,`warn`,`reason`,`time`,`time2`,`jifen`) VALUES(11701,141,134,'系统充值','PAY1569395833289',1,0,'无','2019-09-25 15:17:13','0000-00-00 00:00:00',0);