如何将mysql表附加到另一个数据库中的另一个表

I would like to grab a table from one database and append this data to a table in another database. However, they have similar numbers (including the id) which need to be updated before they can be copied over. Is there a function available that could do this automatically? Or do I need to write a script in between?

我想从一个数据库中获取一个表，并将此数据附加到另一个数据库中的表。但是，它们具有相似的数字（包括id），需要在复制之前进行更新。是否有可以自动执行此操作的功能？或者我需要在两者之间编写脚本吗？

So far I've got:

到目前为止，我有：

#!/bin/sh
mysqldump -uuser1 -ppw1 database1 table1 > /home/user/public_html/database1.sql --skip-add-drop-table --skip-create-options
mysql -uuser2 -ppw2 database2 < /home/user/public_html/database1.sql
rm /home/user/public_html/database1.sql

3 个解决方案

#1

You could select from one table and insert it into another. The results will be "appended" to the original data.

您可以从一个表中选择并将其插入另一个表中。结果将“附加”到原始数据。

insert into new_table (id, name) select old_id, old_name from old_table;

To append a table from one database to a table from an other database

将表从一个数据库附加到另一个数据库中的表

insert into new_database.new_table (id, name) select old_id, old_name from old_database.old_table;

#2

Sounds like something that would be a lot safer to do via script, which seems simple enough - just grab the data from the first DB and perform batch inserts into the other, letting mysql handle the ids itself. This should take about 10-30 LOC in any descent scripting language, and gives you more control over the outcome.

听起来像通过脚本更安全的东西，这似乎很简单 - 只需从第一个数据库中获取数据并执行批量插入到另一个中，让mysql自己处理id。这应该在任何下降脚本语言中占用大约10-30 LOC，并使您可以更好地控制结果。

#3

I solved it by creating a php script that creates new connections for each new database. This script empties the main table first before it will append the data of the other tables. Having the first entry on NULL and having the $row[x] start on 1 makes sure it appends.. Don't know if it's the best solution, but it works.

我通过创建一个为每个新数据库创建新连接的php脚本来解决它。该脚本在添加其他表的数据之前先清空主表。让第一个条目为NULL并让$ row [x]从1开始确保它附加..不知道它是否是最佳解决方案，但它可以工作。

<?php

$db_user = "database_all_usr";
$db_pw = "";
$db_database = "database_all_db";

mysql_connect("localhost", $db_user, $db_pw) or die(mysql_error());
mysql_select_db($db_database) or die(mysql_error());

$sql = "TRUNCATE TABLE table_all";
mysql_query($sql) or die(mysql_error());

copy_table("database1_db","database1_usr","",$db_database,$db_user,$db_pw);
copy_table("database2_db","database2_usr","",$db_database,$db_user,$db_pw);

function copy_table($db_current,$db_user_current,$db_pw_current,$db_host,$db_user_host,$db_pw_host){

    mysql_connect("localhost", $db_user_current, $db_pw_current) or die(mysql_error());
    mysql_select_db($db_current) or die(mysql_error()); 

    $sql = "SELECT * FROM table";

    $result = mysql_query($sql) or die(mysql_error());

    mysql_connect("localhost", $db_user_host, $db_pw_host) or die(mysql_error());
    mysql_select_db($db_host) or die(mysql_error());

    while ($row = mysql_fetch_row($result)) {

        $sql = "INSERT INTO table_all VALUES (NULL, '$row[1]', '$row[2]')"; //adapt to the amount of columns
        mysql_query($sql) or die(mysql_error());
    }   
}
?>

#1