PHP:将 MySQL 数据库表复制到具有附加列的新数据库表中

Question

我正在寻找一种方法，将具有多个表的现有数据库复制到具有相同表和列以及一些附加列的新数据库中。到目前为止，一切都很好。如果我只是将数据库复制到具有相同数量的表和列的新数据库，我会这样做:

+---------+---------+---------+
| TABLE 1 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

复制到:

+---------+---------+---------+
| TABLE 2 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

代码:

public function loadDB($db1,$db2){
    $this->db->prepare("use ".$db1."");
    $sqlshow = "SHOW TABLES ";
    $statement = $this->db->prepare($sqlshow);
    $statement->execute();
    $tables = $statement->fetchAll(PDO::FETCH_NUM);

    foreach($tables as $table){
        $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT * FROM ".$db1.".".$table[0]."; ";
    }
    $sqlState = implode(' ', $sql);
    $insertStatement = $this->db->exec($sqlState);
    return $insertStatement?$insertStatement:false;
}

此代码有效，我的数据库已成功复制，其中包含我的所有表和表中的值。我现在需要的是一个工作示例，说明我如何能够将数据库复制到一个新数据库，其中所有表都有四个额外的列，如下所示:

+---------+---------+---------+
| TABLE 1 |         |         |
+---------+---------+---------+
| Col1    | Col2    | Col3    |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

复制到:

+---------+---------+---------+-----------+-----------+-----------+-----------+
| TABLE 2 |         |         |           |           |           |           |
+---------+---------+---------+-----------+-----------+-----------+-----------+
| Col1    | Col2    | Col3    | Counter   | LoadDay   | User     | UserNew      |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
| Value 1 | Value 2 | Value 3 | NEW VALUE | NEW VALUE | NEW VALUE | NEW VALUE |
+---------+---------+---------+-----------+-----------+-----------+-----------+

代码(到目前为止我尝试过的):

public function loadDB($db1,$db2,$condition){
    $this->db->prepare("use ".$db1."");
    $sqlshow = "SHOW TABLES ";
    $statement = $this->db->prepare($sqlshow);
    $statement->execute();
    $tables = $statement->fetchAll(PDO::FETCH_NUM);

    foreach($tables as $table){
        $sqlshow2 = "SHOW COLUMNS FROM ".$table[0]." ";
        $statement = $this->db->prepare($sqlshow2);
        $statement->execute();
        $columns = $statement->fetchAll(PDO::FETCH_NUM);

        foreach($columns as $column){
            $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT ".$column[0]." FROM ".$db1.".".$table[0]."; ";
        }
        $sql[] .= "INSERT INTO ".$db2.".".$table[0]." (`Counter`, `LoadDay`, `User`, `UserNew`) VALUES ('1', '".date("Y-m-d H:i:s")."', '".$condition."', '".$condition."')";
    }
    $sqlState = implode(' ', $sql);
    var_dump($sqlState);
    $insertStatement = $this->db->exec($sqlState);
    return $insertStatement?$insertStatement:false;
}

数据库的创建工作正常(在我在这里发布的代码中不可见)。我只得到没有值复制到我的新数据库中的新表。我在这里做错了什么？

Answer 1

您最终的 SQL 查询出现错误。我建议将您的代码更改为如下所示:

foreach($tables as $table){
    $sqlshow2 = "SHOW COLUMNS FROM ".$table[0]." ";
    $statement = $this->db->prepare($sqlshow2);
    $statement->execute();
    $columns = $statement->fetchAll(PDO::FETCH_NUM);

    $sql[] = "INSERT INTO ".$db2.".".$table[0]." SELECT * , '1', '".date("Y-m-d H:i:s")."', '".$condition."', '".$condition."'" . " FROM ".$db1.".".$table[0]."; ";

}