How to replicate table structure in MySQL database?Copy table data content statement

How to copy the table structure in a MySQL database ? What statements are used to copy table data?

MySQLCopy table

If we need to completely copy a MySQL table, including its structure, indexes, default values, etc., it cannot be achieved simply by using the `CREATE TABLE … SELECT` command.

This chapter will introduce how to completely copy MySQL data tables, the steps are as follows:

• in UseSHOW CREATE TABLEcommand get create datatable (CREATE TABLE) statement, which contains the structure, index, etc. of the original data table.
• Copy the SQL statement displayed by the following command, modify the data table name, and execute the SQL statement. The above command will completely copy the data table structure.
• If you want to copy the content of the table, you can useINSERT INTO … SELECT statement to achieve.

Examples

Try the following example to replicate the table chenweiliang_tbl.

step one:

Get the complete structure of the data table.

mysql > SHOW CREATE TABLE chenweiliang_tbl \ G ; *************************** 1. row ******************** ******* Table ：chenweiliang_tbl
 Create Table ：CREATE TABLE`chenweiliang_tbl` （`chenweiliang_id` int （11 ）NOT NULL auto_increment ，`chenweiliang_title` varchar （100 ）NOT NULL default '' ，'chenweiliang_author` varchar （40 ）NOT NULL 默认'' ，
 
         
   
   
   
  `submission_date` 日期默认NULL ，
  PRIMARY KEY   （`chenweiliang_id` ），
  UNIQUE KEY `AUTHOR_INDEX` （`chenweiliang_author` ））ENGINE = InnoDB的1 行中集合（0.00 秒） 
 
  

错误：没有指定查询

Step two:

Modify the data table name of the SQL statement and execute the SQL statement.

mysql > CREATE TABLE`clone_tbl` （- > `chenweiliang_id` int （11 ）NOT NULL auto_increment ，- > `chenweiliang_title` varchar （100 ）NOT NULL default '' ，- > `chenweiliang_author` varchar （40 ）NOT NULL default '' ，- > `submission_date` 日期默认为NULL ，- > PRIMARY KEY   （`chenweiliang_id` ），- > UNIQUE KEY 
    
    
    
   
  
  `AUTHOR_INDEX` （`chenweiliang_author` ）- > ）ENGINE = InnoDB ; 查询OK ，0 行受影响（1.80 秒） 
 
 

Step three:

After completing the second step, you will create a new clone table named clone_tbl in the database. If you want to copy data from the table, you can use the INSERT INTO … SELECT statement.

mysql > INSERT INTO clone_tbl （chenweiliang_id ，- >                         chenweiliang_title ，- >                         chenweiliang_author ，- >                         submission_date ）- > SELECT chenweiliang_id ，chenweiliang_title ，- >         chenweiliang_author ，submission_date
     - > FROM chenweiliang_tbl ; 查询OK ，3 行受影响（0.07 秒）记录：3 重复：0
    
    
    
    
    
 
      警告：0 

After performing the above steps, you will have a complete copy of the table, including the table structure and table data.

Another way to copy a table

Another way to make a full copy of the table:

CREATE TABLE targetTable LIKE sourceTable ; 
INSERT INTO targetTable SELECT * FROM sourceTable ;

other:

You can copy some of these fields in a table:

CREATE TABLE newadmin AS
 （
    SELECT用户名，密码从管理员
 ）

You can rename the fields of the newly created table:

CREATE TABLE newadmin AS
 （  
    SELECT id ，username AS uname ，password AS pass FROM admin
 ）

Part of the data can be copied:

CREATE TABLE newadmin AS
 （
    SELECT * FROM admin WHERE LEFT （username ，1 ）= 's' ）  

Field information in the table can be defined while creating the table:

CREATE TABLE newadmin
 （
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
）
如
（
    SELECT * FROM admin
 ）