Shulou(Shulou.com)06/01 Report--

Introduction

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

Example:

CREATE TABLE tb_base (id INT NOT NULL PRIMARY KEY,name VARCHAR (10), KEY ix_name (name)) ENGINE='MyISAM',CHARSET=utf8,COMMENT 'a'; insert into tb_base () values (1meme Za'), (2Jing Zhe b')

1. LIKE method

The like method can exactly copy the results of a table into a new table, including the comments of the replicated table, index, primary key foreign key, storage engine, etc.

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

1. Copy tabl

CREATE TABLE IF NOT EXISTS tb_base_like (LIKE tb_base)

two。 View tabl

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

2. SELECT method

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

1. Copy tabl

CREATE TABLE IF NOT EXISTS tb_base_select SELECT * FROM tb_base

two。 View tabl

The method value of like copies the field properties, but not other primary keys, indexes, table comments, and storage engines.

III. Differences

Like method: the like method is actually a method for copying table structures, but it only replicates the table structure and related properties, not data.

Select method: strictly speaking, select method can not be understood as the method of copying table structure, in fact, it only executes a select query statement, so the copied result only contains the fields and data of select, and other table attributes are determined by the systematic configuration file; including storage engine, default character set and so on are determined by systematic default configuration.

Summary

So the real table structure replication method is the LIKE method, if you do not need to consider the original properties of the table, including storage engine, comments, primary key, index and so on, then select replication method is a good method and can also be replicated with data.

Introduction

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

Example:

CREATE TABLE tb_base (id INT NOT NULL PRIMARY KEY,name VARCHAR (10), KEY ix_name (name)) ENGINE='MyISAM',CHARSET=utf8,COMMENT 'a'; insert into tb_base () values (1meme Za'), (2Jing Zhe b')

1. LIKE method

The like method can exactly copy the results of a table into a new table, including the comments of the replicated table, index, primary key foreign key, storage engine, etc.

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

1. Copy tabl

CREATE TABLE IF NOT EXISTS tb_base_like (LIKE tb_base)

two。 View tabl

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

2. SELECT method

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

1. Copy tabl

CREATE TABLE IF NOT EXISTS tb_base_select SELECT * FROM tb_base

two。 View tabl

The method value of like copies the field properties, but not other primary keys, indexes, table comments, and storage engines.

III. Differences

Like method: the like method is actually a method for copying table structures, but it only replicates the table structure and related properties, not data.

Select method: strictly speaking, select method can not be understood as the method of copying table structure, in fact, it only executes a select query statement, so the copied result only contains the fields and data of select, and other table attributes are determined by the systematic configuration file; including storage engine, default character set and so on are determined by systematic default configuration.

Summary

So the real table structure replication method is the LIKE method, if you do not need to consider the original properties of the table, including storage engine, comments, primary key, index and so on, then select replication method is a good method and can also be replicated with data.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.