PHP/MySQL:将表和数据从一个数据库复制到另一个数据库 [英] PHP/MySQL: Copy Table and Data from one Database to another

问题描述

另一个问题:什么是从数据库中复制整个表而不使用类似方法的最佳方法是什么?

Anoter question: What is the best way to copy a whole table from one Database without using something like this:

CREATE TABLE DB2.USER SELECT * FROM DB1.USER;

这不起作用，因为我不能同时使用来自两个数据库的数据.所以我决定用php来制作.在那里，我不得不缓存所有数据，然后在另一个数据库中创建一个表.

This does not work because I can't use data from two Databases at the same time. So I decided to make this with php. There I had to cache all Data and then I'd create a table in the other DB.

但是现在-缓存数据的最快方法是什么?我想每张表每次几乎都少于1000条记录.

But now - what would be the fastest way to cache the data? I guess there are closely everytime less than 1000 records per table.

感谢您的输入

推荐答案

将其导出到.sql文件并导入到新数据库.

Export it to a .sql file and import to the new database.

在phpMyAdmin中，单击要导出的数据库，单击导出，选中另存为文件，然后单击 go .

In phpMyAdmin click the database you want to export, click export, check save as file, then go.

然后将其上传到要在其中复制数据的新数据库.

Then upload it to the new database that you want to duplicate the data in.

从严格的PHP角度讲，我可以想到的唯一方法是使用SHOW TABLES和describe {$table}返回记录的所有字段名称和结构，解析出来，创建您的create table语句，然后遍历每个表并创建insert语句.

In a strict PHP sense, the only way I could think to do it would to be use of SHOW TABLES and describe {$table} to return the all the field names and structures of the records, parse that out, create your create table statements, then loop through each table and create insert statements.

恐怕我能为您做的最好的就是一种 prototype 代码，我想这将是难以置信的服务器密集型代码，这就是为什么我建议您选择替代路线.

I'm afraid the best I can do for you is a sort of prototype code that I would imagine would be incredibly server intensive, which is why I recommend you go an alternate route.

类似的东西:

<?php

    // connect to first DB

    $tables = mysql_query("SHOW TABLES") or die(mysql_error());
    while ($row = mysql_fetch_assoc($tables)) {
        foreach($row as $value) {
            $aTables[] = $value;
        }
    }
    $i = 0;
    foreach ($aTables as $table) {
        $desc = mysql_query("describe " . $table);
        while ($row = mysql_fetch_assoc($desc)) {
            $aFields[$i][] = array($row["Field"],$row["Type"],$row["Null"],$row["Key"],$row["Default"],$row["Extra"]);
        }
        $i++;
    }

    // connect to second DB

    for ($i = 0; $i < count($aTables); $i++) {

        // Loop through tables, fields, and rows for create table/insert statements

        $query = 'CREATE TABLE IF NOT EXISTS {$aTables[$i]} (
                //loop through fields {

                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    {$aFields[$i][$j][0]} {$aFields[$i][$j][1]} {$aFields[$i][$j][2]} {$aFields[$i][$j][3]} {$aFields[$i][$j][4]} {$aFields[$i][$j][5]},
                    etc...
                }
            )';

            //loop through data

            $query .= 'INSERT INTO {$aTables[$i]} VALUES';
            $result = mysql_query("SELECT * FROM " . $aTables[$i]);
            while ($row = mysql_fetch_assoc($result)) {
                    $query .= '(';
                foreach ($aFields[$i][0] as $field) {

                    $query .= '"{$row[$field]}",';
                }
                $query .= '),';
            }
        mysql_query($query);
    }

?> 

这是基于此脚本的，它可能很方便参考.

This is based off of this script which may come in handy for reference.

希望这可以帮助您入门，但是我建议您寻找一种非PHP的替代方法.

Hopefully that's something to get you started, but I would suggest you look for a non PHP alternative.

这篇关于PHP/MySQL:将表和数据从一个数据库复制到另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！