MySQL Bulk Import data

MySQL Bulk Import data

In our use of the database process, to avoid backup, restore, import, export data, methods are more, according to different data volume selection of different methods, will improve our efficiency,

(1) Import data from table A to table B; Generally involves the same table structure or the table structure is different, when the amount of data is very high efficiency,

The test table A is send_done, and the statement is created as follows:

CREATE TABLE ' Send_done ' (' ID ' bigint () not NULL auto_increment, ' phone ' varchar () ' "Not NULL, ' content ' varchar (100 0) Not NULL, ' Intime ' datetime isn't null, ' state ' int (one) not null, ' Sendtime ' datetime DEFAULT NULL, ' Statcode ' varcha R () default NULL, PRIMARY key (' ID '), key ' ind_state ' (' state ')) Engine=innodb auto_increment=1166482 default charset= Utf8

CREATE TABLE ' Send_sf_done ' (' ID ' bigint () not NULL auto_increment, ' phone ' varchar () ' = ' NOT null ', ' content ' varchar ( ) not NULL, ' Intime ' datetime is NOT NULL, ' state ' int (one) not null, ' Sendtime ' datetime DEFAULT NULL, ' Statcode ' var char () default NULL, PRIMARY key (' ID '), key ' ind_state ' (' state ')) Engine=innodb auto_increment=1166482 default CHARS Et=utf8

Table A's data
MariaDB [wangjubao]> SELECT * from Send_done;
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+
| ID | Phone | Content | Intime | State | Sendtime | Statcode |
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+
| 1166459 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 10:33:02 | 1 | 2016-11-04 10:44:21 | 2114782562554393363 |
| 1166461 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:45:53 | -3 | 2016-11-04 10:46:05 | -3 |
| 1166463 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:47:42 | -3 | 2016-11-04 10:47:54 | -3 |
| 1166465 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:49:08 | -3 | 2016-11-04 10:49:19 | -3 |
| 1166467 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 10:55:33 | 1 | 2016-11-04 10:55:53 | 2114782569505307918 |
| 1166469 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 11:10:20 | -3 | 2016-11-04 11:15:17 | -3 |
| 1166471 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:16 | 1 | 2016-11-04 11:23:18 | 2114782586069773509 |
| 1166473 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:34 | 1 | 2016-11-04 11:23:35 | 2114782586234583070 |
| 1166475 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:34 | 1 | 2016-11-04 11:23:35 | 2114782586234583070 |
| 1166477 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
| 1166479 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
| 1166481 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+
1. import table B from Table A
INSERT INTO Send_done select * from Send_sf_done; (Fields are all the same without writing)
2. Export text from table a load to B
Exporting data
SELECT * from Send_done to outfile "/tmp/winn.sql";

MariaDB [wangjubao]> SELECT * from Send_done to outfile "/tmp/winn.sql"
;
Query OK, Rows Affected (0.00 sec)
Check the Export file
[email protected] tmp]# cat Winn.sql
1166459 15021970243 Winter to "cloud letter Test" 2016-11-04 10:33:02 1 2016-11-04 10:44:21 2114782562554393363
1166461 15021970243 Winter to "cloud letter test" 2016-11-04 10:45:53-3 2016-11-04 10:46:05-3
1166463 15021970243 Winter to "cloud letter test" 2016-11-04 10:47:42-3 2016-11-04 10:47:54-3
1166465 15021970243 Winter to "cloud letter test" 2016-11-04 10:49:08-3 2016-11-04 10:49:19-3
1166467 15021970243 Winter to "cloud letter Test" 2016-11-04 10:55:33 1 2016-11-04 10:55:53 2114782569505307918
1166469 15021970243 Winter to "cloud letter test" 2016-11-04 11:10:20-3 2016-11-04 11:15:17-3
1166471 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:16 1 2016-11-04 11:23:18 2114782586069773509
1166473 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166475 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166477 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166479 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166481 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897

Delete the data from table B before importing from a table,
MariaDB [wangjubao]> Delete from Send_sf_done;
Query OK, Rows Affected (0.00 sec)

MariaDB [wangjubao]> SELECT * from Send_sf_done;
Empty Set (0.00 sec)

Load the exported file
Load data infile '/tmp/winn.sql ' into table send_sf_done;

MariaDB [wangjubao]> Load Data infile '/tmp/winn.sql ' into table send_sf_done;
Query OK, Rows Affected (0.00 sec)
Records:12 deleted:0 skipped:0 warnings:0

MariaDB [wangjubao]> SELECT * from Send_sf_done;
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+
| ID | Phone | Content | Intime | State | Sendtime | Statcode |
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+
| 1166459 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 10:33:02 | 1 | 2016-11-04 10:44:21 | 2114782562554393363 |
| 1166461 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:45:53 | -3 | 2016-11-04 10:46:05 | -3 |
| 1166463 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:47:42 | -3 | 2016-11-04 10:47:54 | -3 |
| 1166465 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 10:49:08 | -3 | 2016-11-04 10:49:19 | -3 |
| 1166467 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 10:55:33 | 1 | 2016-11-04 10:55:53 | 2114782569505307918 |
| 1166469 | 15021970243 | The winter comes "cloud Letter Test" |    2016-11-04 11:10:20 | -3 | 2016-11-04 11:15:17 | -3 |
| 1166471 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:16 | 1 | 2016-11-04 11:23:18 | 2114782586069773509 |
| 1166473 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:34 | 1 | 2016-11-04 11:23:35 | 2114782586234583070 |
| 1166475 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:34 | 1 | 2016-11-04 11:23:35 | 2114782586234583070 |
| 1166477 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
| 1166479 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
| 1166481 | 15021970243 | The winter comes "cloud Letter Test" |     2016-11-04 11:23:35 | 1 | 2016-11-04 11:23:36 | 2114782586244477897 |
+---------+-------------+--------------------------------+---------------------+-------+---------------------+- --------------------+

Method 3 Using Select to export data
Mysql-u root-p-E "select * from Wangjubao.send_done;" >/tmp/winner2.sql
Check Data
[email protected] tmp]# cat Winner2.sql
ID Phone content Intime State sendtime Statcode
1166459 15021970243 Winter to "cloud letter Test" 2016-11-04 10:33:02 1 2016-11-04 10:44:21 2114782562554393363
1166461 15021970243 Winter to "cloud letter test" 2016-11-04 10:45:53-3 2016-11-04 10:46:05-3
1166463 15021970243 Winter to "cloud letter test" 2016-11-04 10:47:42-3 2016-11-04 10:47:54-3
1166465 15021970243 Winter to "cloud letter test" 2016-11-04 10:49:08-3 2016-11-04 10:49:19-3
1166467 15021970243 Winter to "cloud letter Test" 2016-11-04 10:55:33 1 2016-11-04 10:55:53 2114782569505307918
1166469 15021970243 Winter to "cloud letter test" 2016-11-04 11:10:20-3 2016-11-04 11:15:17-3
1166471 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:16 1 2016-11-04 11:23:18 2114782586069773509
1166473 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166475 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166477 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166479 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166481 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
Processing the first line
Sed-i "1,1D"/tmp/winner2.sq
Processing results
1166459 15021970243 Winter to "cloud letter Test" 2016-11-04 10:33:02 1 2016-11-04 10:44:21 2114782562554393363
1166461 15021970243 Winter to "cloud letter test" 2016-11-04 10:45:53-3 2016-11-04 10:46:05-3
1166463 15021970243 Winter to "cloud letter test" 2016-11-04 10:47:42-3 2016-11-04 10:47:54-3
1166465 15021970243 Winter to "cloud letter test" 2016-11-04 10:49:08-3 2016-11-04 10:49:19-3
1166467 15021970243 Winter to "cloud letter Test" 2016-11-04 10:55:33 1 2016-11-04 10:55:53 2114782569505307918
1166469 15021970243 Winter to "cloud letter test" 2016-11-04 11:10:20-3 2016-11-04 11:15:17-3
1166471 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:16 1 2016-11-04 11:23:18 2114782586069773509
1166473 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166475 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:34 1 2016-11-04 11:23:35 2114782586234583070
1166477 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166479 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
1166481 15021970243 Winter to "cloud letter Test" 2016-11-04 11:23:35 1 2016-11-04 11:23:36 2114782586244477897
Loading data

MySQL Bulk Import data