How to copy data from a data table to a new table in MySQL _ MySQL

This article mainly introduces how to copy data from a data table in MySQL to a new table. This article describes the existence of a new table and the absence of a new table, for more information, see if MySQL does not support SELECT... INTO syntax, use insert... SELECT replaces the same usage. here we will give a brief answer to the two situations where the new table exists and does not exist. different statements are used.
1. the new table does not exist.
Copy the table structure to the new table

create table new_tableselect * from old_talbe;

This method copies all content in the old_table. Note that the primary key, Extra, auto_increment and other attributes in the old_table are not in new_table. you must manually add them, for more information, see modify table (field attribute.
Only copy the table structure to the new table

# The first method is similar to the preceding method, except that the data record is null. that is, a false condition is given to create table new_tableselect * from old_table where 1 = 2; # Method 2 create table new_table like old_table;

2. the new table exists.
Copy the old table data to the new table (assuming the two tables have the same structure)

insert into new_tableselect * from old_table;

Copy the old table data to the new table (assuming the two tables have different structures)

insert into new_table(field1,field2,.....)select field1,field2,field3 from old_table;

Copy all data

select * into new_table from old_table;

Only copy the table structure to the new table

select * into new_talble from old_table where 1=2;

3. instance

(1) the table does not have a copy.

Mysql> show tables; + --------------- + | Tables_in_test1 | + ----------------- + | cpu_stat | test1 | test2 | test3 | + ----------------- + 4 rows in set (0.02 sec) mysql> create tabletest4 as select * from test1 where 1 = 0; // only copy the table structure QueryOK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create tabletest5 as select * from test1; // Copy all contents of table test1 to test5 QueryOK, 7 rows affected (0.11 sec) Records: 7 Duplicates: 0 Warnings: 0

(2) the table has been copied.

Mysql> create table test6 (id int not null auto_increment primary key, name varchar (20); Query OK, 0 rows affected (0.13 sec) mysql> insert into test6 (name) select name from test1; // only copy the name column Query OK, 7 rows affected (0.06 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from test6; + ---- + ------- + | id | name | + ---- + ------- + | 1 | wu | 2 | terry | 3 | tang | ...... 7 rows in set (0.00 sec)