MySQL 在MySQL中，如何将一张表的内容复制到同一个数据库内的另一张表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2343644/
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
In MySQL, how to copy the content of one table to another table within the same database?
提问by Joneph O.
I am new to MySQL. I would like to copy the content of one table to another table within the same database. Basically, I would like to insert to a table from another table. Is there easy way of doing this?
我是 MySQL 的新手。我想将一个表的内容复制到同一个数据库中的另一个表。基本上，我想从另一个表插入到一个表中。有没有简单的方法来做到这一点？
回答by ggiroux
INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;
INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;
EDIT: or if the tables have different structures you can also:
编辑：或者如果表具有不同的结构，您还可以：
INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;
EDIT: to constrain this..
编辑：限制这个..
INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1
回答by GSto
If the table doesn't exist, you can create one with the same schema like so:
如果该表不存在，您可以创建一个具有相同架构的表，如下所示：
CREATE TABLE table2 LIKE table1;
Then, to copy the data over:
然后，复制数据：
INSERT INTO table2 SELECT * FROM table1
回答by Ike Walker
If table1 is large and you don't want to lock it for the duration of the copy process, you can do a dump-and-load instead:
如果 table1 很大并且您不想在复制过程中锁定它，则可以改为执行转储和加载：
CREATE TABLE table2 LIKE table1;
SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;
回答by Jason
This worked for me,
这对我有用，
CREATE TABLE newtable LIKE oldtable;
CREATE TABLE newtable LIKE oldtable;
Replicates newtable with old table
用旧表复制新表
INSERT newtable SELECT * FROM oldtable;
INSERT newtable SELECT * FROM oldtable;
Copies all the row data to new table.
将所有行数据复制到新表。
Thank you
谢谢
回答by Frank Heikens
If you want to create and copy the content in a single shot, just use the SELECT:
如果您想一次性创建和复制内容，只需使用 SELECT：
CREATE TABLE new_tbl SELECT * FROM orig_tbl;
创建表 new_tbl SELECT * FROM orig_tbl;
回答by Sandor Fekete
This worked for me. You can make the SELECT statement more complex, with WHERE and LIMIT clauses.
这对我有用。您可以使用 WHERE 和 LIMIT 子句使 SELECT 语句更复杂。
First duplicate your large table (without the data), run the following query, and then truncate the larger table.
首先复制大表（没有数据），运行以下查询，然后截断大表。
INSERT INTO table_small (SELECT * FROM table_large WHERE column = 'value' LIMIT 100)
Super simple. :-)
超级简单。:-)
回答by RAHUL KUMAR
Try this. Works well in my Oracle 10g,
尝试这个。在我的 Oracle 10g 中运行良好，
CREATE TABLE new_table
AS (SELECT * FROM old_table);
