Category:Mysql
Article From:https://www.cnblogs.com/xiaoliwang/p/9124306.html

Sometimes we need to copy the structure of a table to create a new table. MYSQL provides two convenient ways.

One, LIKE method

likeThe method can copy the result of a table exactly and generate a new table, including copy the table’s remarks, index, primary key foreign key, storage engine and so on.

grammar 

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

For example:

CREATE  TABLE IF NOT EXISTS `news_dynamic` (LIKE `news_bulletin`);

Two, SELECT method

grammar

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
SELECT ...   (Some valid select or union statement)

for example

CREATE  TABLE IF NOT EXISTS tb_base_select SELECT * FROM tb_base; 

Three. Difference

likeMethod: the like method is actually a way to copy the table structure, but it only copies the structure and related attributes of the table and does not copy data.

selectMethods: strictly speaking, the select method cannot be understood as a copy of the table structure. In fact, it only performs a select query, so the result of the replication contains only the fields and data of the select, and the other table properties have a system configuration file, including the storage engine, the default.Character sets are all determined by a systematic default configuration.

Link of this Article: Mysql replication table structure

Leave a Reply

Your email address will not be published. Required fields are marked *