在MySQL中,如何将一个表的内容复制到同一数据库中的另一个表?[英] In MySQL, how to copy the content of one table to another table within the same database?

本文是小编为大家收集整理的关于在MySQL中,如何将一个表的内容复制到同一数据库中的另一个表?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我是MySQL的新手.我想将一个表的内容复制到同一数据库中的另一个表.基本上,我想从另一个表中插入表格.有什么简单的方法吗?

推荐答案

如果表具有相同的结构:

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

如果表有不同的结构:

INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;

您还可以添加条件:

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

其他推荐答案

如果表不存在,则可以使用相同的模式创建一个类似的模式:

CREATE TABLE table2 LIKE table1;

然后,复制数据:

INSERT INTO table2 SELECT * FROM table1

其他推荐答案

如果Table1很大,并且您不想在复制过程的持续时间内锁定它,则可以进行转储和负载:

CREATE TABLE table2 LIKE table1;

SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;

本文地址:https://www.itbaoku.cn/post/597395.html

问题描述

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?

推荐答案

If the tables have the same structure:

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

If the tables have different structures:

INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;

You can also add conditions:

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

其他推荐答案

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

其他推荐答案

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:

CREATE TABLE table2 LIKE table1;

SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;
LOAD DATA INFILE '/tmp/table1.txt' INTO TABLE table2;