MySQL replication table structure, mysql replication Structure

MySQL replication table structure, mysql replication Structure
Introduction

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

 

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(1,'a'),(2,'b');

I. LIKE Method

The like method can replicate the results of a table to generate a new table, including the remarks, indexes, primary key foreign keys, and storage engine of the copied table.

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

1. Copy a table

CREATE  TABLE IF NOT EXISTS tb_base_like (LIKE tb_base); 

2. view the table

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

Ii. SELECT Method

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

1. Copy a table

CREATE  TABLE IF NOT EXISTS tb_base_select SELECT * FROM tb_base; 

2. view the table

Like method value copying field attribute, other primary key, index, table remarks, storage engine are not copied

Iii. Differences

Like method: The like method is actually a special method for copying table structures, but it only copies the structure and related properties of the table and does not copy data.

Select method: strictly speaking, the select method cannot be understood as a method for copying the table structure. In fact, it only executes a select query statement. Therefore, the copied result only contains select fields and data, other table attributes are determined by system configuration files, including storage engines and default character sets.

Summary

Therefore, the real table structure replication method is the LIKE method, if you do not need to consider the original attributes of a table, such as storage engine, remarks, primary keys, and indexes, the select COPY method is a good method and can be copied together with data.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion