Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to copy table structure and table data in Mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Copy the table structure and data to the new table

CREATE TABLE new table SELECT * FROM old table

This method copies everything in the oldtable, and of course we can delete it with delete from newtable;.

However, one of the worst aspects of this approach is that the new table does not have the primary key, Extra (auto_increment) and other properties of the old table. You need to add it with "alter" yourself, and it's easy to make a mistake.

2. Only copy the table structure to the new table

CREATE TABLE new table SELECT * FROM old table WHERE 1 # 2

Or CREATE TABLE new table LIKE old table

3. Copy the data from the old table to the new table (assuming the structure of the two tables is the same)

INSERT INTO new table SELECT * FROM old table

4. Copy the data from the old table to the new table (assuming that the structure of the two tables is different)

INSERT INTO new table (field 1, field 2.) SELECT field 1, field 2. FROM old watch

5. You can copy the structure of Table 1 to Table 2

SELECT * INTO Table 2 FROM Table 1 WHERE 1

6. You can copy all the contents of Table 1 to Table 2.

SELECT * INTO Table 2 FROM Table 1

7. Show create table old table

This lists the creation commands for the old table. We just need to copy the command and change the name of table to create an identical table.

8 、 dump

Use mysqldump to dump the table, rename it and import it back, or run it directly on the command line

After reading the above, do you have any further understanding of how to copy table structure and table data in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.