"Database" MySQL Replication table structure

Introduced

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 (Ten), KEY ix_name (name)) engine= ' MyISAM ', Charset=utf8, COMMENT ' a ';

Insert into Tb_base () 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 Old_tbl_name | (Like Old_tbl_name)}

1. Copying a table

CREATE  

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_nameselect ...   (Some valid Select or UNION statement)

1. Copying a table

CREATE  

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.

"Database" MySQL Replication table structure