我正在寻找一种将带有多个表的现有数据库复制到带有相同表和列的新数据库中的方法。到目前为止,一切都很好。如果我只是将数据库复制到具有相同数量的表和列的新数据库,则我正在这样做:
+---------+---------+---------+
| 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;
}
数据库的创建正在起作用(在我发布的代码中不可见(。我在新数据库内的新表中只没有复制的值。我在这里做错了什么?
您的最终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]."; ";
}