Table data and table structure copy statements in MySQL

Source: Internet
Author: User

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 ***************************
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.