MySQL Fast copy data table and database method

The following examples are to copy old_table to new_table.

To start with the conclusion, the most recommended practice is the following two lines:

The code is as follows	Copy Code
CREATE TABLE new_table like old_table; INSERT new_table SELECT * from old_table;

Come down to talk about several practices and advantages and disadvantages.

If the MyISAM approach is more violent, it can be done in the following ways:

The code is as follows	Copy Code
1.CREATE TABLE new_table; 2./etc/init.d/mysql stop 3.cd/var/lib/mysql/database_name 4.CP old_table. Myi new_table. Myi 5.CP old_table. MyD new_table. MyD 6./etc/init.d/mysql start

This can also be replicated, but the violence is likely to have some minor problems to solve.

The following practices would be more recommended (refer to this article: Sql-fastest Way to copy a table in MySQL), but there are two approaches, some different, that first write the practice and then explain the difference.

?: The following old_table if cross DB, can be written as old_db.old_table to specify.

The first way: a row of syntax copy Table + Data, but need to manually increase Primay, index key, and so on.

The code is as follows	Copy Code
1.CREATE TABLE new_table SELECT * from old_table; # This method Primay, index key will not be copied, you need to manually add 2.ALTER TABLE new_table ADD PRIMARY KEY (ID);

The second approach is to copy the Table schema before you INSERT Data. (This is recommended, the Schema must be exactly the same)

The code is as follows	Copy Code
1.CREATE TABLE new_table like old_table; 2.INSERT new_table SELECT * from old_table;

First of all, let's start by saying: (This Schema may be different, Data will be copied correctly).

The code is as follows	Copy Code
CREATE TABLE new_table SELECT * from old_table;

This row copies the table and Data to new_table, but the table uses the same Engine and language code as the MySQL preset, not the copy old_table. The result may be the following: (Depends on your system settings and The schema of old_table and new_table may be different, but Data is consistent.

The code is as follows	Copy Code
CREATE TABLE ' old_table ' ( ' No ' int (8) Not NULL, ' CNAME ' varchar (255) DEFAULT NULL, PRIMARY KEY (' no ') ) Engine=myisam DEFAULT Charset=utf8; CREATE TABLE ' new_table ' ( ' No ' int (8) Not NULL, ' CNAME ' varchar (255) CHARACTER SET UTF8 DEFAULT NULL ) Engine=innodb DEFAULT charset=latin1;

The second approach is: (This approach can replicate Schema and Data in exactly the same way).

CREATE TABLE new_table like old_table;
This row copies the table schema, copying the table schema, and then INSERT the data in a lump sum.