Mysql replication table structure, table data methods _mysql

This article has shared several kinds of replication table structure, table data examples, details, please see below.

1. Copy table structure and data to new table

CREATE Table New Table select * from old table

This method copies all the contents of the oldtable, and of course we can delete them with the delete from newtable.

But one of the worst aspects of this approach is that the new table has no attributes such as the primary key, Extra (auto_increment) of the old table. You need to add "alter" yourself, and it's easy to get it wrong.

2. Copy table structure to new table only

CREATE Table New Table select * from old table where 1=2

or CREATE table new table like old table

3, copy the old table data to the new table (assuming two table structure)

INSERT into new Table select * from old table

4, copy the old table data to the new table (assuming that two table structure is not the same)

INSERT into new Table (Field 1, Field 2,.......) SELECT Field 1, Field 2,...... From old table

5, you can copy the table 1 structure to table 2

SELECT * into table 2 from table 1 WHERE 1=2

6. You can copy table 1 to table 2

SELECT * into table 2 from table 1

7, show create table old tables;

This lists the creation commands for the old table. We just need to copy the command, change the name of the table, and we can create a table that's exactly the same.

8, Mysqldump

Dump the table with mysqldump, change the name, and then guide it back or run directly on the command line

9. Copy old database to new database (copy all table structure and copy all table data)

#mysql-U Root-ppassword
>create DATABASE new_db;
#mysqldump old_db-u Root-ppassword--skip-extended-insert--add-drop-table | MySQL new_db-u Root-ppassword

10, the table is not in the same database (for example, DB1 table1, DB2 table2)
Sql:insert into Db1.table1 select * from Db2.table2 (full copy)
INSERT INTO DB1.TABLE1 SELECT DISTINCT * from Db2.table2 (do not replicate duplicate records)
Insert INTO TDB1.ABLE1 select Top 5 * DB2.TABLE2 (first five records) the above content is the entire description of this article, I hope you like.