Three ways to copy MySQL tables (summary)

Three ways to copy MySQL tables (summary)

Copy table structure and its data

The following statement will copy the data into a new table.

Note: This statement actually just creates a table with the results of the select statement, so the new table will not have a primary key or index.

create table table_name_new as (select * from table_name_old);

Copy only the table structure

create table table_name_new as select * from table_name_old where 1=2;

or

create table table_name_new like table_name_old;

Note: The former method will not copy the primary key type and index, while the latter method will copy all field types of the old table to the new table.

Copy only table data

If the two tables have the same structure

insert into table_name_new select * from table_name_old;

If the two tables have different structures

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old;

Note: Many articles say that data can be copied using the following statement. The table_name_new table does not need to exist and will be automatically created during execution. In fact, the SELECT ... INTO form stores the query results in a variable or writes them to a file, that is, table_name_new is a variable or a file.

select column1,column2,.... into table_name_new from table_name_old;

This concludes this article on three ways to copy MySQL tables (summary). For more information about copying MySQL tables, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Three implementation methods of Mysql copy table and grant analysis
  • Mysql method to copy a column of data in one table to a column in another table
  • Copy fields between different tables in MySQL
  • How to use worm replication in Mysql data table
  • MySQL replication table details and example code
  • How to copy MySQL query results to a new table (update, insert)
  • mysql copy table structure and data example code
  • Tutorial on copying data from a table to a new table in MySQL
  • Tutorial on table replication and large data table backup in MySQL
  • Mysql method of copying table structure and table data
  • How to copy MySQL table

<<:  Summary of 28 common JavaScript string methods and usage tips

>>:  Working principle and example analysis of Linux NFS mechanism

Recommend

How to use @media in mobile adaptive styles

General mobile phone style: @media all and (orien...

Detailed explanation of DOM DIFF algorithm in react application

Table of contents Preface What is VirtualDOM? Rea...

Detailed process of modifying hostname after Docker creates a container

There is a medicine for regret in the world, as l...

Elegant practical record of introducing iconfont icon library into vue

Table of contents Preface Generate SVG Introducti...

Summarize the problems encountered in using Vue Element UI

Table of contents 1. DateTimePicker date selectio...

Some suggestions for HTML beginners and novices, experts can ignore them

Feelings: I am a backend developer. Sometimes when...

Use CSS content attr to achieve mouse hover prompt (tooltip) effect

Why do we achieve this effect? ​​In fact, this ef...

TypeScript decorator definition

Table of contents 1. Concept 1.1 Definition 1.2 D...

Vuex modularization and namespaced example demonstration

1. Purpose: Make the code easier to maintain and ...

HTML web page hyperlink tag

HTML web page hyperlink tag learning tutorial lin...

Why node.js is not suitable for large projects

Table of contents Preface 1. Application componen...