MySQL replication table structure and data instance code _mysql

In MySQL database development, we sometimes need to copy or copy a table structure and data to the exception of a table, this time we can use the create ... from statement to implement, this article introduces you to the MySQL replication table structure and data a simple example,

For example, now there is a table, we want to copy the table for future use, then how to use the MySQL statement to implement it? In fact, we can directly use the Create ... select ... from statement to implement, see the example below.

Let's first create a topic table and create the SQL statement for the topic table as follows:

Mysql> CREATE TABLE Topic (
  ->  topicid   SMALLINT not NULL auto_increment KEY,
  PRIMARY  Name    VARCHAR NOT NULL,
  ->  instock   SMALLINT UNSIGNED not NULL,
  ->   onorder SMALLINT UNSIGNED NOT NULL,
  ->  Reserved  SMALLINT UNSIGNED not NULL,
  -> Department  ENUM (' Classical ', ' popular ') Not NULL,
  ->  Category  VARCHAR (m) not NULL,
  ->  rowupdate  TIMESTAMP NOT NULL
  ->);

To insert data into the topic table:

Mysql> INSERT into Topic (Name, Instock, Onorder, Reserved, Department, Category) VALUES-> (' Java ' , 5, 3, ' popular ', ' Rock '),-> (' JavaScript ', ten, 5, 3, ' classical ', ' Opera '),- > (' C Sharp ', 4, 1, ' popular ', ' Jazz '),-> (' C ', 9, 4, 2, ' Classi    Cal ', ' Dance '),-> (' C + + ', 2, 5, ' classical ', ' General '),-> (' Perl ', 16,          6, 8, ' classical ', ' vocal '),-> (' Python ', 2, 6, ' popular ', ' Blues '),-> (' PHP ', 3, ' popular ', ' Jazz '),-> (' asp.net ', ', ', ' popular ', ' C Ountry '),-> (' vb.net ', 5,, ' popular ', ' New age '),-> (' vc.net ', 24, 1          1, ' popular ', ' New age '),-> (' UML ', @,, ' classical ', ' General '),-> (' Www.java2s.com ', 25, classical, ' Dance ', '-> ' (' Oracle ', +, ' classical ', ' general '),          ; (' Pl/sql ', M, 5, ' classical ', ' Opera '),-> (' sql Server ', #, 8, ' classical ', ' Ge
Neral ');
 Query OK, Rows Affected (0.00 sec) Records:16 duplicates:0 warnings:0

Now we are going to copy this form, as follows:

mysql> CREATE TABLE Topic2
  -> (
  ->  topicid SMALLINT not NULL auto_increment   KEY,
  - >  Name    VARCHAR (m) not NULL,
  ->  instock   SMALLINT UNSIGNED not NULL,
  ->  Onorder   SMALLINT UNSIGNED not NULL,
  -> Reserved SMALLINT UNSIGNED not  null,
  ->  Department ENUM (' Classical ', ' popular ') not NULL,
  ->  Category VARCHAR  (.) not NULL,
  ->  rowupdate  TIMESTAMP not NULL
  ->)
  -> SELECT *
  -> from Topic

So the table Topic2 and topic tables have the same table structure, and the table data is the same.

The exception is that if we only need to replicate the table structure, we do not need to replicate the data, or we can use create like to:

Create table a like users;

Thank you for reading this article, I hope to help you, thank you for your support for this site!