MySQL copies the table data or table structure to the new table.

MySQL copies the table data or table structure to the new table.

How to copy data from a MySQL table to a new table.

1. MySQL copies the table structure and data to the new table

Create table new_table SELECT * FROM old_table;

2. Only copy the table structure to the new table

Create table new_table SELECT * FROM old_table WHERE 1 = 2; (that is, make the WHERE condition invalid)

Do not copy column attributes and indexes.

Mysql of earlier versions is not supported, mysql 4.0.25 is not supported, and mysql 5 and above are supported.

3. Use LIKE to create an empty table based on the definitions of other tables (including all column attributes and indexes defined in the original table:

Create table new_table LIKE old_table;

The specified data directory or index directory table option for the original table or foreign key definition is not copied.

4. copy the data from the old table to the new table (assuming the two tables have the same structure)

Insert into new_table
SELECT * FROM old_table;

5. copy the data from the old table to the new table (assuming the two tables have different structures)

Insert into new_table (Field 1, Field 2 ,.......)
SELECT Field 1, Field 2 ,...... FROM old_table;

If you have any mistakes, please reply to me, share the technology, and learn from each other.

This article permanently updates the link address: