Table data and table structure copy statements in MySQL

To summarize the methods of copying tables and knots first

One, CREATE TABLE method

Whole table copy: CREATE table new Table select * from old table;

Structure copy: 1, CREATE table new table select * from old table where 1<>1;

One, copy table structure

Method 1:

The code is as follows	Copy Code
Mysql> CREATE table a like users; Duplicate table structure Query OK, 0 rows affected (0.50 sec) Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | A | | Users | +----------------+ 2 rows in Set (0.00 sec)

Method 2:

code is as follows	copy code
mysql> CREATE TABLE B Select * from users limit 0;  //Replication table structure Query OK, 0 rows Affected (0.00 sec) Records:0&nb Sp duplicates:0  warnings:0 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | a              | | b              | | users          | +----------------+ 3 rows in Set (0.00 sec)

Method 3:

code is as follows

copy code

Mysql> Show CREATE TABLE usersg;         //Display the SQL ******************** of the creation table 1. Row ***************************  table:users Create table:create Table ' users ' (       //ALTER TABLE name   ' ID ' bigint (m) unsigned not NULL auto_increment,   ' user_name ' varchar (?) NOT NULL DEFAULT ',   ' user_pass ' varchar not NULL DEFAULT ',  primary KEY (' ID ') ) Engine=innodb auto_ Increment=5 DEFAULT charset=utf8 //change auto_increment 1 row in Set (0.00 sec)

Copy the SQL statement, change the table name and Atuo_increment, and then execute it.

Second, copy table data, and table structure

Method 1:

The code is as follows	Copy Code
mysql> CREATE TABLE C select * from users; Copy the SQL of the table Query OK, 4 rows Affected (0.00 sec) Records:4 duplicates:0 warnings:0

Method 2:

The code is as follows	Copy Code
mysql> CREATE TABLE D select User_name,user_pass from users where id=1; Query OK, 1 row Affected (0.00 sec) Records:1 duplicates:0 warnings:0

The above 2 methods are convenient, fast and flexible.

2, CREATE table new form like old table;

Ii. INSERT into method

Get the Build Table statement: Show create table old table;

To copy data to a new table:

1, copy the old table data to the new table (assuming two table structure)
INSERT into new table SELECT * from old table;
2, copy the old table data to the new table (assuming that two table structure is not the same)
INSERT into new Table (Field 1, Field 2,.......) SELECT field 1, Field 2,...... from the old table;

Create temporary table creating temporary tables