MySQL 从一个数据库插入另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22912167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Insert Into from one Database in another
提问by KhorneHoly
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 *doesn't work for me.
Select *对我不起作用。
INSERT INTO newDatabase.table1(Column1, Column2);
SELECT oldDatabase.table1(column1, column2) FROM oldDatabase.table1
but all i got is a #1064 - Syntax Error
但我得到的只是一个 #1064 - Syntax Error
What is the error in my Query and How can i fix this ?
我的查询中有什么错误，我该如何解决？
Thanks in advance
提前致谢
回答by dkasipovic
Your query should go like this:
你的查询应该是这样的：
INSERT INTO newDatabase.table1 (Column1, Column2)
SELECT column1, column2 FROM oldDatabase.table1;
UPDATE
更新
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;
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.
因此，现在可能更明显了，规则是，只要 SELECT 查询返回与 INSERT 查询所需的列数相同的列，就可以使用它来代替 VALUES。
回答by Priyank Kotiyal
INSERT INTO db1.table SELECT * FROM db2.table;
If you want to copy data to same tables of different db.
如果要将数据复制到不同数据库的相同表。
回答by Racil Hilan
You said "The tables and columns got different names", but you still used the same names. Try this:
您说“表和列的名称不同”，但您仍然使用相同的名称。尝试这个：
INSERT INTO newDatabase.newtable1 (newColumn1, newColumn2)
SELECT oldcolumn1, oldcolumn2 FROM oldDatabase.oldtable1;
回答by Mayuri
CREATE TABLE db2.table LIKE db1.table;
INSERT INTO db2.table
SELECT column_name FROM db1.table
