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

MySQL databaseHow to copy table structure in ?Copy table data content statement

MySQLCopy table

If we need to completely replicate the MySQL data table, including the structure of the table, indexes, default values, etc.If only useCREATE TABLE ... SELECTcommand is impossible.

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

• 使用SHOW 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 performing the second step, you will create a new clone table clone_tbl in the database.If you want to copy the data from the data table you can useINSERT INTO … SELECT statement to achieve.

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
 ）