MySQL 从一个数据库插入另一个数据库

2021-11-20 00:00:00 sql database mysql

我需要将数据从一个数据库迁移到另一个数据库，两者都在同一个本地系统上.

I need to migrate data from one Database to another one, both are on the same local system.

表和列的名称不同，我不能迁移旧数据库中的所有列，所以

The tables and columns got different names and I mustn't migrate all the Columns from the old Database, so

Select * 对我不起作用.

INSERT INTO newDatabase.table1(Column1, Column2); SELECT oldDatabase.table1(column1, column2) FROM oldDatabase.table1

但我得到的只是一个#1064 - 语法错误

我的查询中有什么错误，我该如何解决?

What is the error in my Query and How can i fix this ?

提前致谢

推荐答案

你的查询应该是这样的:

Your query should go like this:

INSERT INTO newDatabase.table1 (Column1, Column2) SELECT column1, column2 FROM oldDatabase.table1;

更新

由于这个答案受到的关注比我预期的还要多，我应该扩展这个答案.首先，从答案本身可能并不明显，但列不需要具有相同的名称.因此，以下也将起作用(假设列存在于各自的表中):

Since this answer is getting more attention than I even anticipated, I should expand on this answer. First of all, it might not be obvious from the answer itself, but the columns do not need to have the same name. So, following will work too (assuming that the columns exist in their respective tables):

INSERT INTO newDatabase.table1 (Column1, Column2) SELECT SomeOtherColumn, MoreColumns FROM oldDatabase.table1;

此外，它们甚至不需要是表中的真正列.我经常使用的转换数据的示例之一是:

Furthermore, they don't even need to be real columns in the table. One of the examples for transforming data that I use quite often is:

INSERT INTO newDatabase.users (name, city, email, username, added_by) SELECT CONCAT(first_name, ' ', last_name), 'Asgard', CONCAT(first_name,'@gmail.com'), CONCAT(first_name,last_name), 'Damir' FROM oldDatabase.old_users;

因此，现在可能更明显了，规则是，只要 SELECT 查询返回与 INSERT 查询所需的列数相同的列数，就可以使用它来代替 VALUES.

So, as it might be more obvious now, the rule is, as long as the SELECT query returns same number of columns that INSERT query needs, it can be used in place of VALUES.

相关文章