Summary of Quick data table copying methods in MySQL

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

This helps you quickly copy tables as test data during development without having to take the risk of directly operating the running data tables.

Example:

Copy the mytbl table in the production database to mytbl_new quickly. The two commands are 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 data table structure of mytbl.

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

Note: production. mytbl indicates that the database name of the table to be copied is production. It is optional.

If there is no production function, the MySQL database assumes that mytbl is in the database currently operated.

Other methods:
Solution 1:
Copy the 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 solution only creates a table based on the results of the select statement. Therefore, the new_table table does not have primary keys and indexes.

Solution 2:
Suppose we have the following table:

Id username password

1. The following statement copies the table structure to newadmin. (Data in the table will not be copied)

Create table newadmin LIKE admin

2. The following statement copies the data to the new table. Note: This statement only creates a table for the results of the select statement. Therefore, the newadmin table does not have a primary key and an index.

Create table newadmin
(
SELECT *
FROM admin
)

3. If you want to 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 fields in a table.

Create table newadmin
(
SELECT username, password FROM admin
)

6. We can also change the name of the field in the new table.

Create table newadmin
(
SELECT id, username AS uname, password AS pass FROM admin
)

7. We can also copy part of the data.

Create table newadmin
(
SELECT * FROM admin where left (username, 1) ='s'
)

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

Create table newadmin
(
Id integer not null AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)