MySQL Replication Table Structure

Introduction

Sometimes we need to copy the table structure of a table intact to generate a new table, and MySQL provides two convenient methods.

Cases:

 create  table   Tb_base (id  int  not  null  primary  key  ,name  varchar  (10   key   Ix_name (name) ENGINE  =  Span style= "color: #ff0000;" > '  myisam   ' , Charset; 

Insert  into Values (1,'a'), (2,'b');

First, like method

The like method can replicate the results of a table in exactly the same way as a new table, including the notes, indexes, primary key foreign keys, storage engines, etc. of the copied table.

CREATE [temporary] TABLE [IF not EXISTS] tbl_name      like | ( like Old_tbl_name)}

1. Copying a table

CREATE  TABLE IF  not EXISTS tb_base_like ( like

2. View the table

You can see that the newly copied table is exactly the same as the original table.

Second, select method

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name SELECT ...   (SomeSelectorUnion statement)

1. Copying a table

CREATE  TABLE IF  not EXISTS SELECT *  from

2. View the table

Like method values copy field properties, other primary keys, indexes, table notes, storage engines are not replicated

Three, the difference

Like method: The Like method is actually a method that specifically replicates the table structure, but it replicates only the structure and related properties of the table and does not replicate the data

Select method: Strictly speaking, the Select method does not understand the method of copying table structure, in fact, it just executes a SELECT query statement, so the result of the copy contains only the fields and data of select, the other table properties have the configuration file of the system, including the storage engine, The default character set, and so on, is determined by the system's default configuration.

Summary

So the real table structure replication method is the like method, if you do not need to consider the table's original properties including the storage engine, notes, primary key, index, etc. then the Select Copy method is a good method and can also be copied with the data.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL Replication Table Structure