如何在不同数据库中的2个表/不同列之间移动数据

We have the following scenario where foo has been renamed as foo1.

我们有以下场景,其中foo已重命名为foo1。

foo.col1 has been renamed as foo1.col11.

foo.col1已重命名为foo1.col11。

foo.col2 has been removed

foo.col2已被删除

In fact these used to be similar tables and I would like to copy data from A to B for these tables. How would I go about doing a simple migration given that the table/column names have undergone a change.

事实上,这些表曾经是类似的表,我想将这些表的数据从A复制到B.考虑到表/列名称已经发生了变化,我将如何进行简单的迁移。

Database 'A'
create table foo {id pk, col1 varchar(255), col2 tinyint(1), col3 datetime);
create table foo_bar1 (id pk, foo_id fk, col4 datetime, col5 varchar(255));

Database 'B'
create table foo1 {id pk, col11 varchar(255), col3 datetime);
create table foo1_bar1 (id pk, foo1_id fk, col4 datetime, col5 varchar(255));

4 个解决方案

#1

you should be able to do:

你应该能够做到:

INSERT INTO B.foo1 (id, col11, col3) 
 SELECT id,col1,col3 FROM A.foo;

INSERT INTO B.foo1_bar1 (id, foo1_id, col4, col5) 
 SELECT id,foo_id,col4,col5 FROM A.foo_bar1;

#2

BEGIN;
INSERT INTO B.foo1 (id, col11, col3) 
   SELECT id, col1, col3 
   FROM A.foo;
INSERT INTO B.foo1_bar1 (id, foo1_id, col4, col5) 
   SELECT id, foo_id, col4, col5 
   FROM A.foo_bar1;
COMMIT;

#3

IF you know the columns that have been removed you can do a straight insert of query results:

如果您知道已删除的列,则可以直接插入查询结果:

INSERT INTO B.foo1 (id, col11, col3) (SELECT id, col1, col3 FROM A.foo);
INSERT INTO B.foo_bar1 (id, foo1_id, col4, col5) (SELECT id, foo_id, col4, col5 FROM A.foo_bar1);

#4

another solution that includes your 2nd question to set the source database name dynamically

另一个解决方案,包括您动态设置源数据库名称的第二个问题

create your script 'bla.sql':

创建脚本'bla.sql':

SET @q = CONCAT('INSERT INTO B.foo1 (id, col11, col3) 
  SELECT id,col1,col3 FROM ',@sourcedb,'.foo');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

(add additional statements as needed)

(根据需要添加其他声明)

then run from console:

然后从控制台运行:

mysql -e "SET @sourcedb:='A' ; source bla.sql;" B -u root

source for this solution: variable database name and koneraks comment ;-)

此解决方案的来源:变量数据库名称和koneraks评论;-)

#1