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

发布于 2024-11-28 17:26:17 字数 399 浏览 0 评论 0原文

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

到目前为止我已经得到：

#!/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

原文

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?

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

野味少女 2024-12-05 17:26:17

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

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

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

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

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;

回复收藏 0 原文

凉城 2024-12-05 17:26:17

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

回复收藏 0 原文

岁吢 2024-12-05 17:26:17

我通过创建一个 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());
    }   
}
?>

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

$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());
    }   
}
?>

回复收藏 0 原文

~没有更多了~