Quick Copy Data table method summary in MySQL

This article will focus on a combination of two MySQL commands that will create a new data table of the same structure and data based on the original data table.

This helps you to quickly copy tables as test data during development without risking direct manipulation of the running data tables.

Examples are as follows:

The MYTBL table in the production database is quickly copied to mytbl_new,2 commands as follows:

CREATE TABLE mytbl_new like production.mytbl;

INSERT mytbl_new SELECT * from PRODUCTION.MYTBL;

The first command is to create a new data table mytbl_new and copy the MYTBL data table structure.

The second command is to copy the data from the data table Mytbl to the new table mytbl_new.

Note: PRODUCTION.MYTBL is the name of the database that specifies the table to replicate to production. It is optional.

If there is no production. , the MySQL database will assume that MYTBL is in the current operational database.

Other methods:
Scenario 1:
Copy entire table
CREATE TABLE new_table SELECT * from old_table;

Copy, do not copy data
CREATE TABLE new_table SELECT * from old_table where 0;

Note: This scenario simply builds a table of the results of the SELECT statement. So new_table This table will not have a primary key, index.

Scenario 2:
If we have one of the following tables:

ID Username password

1. The following statement copies the table structure into the new table newadmin. (Data in the table is not copied)

CREATE TABLE newadmin like admin

2. The following statement copies the data to the new table. Note: This statement actually only constructs a table for the result of the SELECT statement. So newadmin This table will not have a primary key, index.

CREATE TABLE Newadmin as
(
SELECT *
From admin
)

3. If you want to actually copy a table. You can use the following statement.

CREATE TABLE newadmin like admin;
INSERT into Newadmin SELECT * from admin;

4. We can operate different databases.

CREATE TABLE newadmin like shop.admin;
CREATE TABLE newshop.newadmin like shop.admin;

5. We can also copy some of the fields in a table.

CREATE TABLE Newadmin as
(
SELECT username, password from admin
)

6. We can also rename the fields of the newly created table.

CREATE TABLE Newadmin as
(
SELECT ID, username as uname, password as pass from admin
)

7. We can also copy part of the data.

CREATE TABLE Newadmin as
(
SELECT * FROM admin WHERE Left (username,1) = ' s '
)

8. We can also define the field information in the table while creating the table.

CREATE TABLE Newadmin
(
ID INTEGER not NULL auto_increment PRIMARY KEY
)
As
(
SELECT * from admin
)