MySQL a library a table B library B to import a table in table B

Today received the development of the sister's needs, import the data for the specified month to the test suit MySQL specified table (table name is not the same)

First look at the overall size of the table on the Downline

mysql> SELECT sum (data_length) +sum (index_length) from INFORMATION_SCHEMA. TABLES where table_schema= ' library name ' and table_name= ' table name ';

+------------------------------------+

| SUM (data_length) +sum (index_length) |

+------------------------------------+

| 4196941824 |

+------------------------------------+

1 row in Set (0.00 sec)

This is a little bit bigger than 4G, but it's just that the data for the specified month is imported

Open Dry

Vaguely remember mysqldump has a function to specify the scope of export after Google Resolute export

/USR/LOCAL/MYSQL/BIN/MYSQLDUMP-H192.168.1.1-UZXC-PZXC123 Library Name Table name--where= "Uptime >= 1404144000 and uptime <= 14068 22399 ">/tmp/table name. sql

Export or faster, data only more than 200 m

Then how to import it, the table name is not the same, finally under various auxiliary, decided to use the method of merging tables to get

A, the pilot into the database of the corresponding library inside

b, then merge the data using the Merge table method

INSERT INTO target table select * from source table;

OK after finding data in the increase, in importing data ing easy happy solved this problem

mysql> SELECT sum (data_length) +sum (index_length) from INFORMATION_SCHEMA. TABLES where table_schema= ' library name ' and table_name= ' table name ';

+------------------------------------+

| SUM (data_length) +sum (index_length) |

+------------------------------------+

| 177422336 |

+------------------------------------+

1 row in Set (0.00 sec)

Data in growth ...

After the import is successful,

With a happy view of the data,

SELECT * FROM table name LIMIT 1, 10;

Find column name since the order of the corresponding data is not the same, drunk, sister said the data structure of the same

Only once, after emptying the previously imported data, specify the order in which to import the column names according to the column name order of the source table

INSERT into target table (column 1, column 2, column 3 ...) ) SELECT * from source table;

Now we're finally done

This article is from the "on the Road" blog, please be sure to keep this source http://xiaochengxiang.blog.51cto.com/2558908/1541628